What's it all about, Alfie?

Cracked the Speed Problem with Postgres

This morning I put a lot of effort into making the selection of the children from the postgres database faster. There's just no way it needs to be 30 sec. Since I'd already tried the sub-select, I needed to have a completely different approach. Thankfully, I have a lot of ideas, and it didn't take long to find the one that solved the problem.

The problem is that I was always looking in the "wrong direction". I was using IN and joins, when it was really a much simpler problem. Let's find all the demand_id values from the demands table - based on the demand_set_id - which is keyed. This is a very fast request. Then we use that as a cursor to get the locations based on that.

There are a few nice things with postgresql that make this even nicer - like the FOR loop with the select embedded - very nice. Turns out to be really pretty simple:

create or replace function locations_for_demand_set(ds_id uuid) returns table(id uuid, demand_id uuid, is_anchor_zip boolean, latitude double precision, longitude double precision, name varchar, subdivision_rank varchar, type varchar, division_permalink varchar, zip varchar) as $body$ declare rec demands%rowtype; begin create temp table answer on commit drop as select * from locations with no data; for rec in select * from demands where demand_set_id = ds_id loop insert into answer select * from locations l where l.demand_id = rec.id; end loop; return query select * from answer; end $body$ language plpgsql

The difference was dramatic. Now it's about 450 msec for the same load. That's a factor of 60x! I felt such amazement, what a lucky find!

This entry was posted on Friday, February 1st, 2013 at 11:41 am and is filed under Coding, Cube Life, Open Source Software. You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed.

Cracked the Speed Problem with Postgres

Pages

Archives

Categories