From: John A Meinel <john@arbash-meinel.com>
To: Dario Pudlo <dariop@unitech.com.ar>
Cc: pgsql-performance@postgresql.org
Subject: Re: join and query planner
Date: Mon, 11 Jul 2005 18:39:08 -0500
Message-ID: <42D3031C.8050907@arbash-meinel.com> (raw)
In-Reply-To: <MHEDJHCKDNOEHJKHIOCJIEFKCEAA.dariop@unitech.com.ar>
References: <MHEDJHCKDNOEHJKHIOCJIEFKCEAA.dariop@unitech.com.ar>
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
>
Attachments:
signature.asc (application/pgp-signature, 252B) download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: john@arbash-meinel.com, dariop@unitech.com.ar
Subject: Re: join and query planner
In-Reply-To: <42D3031C.8050907@arbash-meinel.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox