pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedjoin and query planner
9+ messages / 6 participants
[nested] [flat]
* join and query planner
@ 2005-07-06 21:54 Dario Pudlo <dariop@unitech.com.ar>
2005-07-06 22:04 ` join and query planner Dario <dario_d_s@unitech.com.ar>
2005-07-11 23:39 ` Re: join and query planner John A Meinel <john@arbash-meinel.com>
2005-07-12 14:35 ` Re: join and query planner Bruno Wolff III <bruno@wolff.to>
0 siblings, 3 replies; 9+ messages in thread
From: Dario Pudlo @ 2005-07-06 21:54 UTC (permalink / raw)
To: 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.
^ permalink raw reply [nested|flat] 9+ messages in thread
* join and query planner
2005-07-06 21:54 join and query planner Dario Pudlo <dariop@unitech.com.ar>
@ 2005-07-06 22:04 ` Dario <dario_d_s@unitech.com.ar>
2005-07-06 22:34 ` Re: join and query planner Stephan Szabo <sszabo@megazone.bigpanda.com>
2 siblings, 1 reply; 9+ messages in thread
From: Dario @ 2005-07-06 22:04 UTC (permalink / raw)
To: 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.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: join and query planner
2005-07-06 21:54 join and query planner Dario Pudlo <dariop@unitech.com.ar>
2005-07-06 22:04 ` join and query planner Dario <dario_d_s@unitech.com.ar>
@ 2005-07-06 22:34 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 0 replies; 9+ messages in thread
From: Stephan Szabo @ 2005-07-06 22:34 UTC (permalink / raw)
To: Dario <dario_d_s@unitech.com.ar>; +Cc: pgsql-performance
On Wed, 6 Jul 2005, Dario 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...
Yes. Reordering the outer joins can change the results in some cases which
would be invalid. Before we can change the ordering behavior, we really
need to know under what conditions it is safe to do the reordering.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: join and query planner
2005-07-06 21:54 join and query planner Dario Pudlo <dariop@unitech.com.ar>
@ 2005-07-11 23:39 ` John A Meinel <john@arbash-meinel.com>
2 siblings, 0 replies; 9+ messages in thread
From: John A Meinel @ 2005-07-11 23:39 UTC (permalink / raw)
To: Dario Pudlo <dariop@unitech.com.ar>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: join and query planner
2005-07-06 21:54 join and query planner Dario Pudlo <dariop@unitech.com.ar>
@ 2005-07-12 14:35 ` Bruno Wolff III <bruno@wolff.to>
2 siblings, 0 replies; 9+ messages in thread
From: Bruno Wolff III @ 2005-07-12 14:35 UTC (permalink / raw)
To: Dario Pudlo <dariop@unitech.com.ar>; +Cc: pgsql-performance
On Wed, Jul 06, 2005 at 18:54:02 -0300,
Dario Pudlo <dariop@unitech.com.ar> 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...
The left join operator is not associative so in general the planner doesn't
have much flexibility to reorder left (or right) joins.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: join and query planner
@ 2005-07-18 17:57 Kevin Grittner <Kevin.Grittner@wicourts.gov>
2005-07-18 19:24 ` Re: join and query planner Dario <dario_d_s@unitech.com.ar>
0 siblings, 1 reply; 9+ messages in thread
From: Kevin Grittner @ 2005-07-18 17:57 UTC (permalink / raw)
To: pgsql-performance; dariop@unitech.com.ar
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: join and query planner
2005-07-18 17:57 Re: join and query planner Kevin Grittner <Kevin.Grittner@wicourts.gov>
@ 2005-07-18 19:24 ` Dario <dario_d_s@unitech.com.ar>
0 siblings, 0 replies; 9+ messages in thread
From: Dario @ 2005-07-18 19:24 UTC (permalink / raw)
To: pgsql-performance
Hi.
> Just out of curiosity, does it do any better with the following?
>
> SELECT ...
Yes, it does.
But my query could also be
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)
/*new*/ , e
WHERE (b.column <= 100)
/*new*/ and (e.key = a.key) and (e.field = 'filter')
because it's constructed by an application. I needed to know if, somehow,
someway, I can "unforce" join order.
The only way to solve it so far is changing application. It must build
something like
SELECT ...
FROM b
JOIN (a JOIN e ON (e.key = a.key)) 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) and (e.field = 'filter')
Supossed that e.field has (should have) better selectivity. But now this
problem belongs to programmer's group :-)
The query, in fact, has more tables to join. I wonder if lowering geqo
threshold could do the work...
Thank you. Greetings. Long life, little spam and prosperity!
-----Mensaje original-----
De: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org]En nombre de Kevin
Grittner
Enviado el: lunes, 18 de julio de 2005 14:58
Para: pgsql-performance@postgresql.org; dariop@unitech.com.ar
Asunto: Re: [PERFORM] join and query planner
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)
>>> snipp
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: join and query planner
@ 2005-07-18 20:47 Kevin Grittner <Kevin.Grittner@wicourts.gov>
2005-07-19 21:41 ` Re: join and query planner Dario <dario_d_s@unitech.com.ar>
0 siblings, 1 reply; 9+ messages in thread
From: Kevin Grittner @ 2005-07-18 20:47 UTC (permalink / raw)
To: pgsql-performance; dario_d_s@unitech.com.ar
You might want to set join_collapse_limit high, and use the JOIN
operators rather than the comma-separated lists. We generate the WHERE
clause on the fly, based on user input, and this has worked well for us.
-Kevin
>>> "Dario" <dario_d_s@unitech.com.ar> 07/18/05 2:24 PM >>>
Hi.
> Just out of curiosity, does it do any better with the following?
>
> SELECT ...
Yes, it does.
But my query could also be
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)
/*new*/ , e
WHERE (b.column <= 100)
/*new*/ and (e.key = a.key) and (e.field = 'filter')
because it's constructed by an application. I needed to know if,
somehow,
someway, I can "unforce" join order.
The only way to solve it so far is changing application. It must build
something like
SELECT ...
FROM b
JOIN (a JOIN e ON (e.key = a.key)) 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) and (e.field = 'filter')
Supossed that e.field has (should have) better selectivity. But now this
problem belongs to programmer's group :-)
The query, in fact, has more tables to join. I wonder if lowering geqo
threshold could do the work...
Thank you. Greetings. Long life, little spam and prosperity!
-----Mensaje original-----
De: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org]En nombre de Kevin
Grittner
Enviado el: lunes, 18 de julio de 2005 14:58
Para: pgsql-performance@postgresql.org; dariop@unitech.com.ar
Asunto: Re: [PERFORM] join and query planner
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)
>>> snipp
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: join and query planner
2005-07-18 20:47 Re: join and query planner Kevin Grittner <Kevin.Grittner@wicourts.gov>
@ 2005-07-19 21:41 ` Dario <dario_d_s@unitech.com.ar>
0 siblings, 0 replies; 9+ messages in thread
From: Dario @ 2005-07-19 21:41 UTC (permalink / raw)
To: pgsql-performance
I'll try that.
Let you know as soon as I can take a look.
Thank you-
-----Mensaje original-----
De: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org]En nombre de Kevin
Grittner
Enviado el: lunes, 18 de julio de 2005 17:48
Para: pgsql-performance@postgresql.org; dario_d_s@unitech.com.ar
Asunto: Re: [PERFORM] join and query planner
You might want to set join_collapse_limit high, and use the JOIN
operators rather than the comma-separated lists. We generate the WHERE
clause on the fly, based on user input, and this has worked well for us.
-Kevin
>>> "Dario" <dario_d_s@unitech.com.ar> 07/18/05 2:24 PM >>>
Hi.
> Just out of curiosity, does it do any better with the following?
>
> SELECT ...
Yes, it does.
But my query could also be
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)
/*new*/ , e
WHERE (b.column <= 100)
/*new*/ and (e.key = a.key) and (e.field = 'filter')
because it's constructed by an application. I needed to know if,
somehow,
someway, I can "unforce" join order.
The only way to solve it so far is changing application. It must build
something like
SELECT ...
FROM b
JOIN (a JOIN e ON (e.key = a.key)) 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) and (e.field = 'filter')
Supossed that e.field has (should have) better selectivity. But now this
problem belongs to programmer's group :-)
The query, in fact, has more tables to join. I wonder if lowering geqo
threshold could do the work...
Thank you. Greetings. Long life, little spam and prosperity!
-----Mensaje original-----
De: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org]En nombre de Kevin
Grittner
Enviado el: lunes, 18 de julio de 2005 14:58
Para: pgsql-performance@postgresql.org; dariop@unitech.com.ar
Asunto: Re: [PERFORM] join and query planner
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)
>>> snipp
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2005-07-19 21:41 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-07-06 21:54 join and query planner Dario Pudlo <dariop@unitech.com.ar>
2005-07-06 22:04 ` Dario <dario_d_s@unitech.com.ar>
2005-07-06 22:34 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2005-07-11 23:39 ` John A Meinel <john@arbash-meinel.com>
2005-07-12 14:35 ` Bruno Wolff III <bruno@wolff.to>
2005-07-18 17:57 Re: join and query planner Kevin Grittner <Kevin.Grittner@wicourts.gov>
2005-07-18 19:24 ` Dario <dario_d_s@unitech.com.ar>
2005-07-18 20:47 Re: join and query planner Kevin Grittner <Kevin.Grittner@wicourts.gov>
2005-07-19 21:41 ` Dario <dario_d_s@unitech.com.ar>
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