Thread

2 Selects 1 is faster, why?

Eric <emayo@pozicom.net> — 2002-06-25T19:25:32Z

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

Re: 2 Selects 1 is faster, why?

Alvar Freude <alvar@a-blast.org> — 2002-06-26T15:34:47Z

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/

Re: 2 Selects 1 is faster, why?

Masaru Sugawara <rk73@sea.plala.or.jp> — 2002-06-27T15:04:07Z

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

Re: 2 Selects 1 is faster, why?

Keith Gray <keith@heart.com.au> — 2002-06-27T23:40:46Z

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

Re: 2 Selects 1 is faster, why?

Tom Lane <tgl@sss.pgh.pa.us> — 2002-06-28T03:52:40Z

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