From: Kevin Grittner <Kevin.Grittner@wicourts.gov>
To: pgsql-performance@postgresql.org
To: dariop@unitech.com.ar
Subject: Re: join and query planner
Date: Mon, 18 Jul 2005 12:57:31 -0500
Message-ID: <s2dba73f.020@gwmta.wicourts.gov> (raw)
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
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: Kevin.Grittner@wicourts.gov, dariop@unitech.com.ar
Subject: Re: join and query planner
In-Reply-To: <s2dba73f.020@gwmta.wicourts.gov>
* 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