Category: Revelation

Carp::croak()

I went kayaking after work today on Irondequoit Creek. The carp were mating, and there were lots of dead ones floating around, thus my little perl joke in the title.

It was a great day to blow off work a bit early, and Rob and I went upstream from Bay Creek Paddling Center up just past the Elison Park dog beach. I was pretty tired on the way back, and it didn’t help that the wind shifted around and got cold, so we ended up with headwinds in both directions. According to the thermometer in my car, the temperature dropped about 10 degrees in the course of the hour and a half paddle, although it felt like the temperature change happened all at once when the wind shifted.

The weir was a bit of a challenge, but nothing like it had been last time I took a look at it. I nearly dug a rail under, and was grateful for my spray skirt. Rob took about three tries, but mostly because he tried hitting it from one side or the other instead of coming right up the middle.

A couple of times we saw a bird that at first we thought was some sort of Loon because it was so low in the water, but it had no white markings, only black, with a bit of red or orange near the base of the beak. When it flew, we could see very long pointy wings. And when it swam it held its beak up at a 45 degree angle to the water, rather than parallel like a Loon does. I guessed it was a Cormorant, and after looking it up, I’m sure of it.

We also saw lots of geese. At one point, we heard a lot of honking up ahead, and as we rounded the corner this goose left the shore and paddled out in front of us, and did a bit of a fake take-off, presumably to try to lead us away from his nest. But this annoyed another goose, who then flew into the first one and attacked him until they both flew off.

Lots of people and dogs in the dog park, including two people who kept asking us all sorts of questions about paddling and seemed put out that we continued paddling instead of stopping to talk after we’d answered a couple. And one canoe coming upstream just as we’d flushed the Cormorant downstream for the last time. They asked us what the bird was, I said Cormorant, and the woman in the front of the canoe said “See, I told you” to the man in the back.

Lots of fun.

I LOLed

Conversation at lunch today between R (been using emacs since 1981) and me (been using vi since 1987). It started off with him saying something about how he knew our latitude and longitude because he had to put it in his emacs config.

Me: When I’m looking for an editor, the ability to tell me sunrise and sunset times isn’t high on my list.
R: Emacs isn’t an editor, it’s an environment.
Me: Emacs isn’t an editor, it’s a lifestyle.
R: Yup. There’s straight, gay, and emacs.

At that point, I was literally laughing out loud (and I mean “literally” literally, not in the internet sense of “I smiled a bit”). I was lucky I didn’t have any food or drink in my mouth at the time.

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.