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
>