Re: join and query planner

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
>