Query optimization surprises...
Posted: Wed Jul 09, 2003 1:23 pm
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)
Revised query: (estimated query : 192 units)
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
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;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;Yes this is basically just a "Hey that's cool" post