pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feed2 Selects 1 is faster, why?
5+ messages / 5 participants
[nested] [flat]
* 2 Selects 1 is faster, why?
@ 2002-06-25 19:25 Eric <emayo@pozicom.net>
2002-06-26 15:34 ` Re: 2 Selects 1 is faster, why? Alvar Freude <alvar@a-blast.org>
0 siblings, 1 reply; 5+ messages in thread
From: Eric @ 2002-06-25 19:25 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: 2 Selects 1 is faster, why?
2002-06-25 19:25 2 Selects 1 is faster, why? Eric <emayo@pozicom.net>
@ 2002-06-26 15:34 ` Alvar Freude <alvar@a-blast.org>
2002-06-27 15:04 ` Re: 2 Selects 1 is faster, why? Masaru Sugawara <rk73@sea.plala.or.jp>
0 siblings, 1 reply; 5+ messages in thread
From: Alvar Freude @ 2002-06-26 15:34 UTC (permalink / raw)
To: Eric <emayo@pozicom.net>; pgsql-sql
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/
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: 2 Selects 1 is faster, why?
2002-06-25 19:25 2 Selects 1 is faster, why? Eric <emayo@pozicom.net>
2002-06-26 15:34 ` Re: 2 Selects 1 is faster, why? Alvar Freude <alvar@a-blast.org>
@ 2002-06-27 15:04 ` Masaru Sugawara <rk73@sea.plala.or.jp>
2002-06-27 23:40 ` Re: 2 Selects 1 is faster, why? Keith Gray <keith@heart.com.au>
0 siblings, 1 reply; 5+ messages in thread
From: Masaru Sugawara @ 2002-06-27 15:04 UTC (permalink / raw)
To: Alvar Freude <alvar@a-blast.org>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: 2 Selects 1 is faster, why?
2002-06-25 19:25 2 Selects 1 is faster, why? Eric <emayo@pozicom.net>
2002-06-26 15:34 ` Re: 2 Selects 1 is faster, why? Alvar Freude <alvar@a-blast.org>
2002-06-27 15:04 ` Re: 2 Selects 1 is faster, why? Masaru Sugawara <rk73@sea.plala.or.jp>
@ 2002-06-27 23:40 ` Keith Gray <keith@heart.com.au>
2002-06-28 03:52 ` Re: 2 Selects 1 is faster, why? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: Keith Gray @ 2002-06-27 23:40 UTC (permalink / raw)
To: Masaru Sugawara <rk73@sea.plala.or.jp>; +Cc: Alvar Freude <alvar@a-blast.org>; pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: 2 Selects 1 is faster, why?
2002-06-25 19:25 2 Selects 1 is faster, why? Eric <emayo@pozicom.net>
2002-06-26 15:34 ` Re: 2 Selects 1 is faster, why? Alvar Freude <alvar@a-blast.org>
2002-06-27 15:04 ` Re: 2 Selects 1 is faster, why? Masaru Sugawara <rk73@sea.plala.or.jp>
2002-06-27 23:40 ` Re: 2 Selects 1 is faster, why? Keith Gray <keith@heart.com.au>
@ 2002-06-28 03:52 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2002-06-28 03:52 UTC (permalink / raw)
To: Keith Gray <keith@heart.com.au>; +Cc: Masaru Sugawara <rk73@sea.plala.or.jp>; Alvar Freude <alvar@a-blast.org>; pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2002-06-28 03:52 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-06-25 19:25 2 Selects 1 is faster, why? Eric <emayo@pozicom.net>
2002-06-26 15:34 ` Alvar Freude <alvar@a-blast.org>
2002-06-27 15:04 ` Masaru Sugawara <rk73@sea.plala.or.jp>
2002-06-27 23:40 ` Keith Gray <keith@heart.com.au>
2002-06-28 03:52 ` Tom Lane <tgl@sss.pgh.pa.us>
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