> I originally had this query with an OR clause instead of the UNION,
> which made it amazingly impossibly slow, with the UNION it's far faster
> however it's still very slow,(10,000+ rows in both stables), is there a
> way to speed this up?
Hmm. What does EXPLAIN give as the query plan if you use the OR
approach? The UNION method requires much more work than a decent OR
plan would need; as you can see from the query plan, it has to sort and
unique-ify the results of the two sub-selects in order to discard
duplicate tuples. If you don't mind seeing the same tuple twice when
it matches on both PO fields, you could use UNION ALL rather than UNION
to avoid the sort step. But that's just a hack...
I think the problem might be that Postgres doesn't know how to handle
ORed join clauses very efficiently. Something to work on in the future.
> I have another query that joins the result of this with 5 other
> tables, unfortunately that one takes like 10 minutes...
How long does it take to EXPLAIN that query? Postgres' optimizer has
some bugs that cause it to take an unreasonable amount of time to plan
a query that joins more than a few tables. You can tell whether it's
the planning or execution time that's the problem by comparing EXPLAIN
runtime to the actual query. (The bugs are fixed in 6.5, btw. In the
meantime a workaround is to reduce the GEQO threshold to less than the
number of tables in the query that's giving you trouble.)
regards, tom lane