Dario Pudlo wrote: > (first at all, sorry for my english) > Hi. > - Does "left join" restrict the order in which the planner must join > tables? I've read about join, but i'm not sure about left join... > - If so: Can I avoid this behavior? I mean, make the planner resolve the > query, using statistics (uniqueness, data distribution) rather than join > order. > > My query looks like: > SELECT ... > FROM a, b, > LEFT JOIN c ON (c.key = a.key) > LEFT JOIN d on (d.key=a.key) > WHERE (a.key = b.key) AND (b.column <= 100) > > b.column has a lot better selectivity, but planner insist on resolve > first c.key = a.key. > > Of course, I could rewrite something like: > SELECT ... > FROM > (SELECT ... > FROM a,b > LEFT JOIN d on (d.key=a.key) > WHERE (b.column <= 100) > ) > as aa > LEFT JOIN c ON (c.key = aa.key) > > but this is query is constructed by an application with a "multicolumn" > filter. It's dynamic. > It means that a user could choose to look for "c.column = 1000". And > also, combinations of filters. > > So, I need the planner to choose the best plan...
Probably forcing the other join earlier could help: SELECT ... FROM a JOIN b ON (a.key = b.key) LEFT JOIN c ON (c.key = a.key) ... I think the problem is that postgresql can't break JOIN syntax very easily. But you can make the JOIN earlier. John =:-> > > I've already change statistics, I clustered tables with cluster, ran vacuum > analyze, changed work_mem, shared_buffers... > > Greetings. TIA. > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster >
signature.asc
Description: OpenPGP digital signature
