Message-ID: <42D3031C.8050907@arbash-meinel.com>
Date: Mon, 11 Jul 2005 18:39:08 -0500
From: John A Meinel <john@arbash-meinel.com>
User-Agent: Mozilla Thunderbird 1.0.2 (Macintosh/20050317)
MIME-Version: 1.0
To: Dario Pudlo <dariop@unitech.com.ar>
Cc: pgsql-performance@postgresql.org
Subject: Re: join and query planner
References: <MHEDJHCKDNOEHJKHIOCJIEFKCEAA.dariop@unitech.com.ar>
In-Reply-To: <MHEDJHCKDNOEHJKHIOCJIEFKCEAA.dariop@unitech.com.ar>
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature";
	boundary="------------enigAC7C853A4D28D578E3FB0172"

This is an OpenPGP/MIME signed message (RFC 2440 and 3156)
--------------enigAC7C853A4D28D578E3FB0172
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

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
>


--------------enigAC7C853A4D28D578E3FB0172
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.0 (Darwin)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFC0wMcJdeBCYSNAAMRAs+mAJ4y/5cqJ6BshkSFmos3M/wCXuZf6gCeMiQB
vLDYL89Rt1TCEGFFkOm9lhI=
=QwbF
-----END PGP SIGNATURE-----

--------------enigAC7C853A4D28D578E3FB0172--