pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPosible planner improvement?
14+ messages / 9 participants
[nested] [flat]
* Posible planner improvement?
@ 2008-05-21 10:28 Albert Cervera Areny <albert@sedifa.com>
0 siblings, 2 replies; 14+ messages in thread
From: Albert Cervera Areny @ 2008-05-21 10:28 UTC (permalink / raw)
To: pgsql-performance
I've got a query similar to this:
select * from t1, t2 where t1.id > 158507 and t1.id = t2.id;
That took > 84 minutes (the query was a bit longer but this is the part that
made the difference) after a little change the query took ~1 second:
select * from t1, t2 where t1.id > 158507 and t2.id > 158507 and t1.id =
t2.id;
The change is pretty simple and it seems (note I don't have a clue on how the
planner works) it'd be possible for the planner to make this assumption
itself. Do you think it is really feasible/appropiate?
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Posible planner improvement?
@ 2008-05-21 10:48 Richard Huxton <dev@archonet.com>
parent: Albert Cervera Areny <albert@sedifa.com>
1 sibling, 1 reply; 14+ messages in thread
From: Richard Huxton @ 2008-05-21 10:48 UTC (permalink / raw)
To: Albert Cervera Areny <albert@sedifa.com>; +Cc: pgsql-performance
Albert Cervera Areny wrote:
> I've got a query similar to this:
>
> select * from t1, t2 where t1.id > 158507 and t1.id = t2.id;
>
> That took > 84 minutes (the query was a bit longer but this is the part that
> made the difference) after a little change the query took ~1 second:
>
> select * from t1, t2 where t1.id > 158507 and t2.id > 158507 and t1.id =
> t2.id;
Try posting EXPLAIN ANALYSE SELECT ... for both of those queries and
we'll see why it's better at the second one.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Posible planner improvement?
@ 2008-05-21 11:11 Albert Cervera Areny <albert@sedifa.com>
parent: Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 14+ messages in thread
From: Albert Cervera Areny @ 2008-05-21 11:11 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-performance
A Dimecres 21 Maig 2008, Richard Huxton va escriure:
> Albert Cervera Areny wrote:
> > I've got a query similar to this:
> >
> > select * from t1, t2 where t1.id > 158507 and t1.id = t2.id;
> >
> > That took > 84 minutes (the query was a bit longer but this is the part
> > that made the difference) after a little change the query took ~1 second:
> >
> > select * from t1, t2 where t1.id > 158507 and t2.id > 158507 and t1.id =
> > t2.id;
>
> Try posting EXPLAIN ANALYSE SELECT ... for both of those queries and
> we'll see why it's better at the second one.
Right, attached an example of such a difference.
Attachments:
queries.txt (text/plain, 2.4K) download
# explain analyze select * from datos_venta dv, key_conta kc where dv.codigo > 158507 and kc.codigo> 158507 and dv.codigo=kc.codigo;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=7527.14..18203.25 rows=51415 width=253) (actual time=244.107..326.375 rows=11650 loops=1)
Hash Cond: (dv.codigo = kc.codigo)
-> Bitmap Heap Scan on datos_venta dv (cost=2852.89..9929.95 rows=154245 width=117) (actual time=4.801..25.601 rows=11650 loops=1)
Recheck Cond: (codigo > 158507)
-> Bitmap Index Scan on datos_venta_idx (cost=0.00..2852.89 rows=154245 width=0) (actual time=4.694..4.694 rows=11650 loops=1)
Index Cond: (codigo > 158507)
-> Hash (cost=4539.78..4539.78 rows=53788 width=136) (actual time=238.852..238.852 rows=2985 loops=1)
-> Bitmap Heap Scan on key_conta kc (cost=999.43..4539.78 rows=53788 width=136) (actual time=102.208..174.339 rows=2985 loops=1)
Recheck Cond: (codigo > 158507)
-> Bitmap Index Scan on key_conta_pkey (cost=0.00..999.43 rows=53788 width=0) (actual time=102.137..102.137 rows=2985 loops=1)
Index Cond: (codigo > 158507)
Total runtime: 347.949 ms
(12 rows)
# explain analyze select * from datos_venta dv, key_conta kc where dv.codigo > 158507 and dv.codigo=kc.codigo;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=7737.95..21585.51 rows=154245 width=253) (actual time=2227.439..2991.082 rows=11650 loops=1)
Hash Cond: (dv.codigo = kc.codigo)
-> Bitmap Heap Scan on datos_venta dv (cost=2852.89..9929.95 rows=154245 width=117) (actual time=22.179..149.532 rows=11650 loops=1)
Recheck Cond: (codigo > 158507)
-> Bitmap Index Scan on datos_venta_idx (cost=0.00..2852.89 rows=154245 width=0) (actual time=22.086..22.086 rows=11650 loops=1
Index Cond: (codigo > 158507)
-> Hash (cost=4481.65..4481.65 rows=161365 width=136) (actual time=2205.184..2205.184 rows=161365 loops=1)
-> Seq Scan on key_conta kc (cost=0.00..4481.65 rows=161365 width=136) (actual time=25.736..1351.018 rows=161365 loops=1)
Total runtime: 3307.621 ms
(9 rows)
* Re: Posible planner improvement?
@ 2008-05-21 11:24 Mark Mielke <mark@mark.mielke.cc>
parent: Albert Cervera Areny <albert@sedifa.com>
0 siblings, 1 reply; 14+ messages in thread
From: Mark Mielke @ 2008-05-21 11:24 UTC (permalink / raw)
To: Albert Cervera Areny <albert@sedifa.com>; +Cc: Richard Huxton <dev@archonet.com>; pgsql-performance
A Dimecres 21 Maig 2008, Richard Huxton va escriure:
>> Albert Cervera Areny wrote:
>>
>>> I've got a query similar to this:
>>>
>>> select * from t1, t2 where t1.id > 158507 and t1.id = t2.id;
>>>
>>> That took > 84 minutes (the query was a bit longer but this is the part
>>> that made the difference) after a little change the query took ~1 second:
>>>
>>> select * from t1, t2 where t1.id > 158507 and t2.id > 158507 and t1.id =
>>> t2.id;
>>>
>> Try posting EXPLAIN ANALYSE SELECT ... for both of those queries and
>> we'll see why it's better at the second one.
>>
Even if the estimates were off (they look a bit off for the first
table), the above two queries are logically identical, and I would
expect the planner to make the same decision for both.
I am curious - what is the result of:
select * from t1, t2 where t2.id > 158507 and t1.id = t2.id;
Is it the same speed as the first or second, or is a third speed entirely?
If t1.id = t2.id, I would expect the planner to substitute them freely
in terms of identities?
Cheers,
mark
--
Mark Mielke <mark@mielke.cc>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Posible planner improvement?
@ 2008-05-21 11:30 Albert Cervera Areny <albert@sedifa.com>
parent: Mark Mielke <mark@mark.mielke.cc>
0 siblings, 1 reply; 14+ messages in thread
From: Albert Cervera Areny @ 2008-05-21 11:30 UTC (permalink / raw)
To: Mark Mielke <mark@mark.mielke.cc>; +Cc: Richard Huxton <dev@archonet.com>; pgsql-performance
A Dimecres 21 Maig 2008, Mark Mielke va escriure:
> A Dimecres 21 Maig 2008, Richard Huxton va escriure:
> >> Albert Cervera Areny wrote:
> >>> I've got a query similar to this:
> >>>
> >>> select * from t1, t2 where t1.id > 158507 and t1.id = t2.id;
> >>>
> >>> That took > 84 minutes (the query was a bit longer but this is the part
> >>> that made the difference) after a little change the query took ~1
> >>> second:
> >>>
> >>> select * from t1, t2 where t1.id > 158507 and t2.id > 158507 and t1.id
> >>> = t2.id;
> >>
> >> Try posting EXPLAIN ANALYSE SELECT ... for both of those queries and
> >> we'll see why it's better at the second one.
>
> Even if the estimates were off (they look a bit off for the first
> table), the above two queries are logically identical, and I would
> expect the planner to make the same decision for both.
>
> I am curious - what is the result of:
>
> select * from t1, t2 where t2.id > 158507 and t1.id = t2.id;
>
> Is it the same speed as the first or second, or is a third speed entirely?
Attached the same file with the third result at the end. The result is worst
than the other two cases. Note that I've analyzed both tables but results are
the same. One order of magnitude between the two first queries.
>
> If t1.id = t2.id, I would expect the planner to substitute them freely
> in terms of identities?
>
> Cheers,
> mark
Attachments:
queries.txt (text/plain, 3.3K) download
# explain analyze select * from datos_venta dv, key_conta kc where dv.codigo > 158507 and kc.codigo> 158507 and dv.codigo=kc.codigo;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=7527.14..18203.25 rows=51415 width=253) (actual time=244.107..326.375 rows=11650 loops=1)
Hash Cond: (dv.codigo = kc.codigo)
-> Bitmap Heap Scan on datos_venta dv (cost=2852.89..9929.95 rows=154245 width=117) (actual time=4.801..25.601 rows=11650 loops=1)
Recheck Cond: (codigo > 158507)
-> Bitmap Index Scan on datos_venta_idx (cost=0.00..2852.89 rows=154245 width=0) (actual time=4.694..4.694 rows=11650 loops=1)
Index Cond: (codigo > 158507)
-> Hash (cost=4539.78..4539.78 rows=53788 width=136) (actual time=238.852..238.852 rows=2985 loops=1)
-> Bitmap Heap Scan on key_conta kc (cost=999.43..4539.78 rows=53788 width=136) (actual time=102.208..174.339 rows=2985 loops=1)
Recheck Cond: (codigo > 158507)
-> Bitmap Index Scan on key_conta_pkey (cost=0.00..999.43 rows=53788 width=0) (actual time=102.137..102.137 rows=2985 loops=1)
Index Cond: (codigo > 158507)
Total runtime: 347.949 ms
(12 rows)
# explain analyze select * from datos_venta dv, key_conta kc where dv.codigo > 158507 and dv.codigo=kc.codigo;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=7737.95..21585.51 rows=154245 width=253) (actual time=2227.439..2991.082 rows=11650 loops=1)
Hash Cond: (dv.codigo = kc.codigo)
-> Bitmap Heap Scan on datos_venta dv (cost=2852.89..9929.95 rows=154245 width=117) (actual time=22.179..149.532 rows=11650 loops=1)
Recheck Cond: (codigo > 158507)
-> Bitmap Index Scan on datos_venta_idx (cost=0.00..2852.89 rows=154245 width=0) (actual time=22.086..22.086 rows=11650 loops=1
Index Cond: (codigo > 158507)
-> Hash (cost=4481.65..4481.65 rows=161365 width=136) (actual time=2205.184..2205.184 rows=161365 loops=1)
-> Seq Scan on key_conta kc (cost=0.00..4481.65 rows=161365 width=136) (actual time=25.736..1351.018 rows=161365 loops=1)
Total runtime: 3307.621 ms
(9 rows)
# explain analyze select * from datos_venta dv, key_conta kc where kc.codigo > 158507 and dv.codigo=kc.codigo;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=146.18..14631.18 rows=8130 width=168) (actual time=1419.986..1555.975 rows=11650 loops=1)
Hash Cond: (dv.codigo = kc.codigo)
-> Seq Scan on datos_venta dv (cost=0.00..9776.35 rows=462735 width=59) (actual time=0.044..934.853 rows=462735 loops=1)
-> Hash (cost=139.09..139.09 rows=2835 width=109) (actual time=14.487..14.487 rows=2985 loops=1)
-> Index Scan using key_conta_pkey on key_conta kc (cost=0.00..139.09 rows=2835 width=109) (actual time=0.100..7.847 rows=2985
Index Cond: (codigo > 158507)
Total runtime: 1565.255 ms
(7 rows)
* Re: Posible planner improvement?
@ 2008-05-21 11:37 Albert Cervera Areny <albert@sedifa.com>
parent: Albert Cervera Areny <albert@sedifa.com>
0 siblings, 0 replies; 14+ messages in thread
From: Albert Cervera Areny @ 2008-05-21 11:37 UTC (permalink / raw)
To: pgsql-performance
A Dimecres 21 Maig 2008, Albert Cervera Areny va escriure:
> A Dimecres 21 Maig 2008, Mark Mielke va escriure:
> > A Dimecres 21 Maig 2008, Richard Huxton va escriure:
> > >> Albert Cervera Areny wrote:
> > >>> I've got a query similar to this:
> > >>>
> > >>> select * from t1, t2 where t1.id > 158507 and t1.id = t2.id;
> > >>>
> > >>> That took > 84 minutes (the query was a bit longer but this is the
> > >>> part that made the difference) after a little change the query took
> > >>> ~1 second:
> > >>>
> > >>> select * from t1, t2 where t1.id > 158507 and t2.id > 158507 and
> > >>> t1.id = t2.id;
> > >>
> > >> Try posting EXPLAIN ANALYSE SELECT ... for both of those queries and
> > >> we'll see why it's better at the second one.
> >
> > Even if the estimates were off (they look a bit off for the first
> > table), the above two queries are logically identical, and I would
> > expect the planner to make the same decision for both.
> >
> > I am curious - what is the result of:
> >
> > select * from t1, t2 where t2.id > 158507 and t1.id = t2.id;
> >
> > Is it the same speed as the first or second, or is a third speed
> > entirely?
>
> Attached the same file with the third result at the end. The result is
> worst than the other two cases. Note that I've analyzed both tables but
> results are the same. One order of magnitude between the two first queries.
Sorry, it's not worse than the other two cases as shown in the file. However,
after repetition it seems the other two seem to decrease more than the third
one whose times vary a bit more and some times take up to 5 seconds.
Other queries are running in the same machine, so take times with a grain of
salt. What's clear is that always there's a big difference between first and
second queries.
>
> > If t1.id = t2.id, I would expect the planner to substitute them freely
> > in terms of identities?
> >
> > Cheers,
> > mark
--
Albert Cervera Areny
Dept. Informàtica Sedifa, S.L.
Av. Can Bordoll, 149
08202 - Sabadell (Barcelona)
Tel. 93 715 51 11
Fax. 93 715 51 12
====================================================================
........................ AVISO LEGAL ............................
La presente comunicación y sus anexos tiene como destinatario la
persona a la que va dirigida, por lo que si usted lo recibe
por error debe notificarlo al remitente y eliminarlo de su
sistema, no pudiendo utilizarlo, total o parcialmente, para
ningún fin. Su contenido puede tener información confidencial o
protegida legalmente y únicamente expresa la opinión del
remitente. El uso del correo electrónico vía Internet no
permite asegurar ni la confidencialidad de los mensajes
ni su correcta recepción. En el caso de que el
destinatario no consintiera la utilización del correo electrónico,
deberá ponerlo en nuestro conocimiento inmediatamente.
====================================================================
........................... DISCLAIMER .............................
This message and its attachments are intended exclusively for the
named addressee. If you receive this message in error, please
immediately delete it from your system and notify the sender. You
may not use this message or any part of it for any purpose.
The message may contain information that is confidential or
protected by law, and any opinions expressed are those of the
individual sender. Internet e-mail guarantees neither the
confidentiality nor the proper receipt of the message sent.
If the addressee of this message does not consent to the use
of internet e-mail, please inform us inmmediately.
====================================================================
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Posible planner improvement?
@ 2008-05-21 11:52 Luke Lonergan <LLonergan@greenplum.com>
0 siblings, 1 reply; 14+ messages in thread
From: Luke Lonergan @ 2008-05-21 11:52 UTC (permalink / raw)
To: albert@sedifa.com; pgsql-performance
The problem is that the implied join predicate is not being propagated. This is definitely a planner deficiency.
- Luke
----- Original Message -----
From: pgsql-performance-owner@postgresql.org <pgsql-performance-owner@postgresql.org>
To: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Sent: Wed May 21 07:37:49 2008
Subject: Re: [PERFORM] Posible planner improvement?
A Dimecres 21 Maig 2008, Albert Cervera Areny va escriure:
> A Dimecres 21 Maig 2008, Mark Mielke va escriure:
> > A Dimecres 21 Maig 2008, Richard Huxton va escriure:
> > >> Albert Cervera Areny wrote:
> > >>> I've got a query similar to this:
> > >>>
> > >>> select * from t1, t2 where t1.id > 158507 and t1.id = t2.id;
> > >>>
> > >>> That took > 84 minutes (the query was a bit longer but this is the
> > >>> part that made the difference) after a little change the query took
> > >>> ~1 second:
> > >>>
> > >>> select * from t1, t2 where t1.id > 158507 and t2.id > 158507 and
> > >>> t1.id = t2.id;
> > >>
> > >> Try posting EXPLAIN ANALYSE SELECT ... for both of those queries and
> > >> we'll see why it's better at the second one.
> >
> > Even if the estimates were off (they look a bit off for the first
> > table), the above two queries are logically identical, and I would
> > expect the planner to make the same decision for both.
> >
> > I am curious - what is the result of:
> >
> > select * from t1, t2 where t2.id > 158507 and t1.id = t2.id;
> >
> > Is it the same speed as the first or second, or is a third speed
> > entirely?
>
> Attached the same file with the third result at the end. The result is
> worst than the other two cases. Note that I've analyzed both tables but
> results are the same. One order of magnitude between the two first queries.
Sorry, it's not worse than the other two cases as shown in the file. However,
after repetition it seems the other two seem to decrease more than the third
one whose times vary a bit more and some times take up to 5 seconds.
Other queries are running in the same machine, so take times with a grain of
salt. What's clear is that always there's a big difference between first and
second queries.
>
> > If t1.id = t2.id, I would expect the planner to substitute them freely
> > in terms of identities?
> >
> > Cheers,
> > mark
--
Albert Cervera Areny
Dept. Informàtica Sedifa, S.L.
Av. Can Bordoll, 149
08202 - Sabadell (Barcelona)
Tel. 93 715 51 11
Fax. 93 715 51 12
====================================================================
........................ AVISO LEGAL ............................
La presente comunicación y sus anexos tiene como destinatario la
persona a la que va dirigida, por lo que si usted lo recibe
por error debe notificarlo al remitente y eliminarlo de su
sistema, no pudiendo utilizarlo, total o parcialmente, para
ningún fin. Su contenido puede tener información confidencial o
protegida legalmente y únicamente expresa la opinión del
remitente. El uso del correo electrónico vía Internet no
permite asegurar ni la confidencialidad de los mensajes
ni su correcta recepción. En el caso de que el
destinatario no consintiera la utilización del correo electrónico,
deberá ponerlo en nuestro conocimiento inmediatamente.
====================================================================
........................... DISCLAIMER .............................
This message and its attachments are intended exclusively for the
named addressee. If you receive this message in error, please
immediately delete it from your system and notify the sender. You
may not use this message or any part of it for any purpose.
The message may contain information that is confidential or
protected by law, and any opinions expressed are those of the
individual sender. Internet e-mail guarantees neither the
confidentiality nor the proper receipt of the message sent.
If the addressee of this message does not consent to the use
of internet e-mail, please inform us inmmediately.
====================================================================
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Posible planner improvement?
@ 2008-05-21 13:09 Richard Huxton <dev@archonet.com>
parent: Luke Lonergan <LLonergan@greenplum.com>
0 siblings, 3 replies; 14+ messages in thread
From: Richard Huxton @ 2008-05-21 13:09 UTC (permalink / raw)
To: Luke Lonergan <LLonergan@greenplum.com>; +Cc: albert@sedifa.com; pgsql-performance
Luke Lonergan wrote:
> The problem is that the implied join predicate is not being
> propagated. This is definitely a planner deficiency.
IIRC only equality conditions are propagated and gt, lt, between aren't.
I seem to remember that the argument given was that the cost of
checking for the ability to propagate was too high for the frequency
when it ocurred.
Of course, what was true for code and machines of 5 years ago might not
be so today.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Posible planner improvement?
@ 2008-05-21 16:18 PFC <lists@peufeu.com>
parent: Richard Huxton <dev@archonet.com>
2 siblings, 0 replies; 14+ messages in thread
From: PFC @ 2008-05-21 16:18 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; Luke Lonergan <LLonergan@greenplum.com>; +Cc: albert@sedifa.com; pgsql-performance
On Wed, 21 May 2008 15:09:49 +0200, Richard Huxton <dev@archonet.com>
wrote:
> Luke Lonergan wrote:
>> The problem is that the implied join predicate is not being
>> propagated. This is definitely a planner deficiency.
>
> IIRC only equality conditions are propagated and gt, lt, between aren't.
> I seem to remember that the argument given was that the cost of
> checking for the ability to propagate was too high for the frequency
> when it ocurred.
>
> Of course, what was true for code and machines of 5 years ago might not
> be so today.
>
Suggestion : when executing a one-off sql statement, optimizer should try
to offer "best effort while being fast" ; when making a plan that will be
reused many times (ie PREPARE, functions...) planning time could be
muuuuch longer...
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Posible planner improvement?
@ 2008-05-21 16:22 Albert Cervera Areny <albert@sedifa.com>
parent: Richard Huxton <dev@archonet.com>
2 siblings, 0 replies; 14+ messages in thread
From: Albert Cervera Areny @ 2008-05-21 16:22 UTC (permalink / raw)
To: pgsql-performance
A Dimecres 21 Maig 2008, Richard Huxton va escriure:
> Luke Lonergan wrote:
> > The problem is that the implied join predicate is not being
> > propagated. This is definitely a planner deficiency.
>
> IIRC only equality conditions are propagated and gt, lt, between aren't.
> I seem to remember that the argument given was that the cost of
> checking for the ability to propagate was too high for the frequency
> when it ocurred.
>
> Of course, what was true for code and machines of 5 years ago might not
> be so today.
Hope this can be revisited given the huge difference in this case: 80 minutes
to 1 second.
--
Albert Cervera Areny
Dept. Informàtica Sedifa, S.L.
Av. Can Bordoll, 149
08202 - Sabadell (Barcelona)
Tel. 93 715 51 11
Fax. 93 715 51 12
====================================================================
........................ AVISO LEGAL ............................
La presente comunicación y sus anexos tiene como destinatario la
persona a la que va dirigida, por lo que si usted lo recibe
por error debe notificarlo al remitente y eliminarlo de su
sistema, no pudiendo utilizarlo, total o parcialmente, para
ningún fin. Su contenido puede tener información confidencial o
protegida legalmente y únicamente expresa la opinión del
remitente. El uso del correo electrónico vía Internet no
permite asegurar ni la confidencialidad de los mensajes
ni su correcta recepción. En el caso de que el
destinatario no consintiera la utilización del correo electrónico,
deberá ponerlo en nuestro conocimiento inmediatamente.
====================================================================
........................... DISCLAIMER .............................
This message and its attachments are intended exclusively for the
named addressee. If you receive this message in error, please
immediately delete it from your system and notify the sender. You
may not use this message or any part of it for any purpose.
The message may contain information that is confidential or
protected by law, and any opinions expressed are those of the
individual sender. Internet e-mail guarantees neither the
confidentiality nor the proper receipt of the message sent.
If the addressee of this message does not consent to the use
of internet e-mail, please inform us inmmediately.
====================================================================
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Posible planner improvement?
@ 2008-05-24 18:49 Decibel! <decibel@decibel.org>
parent: Richard Huxton <dev@archonet.com>
2 siblings, 1 reply; 14+ messages in thread
From: Decibel! @ 2008-05-24 18:49 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; PG Hackers <pgsql-hackers@postgresql.org>; +Cc: Luke Lonergan <LLonergan@greenplum.com>; albert@sedifa.com
Moving to -hackers...
On May 21, 2008, at 9:09 AM, Richard Huxton wrote:
> Luke Lonergan wrote:
>> The problem is that the implied join predicate is not being
>> propagated. This is definitely a planner deficiency.
>
> IIRC only equality conditions are propagated and gt, lt, between
> aren't. I seem to remember that the argument given was that the
> cost of checking for the ability to propagate was too high for the
> frequency when it ocurred.
>
> Of course, what was true for code and machines of 5 years ago might
> not be so today.
Definitely...
How hard would it be to propagate all conditions (except maybe
functions, though perhaps the new function cost estimates make that
more practical) in cases of equality?
For reference, the original query as posted to -performance:
select * from t1, t2 where t1.id > 158507 and t1.id = t2.id;
That took > 84 minutes (the query was a bit longer but this is the
part that made the difference) after a little change the query took
~1 second:
select * from t1, t2 where t1.id > 158507 and t2.id > 158507 and
t1.id = t2.id;
--
Decibel!, aka Jim C. Nasby, Database Architect decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828
Attachments:
smime.p7s (application/pkcs7-signature, 2.5K) download
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: [PERFORM] Posible planner improvement?
@ 2008-05-25 08:21 Stephen R. van den Berg <srb@cuci.nl>
parent: Decibel! <decibel@decibel.org>
0 siblings, 0 replies; 14+ messages in thread
From: Stephen R. van den Berg @ 2008-05-25 08:21 UTC (permalink / raw)
To: Decibel! <decibel@decibel.org>; +Cc: Richard Huxton <dev@archonet.com>; PG Hackers <pgsql-hackers@postgresql.org>; Luke Lonergan <LLonergan@greenplum.com>; albert@sedifa.com
Decibel! wrote:
>For reference, the original query as posted to -performance:
>select * from t1, t2 where t1.id > 158507 and t1.id = t2.id;
>That took > 84 minutes (the query was a bit longer but this is the
>part that made the difference) after a little change the query took
>~1 second:
Just out of curiosity, would predefining the order of join have solved
the issue, as in:
a. select * from t1 join t2 using(id) where t1.id > 158507;
vs.
b. select * from t2 join t1 using(id) where t1.id > 158507;
I'd expect a to be faster than b, is it?
--
Sincerely, srb@cuci.nl
Stephen R. van den Berg.
"Technology is stuff that doesn't work yet." -- Bran Ferren
"We no longer think of chairs as technology." -- Douglas Adams
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Posible planner improvement?
@ 2008-05-26 11:30 ITAGAKI Takahiro <itagaki.takahiro@oss.ntt.co.jp>
parent: Albert Cervera Areny <albert@sedifa.com>
1 sibling, 1 reply; 14+ messages in thread
From: ITAGAKI Takahiro @ 2008-05-26 11:30 UTC (permalink / raw)
To: Albert Cervera Areny <albert@sedifa.com>; +Cc: pgsql-performance
Hello Albert,
Albert Cervera Areny <albert@sedifa.com> wrote:
> I've got a query similar to this:
>
> select * from t1, t2 where t1.id > 158507 and t1.id = t2.id;
>
> That took > 84 minutes (the query was a bit longer but this is the part that
> made the difference) after a little change the query took ~1 second:
>
> select * from t1, t2 where t1.id > 158507 and t2.id > 158507 and t1.id =
> t2.id;
I had a similar problem here:
http://archives.postgresql.org/pgsql-general/2007-02/msg00850.php
and added a redundant inequality explicitly to make it work well.
I think it is worth trying to improve, but I'm not sure we can do it
against user defined types. Does postgres always require transitive law
to all types?
Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Posible planner improvement?
@ 2008-08-04 18:16 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: ITAGAKI Takahiro <itagaki.takahiro@oss.ntt.co.jp>
0 siblings, 0 replies; 14+ messages in thread
From: Kevin Grittner @ 2008-08-04 18:16 UTC (permalink / raw)
To: ITAGAKI Takahiro <itagaki.takahiro@oss.ntt.co.jp>; Albert Cervera Areny <albert@sedifa.com>; +Cc: pgsql-performance
>>> On Mon, May 26, 2008 at 6:30 AM, ITAGAKI Takahiro
<itagaki.takahiro@oss.ntt.co.jp> wrote:
> Albert Cervera Areny <albert@sedifa.com> wrote:
>
>> I've got a query similar to this:
>>
>> select * from t1, t2 where t1.id > 158507 and t1.id = t2.id;
>>
>> That took > 84 minutes (the query was a bit longer but this is the
part that
>> made the difference) after a little change the query took ~1
second:
>>
>> select * from t1, t2 where t1.id > 158507 and t2.id > 158507 and
t1.id =
>> t2.id;
>
> I had a similar problem here:
> http://archives.postgresql.org/pgsql-general/2007-02/msg00850.php
> and added a redundant inequality explicitly to make it work well.
>
> I think it is worth trying to improve, but I'm not sure we can do it
> against user defined types. Does postgres always require transitive
law
> to all types?
I've recently run into this. It would be a nice optimization,
if feasible.
-Kevin
^ permalink raw reply [nested|flat] 14+ messages in thread
end of thread, other threads:[~2008-08-04 18:16 UTC | newest]
Thread overview: 14+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-05-21 10:28 Posible planner improvement? Albert Cervera Areny <albert@sedifa.com>
2008-05-21 10:48 ` Richard Huxton <dev@archonet.com>
2008-05-21 11:11 ` Albert Cervera Areny <albert@sedifa.com>
2008-05-21 11:24 ` Mark Mielke <mark@mark.mielke.cc>
2008-05-21 11:30 ` Albert Cervera Areny <albert@sedifa.com>
2008-05-21 11:37 ` Albert Cervera Areny <albert@sedifa.com>
2008-05-26 11:30 ` ITAGAKI Takahiro <itagaki.takahiro@oss.ntt.co.jp>
2008-08-04 18:16 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2008-05-21 11:52 Re: Posible planner improvement? Luke Lonergan <LLonergan@greenplum.com>
2008-05-21 13:09 ` Richard Huxton <dev@archonet.com>
2008-05-21 16:18 ` PFC <lists@peufeu.com>
2008-05-21 16:22 ` Albert Cervera Areny <albert@sedifa.com>
2008-05-24 18:49 ` Decibel! <decibel@decibel.org>
2008-05-25 08:21 ` Stephen R. van den Berg <srb@cuci.nl>
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