Thread: join and query planner

join and query planner

From
"Dario"
Date:
(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...

I've already change statistics, I clustered tables with cluster, ran vacuum
analyze, changed work_mem, shared_buffers...

Greetings. TIA.


Re: join and query planner

From
Stephan Szabo
Date:
On Wed, 6 Jul 2005, Dario 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...

Yes. Reordering the outer joins can change the results in some cases which
would be invalid.  Before we can change the ordering behavior, we really
need to know under what conditions it is safe to do the reordering.