Page 1 of 1

Query optimization surprises...

Posted: Wed Jul 09, 2003 1:23 pm
by nielsene
Sigh, in the middle of rewriting some code I decided to play with a few queries just to see if I could improve their performance. I had test several different constructions before and all were basically similar performance-wise... but the one I tried now, showed a two order of magnitude decreased in predicted cost, wow! I'm happy. Especially as the one revised query replaced three of the slow queries... and elimintates the need for about 500 other simple queries (queries where the db overhead is probably higher than than the query).

I'm using PostGreSQL so I don't know how this tidbit will apply to most of the folks here, but just wanted to share that UNIONs are worth considering instead of disjunctions in your where/join clause. However the UNIONs aren't as simple to read I think.

Sample query before: (estimated cost : 10503 units)

Code: Select all

SELECT     DISTINCT peopleid, firstname, lastname, organization 
   FROM     people 
                JOIN couples ON (peopleid = leader OR peopleid = follower)
ORDER BY lastname, firstname;
Revised query: (estimated query : 192 units)

Code: Select all

SELECT DISTINCT p1.peopleid     AS peopleid,
                p1.firstname    AS firstname,
                p1.lastname     AS lastname,
                p1.organization AS organization,
                coupleid,
                follower        AS partner,
                p2.lastname     AS plast,
                p2.firstname    AS pfirst,
                'Leading'::TEXT AS role
       FROM people AS p1
       JOIN couples      ON (p1.peopleid = leader)
       FROM people AS p2 ON (p2.peopleid = follower)
UNION
SELECT DISTINCT p3.peopleid       AS peopleid,
                p3.firstname      AS firstname,
                p3.lastname       AS lastname,
                p3.organization   AS organization,
                coupleid,
                leader            AS partner,
                p4.lastname       AS plast,
                p4.firstname      AS pfirst,
                'Following'::TEXT AS role
       FROM people AS p3
       JOIN couples      ON (p3.peopleid = follower)
       FROM people AS p4 ON (p4.peopleid = leader)
ORDER BY lastname,firstname,plast,pfirst;
Not only is the second query almost 100x faster it also includes an additional join (self join) on each branch compared to the first!

Yes this is basically just a "Hey that's cool" post :)

Posted: Wed Jul 09, 2003 2:36 pm
by patrikG
That performance-boost is really amazing. Have to try that out.
Hey, that's cool :P

Posted: Wed Jul 09, 2003 4:25 pm
by BDKR
Cool stuff allright! I like seeing these kinds of things myself. I've got something that I know in a short time is going to need this level of review in time. For now, I'm just trying to make sure I'm in the ballpark.

Anyway, do you have a blog? It could really help if you put this up someplace where people could have later access to it to do some comparisons against.

Cheers,
BDKR