Re: query optimization

Micah Gersten wrote:

What indices do you have? Thank you, Micah Gersten onShore Networks Internal Developer http://www.onshore.com YVES SUCAET wrote:

How could I rewrite the following query so it runs faster: select distinct location from blockunit where blockid in ( select bu.blockid from blockunit bu inner join interactionparts ip on (bu.blockid = ip.part) where ip.blockid in (110936,110937,111641,111642,113140,113141,114925,114926,121161,121162,124087, 124088,124562,124563,133358,133359,133409,133410,135304,135305,136096) union select bu.blockid from blockunit bu where bu.blockid in (110936,110937,111641,111642,113140,113141,114925,114926,121161,121162,124087, 124088,124562,124563,133358,133359,133409,133410,135304,135305,136096) )

Which parts are slow? Run the inner query by itself to see if that's slow. If it is, take the first part of the union and run that. Is that slow? Same for the second. Also since you're doing a DISTINCT in the outer query, you can change the subquery to do a UNION ALL. A UNION will remove duplicates from the result sets, a UNION ALL will not. Since you're doing a distinct on the whole thing anyway, remove the duplicate check from the subquery - it'll make it slightly faster. -- Postgresql & php tutorials http://www.designmagick.com/