From: | "Dario Pudlo" <dariop(at)unitech(dot)com(dot)ar> |
---|---|
To: | <pgsql-performance(at)postgresql(dot)org> |
Subject: | join and query planner |
Date: | 2005-07-06 21:54:02 |
Message-ID: | MHEDJHCKDNOEHJKHIOCJIEFKCEAA.dariop@unitech.com.ar |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
(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.
From | Date | Subject | |
---|---|---|---|
Next Message | Dario | 2005-07-06 22:04:48 | join and query planner |
Previous Message | John A Meinel | 2005-07-06 21:29:00 | Re: CURSOR slowes down a WHERE clause 100 times? |