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

Re: Posible planner improvement?

From: Albert Cervera Areny <albert(at)sedifa(dot)com>
To: Mark Mielke <mark(at)mark(dot)mielke(dot)cc>
Cc: Richard Huxton <dev(at)archonet(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Posible planner improvement?
Date: 2008-05-21 11:30:16
Message-ID: 200805211330.16604.albert@sedifa.com (view raw or flat)
Thread:
Lists: 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



 

Attachment: queries.txt
Description: text/plain (3.4 KB)

In response to

Responses

pgsql-performance by date

Next:From: Albert Cervera ArenyDate: 2008-05-21 11:37:49
Subject: Re: Posible planner improvement?
Previous:From: Mark MielkeDate: 2008-05-21 11:24:55
Subject: Re: Posible planner improvement?

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