Hi, I've got a query that I think the query optimiser should be able to work it's magic on but it doesn't! I've had a look around and asked on the IRC channel and found that the current code doesn't attempt to optimise for what I'm asking it to do at the moment. Here's a bad example:
SELECT u.txt FROM smalltable t, ( SELECT id, txt FROM largetable1 UNION ALL SELECT id, txt FROM largetable2) u WHERE t.id = u.id AND t.foo = 'bar'; I was hoping that "smalltable" would get moved up into the union, but it doesn't at the moment and the database does a LOT of extra work. In this case, I can manually do quite a couple of transforms to move things around and it does the right thing: SELECT txt FROM ( SELECT l.id as lid, r.id as rid, r.foo, l.txt FROM largetable1 l, smalltable r UNION ALL SELECT l.id as lid, r.id as rid, r.foo, l.txt FROM largetable1 l, smalltable r) WHERE foo = 'bar'; AND lid = rid The optimiser is intelligent enough to move the where clauses up into the union and end end up with a reasonably optimal query. Unfortunatly, in real life, the query is much larger and reorganising everything manually isn't really feasible! Is this a good place to ask about this or is it more in the realm of the hackers mailing list? Thanks, Sam ---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match