Re: join and query planner - Mailing list pgsql-performance

From Kevin Grittner
Subject Re: join and query planner
Date
Msg-id s2dba73f.020@gwmta.wicourts.gov
Whole thread Raw
In response to join and query planner  ("Dario Pudlo" <dariop@unitech.com.ar>)
Responses Re: join and query planner
List pgsql-performance
Just out of curiosity, does it do any better with the following?

    SELECT ...
     FROM a
     JOIN b ON (a.key = b.key)
     LEFT JOIN c ON (c.key = a.key)
     LEFT JOIN d ON (d.key=a.key)
     WHERE (b.column <= 100)


>>> "Dario Pudlo" <dariop@unitech.com.ar> 07/06/05 4:54 PM >>>
(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.


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster


pgsql-performance by date:

Previous
From: Tom Lane
Date:
Subject: Re: Huge performance problem between 7.4.1 and 8.0.3 - CS issue?
Next
From: Robert Creager
Date:
Subject: Re: Huge performance problem between 7.4.1 and 8.0.3 - CS issue?