Skip site navigation (1) Skip section navigation (2)

Re: Posible planner improvement?

From: Decibel! <decibel(at)decibel(dot)org>
To: Richard Huxton <dev(at)archonet(dot)com>, PG Hackers <pgsql-hackers(at)postgresql(dot)org>
Cc: Luke Lonergan <LLonergan(at)greenplum(dot)com>, albert(at)sedifa(dot)com
Subject: Re: Posible planner improvement?
Date: 2008-05-24 18:49:46
Message-ID: 1870DB98-E4E2-4DB5-8620-DB4A8309A73C@decibel.org (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-performance
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(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828


In response to

Responses

pgsql-performance by date

Next:From: Decibel!Date: 2008-05-24 19:06:56
Subject: Re: I/O on select count(*)
Previous:From: Decibel!Date: 2008-05-24 17:55:15
Subject: Re: shared_buffers performance

pgsql-hackers by date

Next:From: Sushant SinhaDate: 2008-05-24 21:18:57
Subject: Re: [GENERAL] Fragments in tsearch2 headline
Previous:From: Dickson S. GuedesDate: 2008-05-24 14:09:47
Subject: Re: TODO item: Have psql show current values for a sequence

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group