Обсуждение: 2 Selects 1 is faster, why?

If I perform the following 2 selects, the first one is EXTREMELY slow where
the 2nd one is very fast.

(1) Slow

select o.orderid, ol.itemcode, ol.itemname

from orders o, orlines ol

where o.orderid = '1234' and ol.orderid = o.orderid;

(2) VERY FAST

select o.orderid, ol.itemcode, ol.itemname

from orders o, orlines ol

where o.orderid = '1234' and ol.orderid = '1234'

Why would 2 be so much faster?  I have ran the EXPLAIN on this and index
scans are being used.

NOTE: The actual queries return more information than this, but the
fundamental change shown above seems to give me the instant response I am
looking for. (1) takes about 60 seconds to run and (2) takes 3-5 seconds to
run.

Thanks, Eric

Hi,

-- Eric <emayo@pozicom.net> wrote:

> If I perform the following 2 selects, the first one is EXTREMELY slow
> where the 2nd one is very fast.

[...] 

> Why would 2 be so much faster?  I have ran the EXPLAIN on this and index
> scans are being used.

I guess, the first query has to search for all ol.orderid the equivalent
o.orderid; the second variant only has to search for '1234' in each
?.orderid, which is much faster.

Explizit joins should speed up this!


> NOTE: The actual queries return more information than this, but the
> fundamental change shown above seems to give me the instant response I am
> looking for. (1) takes about 60 seconds to run and (2) takes 3-5 seconds
> to run.

3-5 seconds seems very long to me, if indexes are used and the result is
not a set of thousands of rows; are you sure?


Ciao Alvar

-- 
// Unterschreiben!      http://www.odem.org/informationsfreiheit/
// Internet am Telefon: http://www.teletrust.info/
// Das freieste Medium? http://www.odem.org/insert_coin/
// Blaster:             http://www.assoziations-blaster.de/

On Wed, 26 Jun 2002 17:34:47 +0200
Alvar Freude <alvar@a-blast.org> wrote:


> Hi,
> 
> -- Eric <emayo@pozicom.net> wrote:
> 
> > If I perform the following 2 selects, the first one is EXTREMELY slow
> > where the 2nd one is very fast.
> 
> [...] 
> 
> > Why would 2 be so much faster?  I have ran the EXPLAIN on this and index
> > scans are being used.
> 
> I guess, the first query has to search for all ol.orderid the equivalent
> o.orderid; the second variant only has to search for '1234' in each
> ?.orderid, which is much faster.

You are right. And this type of optimising are not yet implemented.Tom said it in the prior discussions. 



Regards,
Masaru Sugawara

Masaru Sugawara wrote:

> 
>  You are right. And this type of optimising are not yet implemented.
>  Tom said it in the prior discussions. 
> 

...but is it true that if you place the filter clause first,
the join will not have to complete the whole table?

eg.

SELECT item.description, stock.available
FROM item, stock
WHERE item.itemid = '1234'
AND item.itemid=stock.itemid;

...would be more efficient than,

SELECT item.description, stock.available
FROM item, stock
WHERE item.itemid=stock.itemid
AND item.itemid = '1234';


-- 
Keith Gray

Technical Services Manager
Heart Consulting Services P/L
mailto:keith@heart.com.au

Keith Gray <keith@heart.com.au> writes:
> ...but is it true that if you place the filter clause first,
> the join will not have to complete the whole table?

PG's planner does not pay attention to the ordering of WHERE clauses;
it will do what it thinks best with them in any case.
        regards, tom lane