Category: Revelation

Optimize this?

The main loop query of my waypoint generator app is kind of hairy. And trying to do an “explain” on a typical query shows why it’s so slow.

explain SELECT a.id, c.pdb_id, internalid, a.type, name,
address, state, country, latitude, longitude,
declination, main_frequency, elevation,
b.category, chart_map, tpa, ispublic
FROM waypoint a, type_categories b,
id_mapping c
WHERE a.type = b.type AND
a.id = c.id AND
country in (‘US’, ‘CA’) AND
(a.type in (‘AIRPORT’, ‘VOR’, ‘NDB’) or
(category = 3 and (chart_map & 7) != 0)) AND
deletedon is null;
QUERY PLAN
——————————————————————————————————————————————————————–
Hash Join (cost=4442.82..19955.46 rows=34752 width=111)
Hash Cond: ((a.id)::text = (c.id)::text)
-> Hash Join (cost=4.83..12938.32 rows=34752 width=107)
Hash Cond: ((a.”type”)::text = (b.”type”)::text)
Join Filter: (((a.”type”)::text = ANY ((‘{AIRPORT,VOR,NDB}’::character varying[])::text[])) OR ((b.category = 3) AND (((a.chart_map)::integer & 7) <> 0)))
-> Seq Scan on waypoint a (cost=0.00..10759.48 rows=72467 width=103)
Filter: ((country = ANY (‘{US,CA}’::bpchar[])) AND (deletedon IS NULL))
-> Hash (cost=4.37..4.37 rows=37 width=15)
-> Seq Scan on type_categories b (cost=0.00..4.37 rows=37 width=15)
-> Hash (cost=2091.77..2091.77 rows=127777 width=12)
-> Seq Scan on id_mapping c (cost=0.00..2091.77 rows=127777 width=12)
(11 rows)

Adding indexes on country and type doesn’t help. There is still that nasty looking “Seq Scan on waypoint a” line. And also, another “Seq Scan on id_mapping c”, which I don’t understand at all because the joining column, c.id, is a primary key, so shouldn’t there be an index involved?

I’ve got a few ideas on how to use the spatial capability of PostGIS to improve that query, so I’m going to have to run a few tests. The first few ideas I’ve had aren’t showing major improvements in “explain”. It looks like the whole “type in … or ((chart_map & NN) != 0)” is going to force a sequential scan on waypoint no matter what I do. Hmmm.

Hmmm

I upgraded my blog to WordPress 2.5 because the damn thing was nagging me all the time about being back at 2.3.3. But now I discover that my theme doesn’t work right with the new code, and one of my favourite plugins, the LiveJournal CrossPoster, doesn’t work. Now I’ve either got to find a less ugly theme, or fix the old Maple theme to support the new comment code with the built-in Gravatars.

Update: I might have found the fix for LJXP!
In lj_crosspost.php, change

if(version_compare($wp_version, “2.1”, “< ")) { require_once(ABSPATH . '/wp-includes/template-functions-links.php'); }

to

if(version_compare($wp_version, “2.3”, “< ")) { require_once(ABSPATH . '/wp-includes/link-template.php'); } else if(version_compare($wp_version, "2.1", "<")) { require_once(ABSPATH . '/wp-includes/template-functions-links.php'); }

Update #2:
I officially hate this update. It keeps adding bogus </code> tags even though my tags are perfectly well closed before I saved them. Let’s try with block quotes instead?

Bald Eagles!

It’s rare for me to get my second paddle of the season so quickly after the first (first was Sunday, second was today, Wednesday), but the weather cooperated and today was chicken wing day in the cafeteria so I wasn’t hungry when I got home, so away I went.

The creek was quite deserted by other boats, except for one flat bottom dinghy that two guys were fishing from. I’ve seen carp mating in that area, so that’s probably what they were after.

Considering that Sunday I saw almost no wildlife except geese, today was a bonanza day. I saw male red-winged blackbirds staking out their territories, several kingfishers patrolling their sections of the river (and raising their crests in alarm when I got too close), a few pairs of ducks, and I finally saw the famous Irondequoit Creek bald eagles. I rounded a corner and saw two soaring birds, one quite high and one just above the ridge line, nd immediately said “oh, turkey vultures”, but then the lower one spread his stunning white tail and I noticed that the head was bright white as well. I never did make out for sure if the higher one was a bald eagle as well, but I think it was. One thing that impressed me was that while it was soaring, it seemed to be moving back and forth much faster than a turkey vulture does. Maybe it’s anti-vulture prejudice, but it just seemed more, I don’t know, purposeful or something.

I also saw a crow or raven down fairly low, but he flew away rapidly as I got near so I didn’t get a good look. There were very distinct primary feathers curling up at the tips, which I think means it was a raven.

I went a little bit further than I did on Sunday, and I wasn’t as tired when I reached the weir, probably because I paced myself better. Probably just as well, because on the way home there were two stretches where I was paddling into a very strong wind. My weather widget says that the winds at the airport are 21G33 knots, or 24 to 33 mph, which I can easily believe. And when the wind is blowing in my face like that, my old canoe trip instincts say “paddle as hard as you can for the lee of the upwind shore, and don’t rest until you get there”, so that’s what I do.

First paddle of the season

Well, I got out earlier than last season, but not as early as the previous year. The sun was shining, the air was warm (just a little over 60, I think), the water was freezing cold. All in all, a great day to be out. And obviously I wasn’t the only one, because the creek was crowded with boats, some who looked like they knew what they were doing, some who obviously didn’t. Three teenagers in a canoe lurching from bank to bank with no clue what they were doing (sort of a “sub-prime” canoe), a large gaggle of kayaks coming downstream together, a guy with his feet up on top of his kayak deck and a fishing rod between his feet, people in spiffy paddling jackets and wet suits, and people in t-shirts and shorts.

I wore my wet suit because I knew the water would be cold and I didn’t want to get cold legs on the bottom of the boat, nor did I want to get hypothermia if I tipped. I had planned to only go as far as the weir so I wouldn’t overdo it. But in hindsight I probably should have turned back sooner – I was tired and my elbows were sore by the time I got there. And when I turned back, there was a strong wind in my face countering any assist I was getting from the current.

The weir was impassible – the smaller gaps were jammed with debris, so all the water was flowing through the middle channel, and there was about a foot and a half or two foot drop there. I bet it would have been fun to paddle down, but as tired as I was, I wasn’t going to try paddling up it. I wasn’t even going to try portaging around it so I could shoot it. I just looked at it and said “no f-ing way”. There were a couple of people fishing the eddy below it. So avoiding the lines, I did an eddy turn and turned down stream. I was glad to see that the big mud flat that had sprung up last year just downstream of the weir had submerged again. Hopefully the spring run-off will scour the stream bed a bit deeper this year so it won’t re-emerge in the lower water season.

Not much wildlife in the marsh yet, except some sparrows and lots and lots of Canada geese. Most of the geese looked like they were getting ready to nest, but there was one on a dead tree that lies on its side in the middle of the creek who was playing dead as I splashed by. I wonder if she had eggs? Last year I noticed that a goose had tried to lay eggs on a semi-flat spot on that tree, but most of them had rolled down into a crack, and I guess she’d abandoned the nest. I hope she has better luck this year.