pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedWeird query speed
2+ messages / 2 participants
[nested] [flat]
* Weird query speed
@ 2004-01-15 16:02 Rigmor Ukuhe <rigmor.ukuhe@finestmedia.com>
0 siblings, 1 reply; 2+ messages in thread
From: Rigmor Ukuhe @ 2004-01-15 16:02 UTC (permalink / raw)
To: pgsql-performance
Almoust identical querys are having very different exec speed (Postgresql
7.2.4).
query: select "NP_ID" from a WHERE "NP_ID" > '0'
Index Scan using NP_ID_a on a (cost=0.00..13.01 rows=112 width=4) (actual
time=16.89..18.11 rows=93 loops=1)
Total runtime: 18.32 msec
-------------------------------------------------
query: select "NP_ID" from a WHERE "NP_ID" > '1'
Index Scan using NP_ID_a on a (cost=0.00..13.01 rows=112 width=4) (actual
time=0.08..1.36 rows=93 loops=1)
Total runtime: 1.56 msec
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Weird query speed
@ 2004-01-15 16:40 Tom Lane <tgl@sss.pgh.pa.us>
parent: Rigmor Ukuhe <rigmor.ukuhe@finestmedia.com>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2004-01-15 16:40 UTC (permalink / raw)
To: Rigmor Ukuhe <rigmor.ukuhe@finestmedia.com>; +Cc: pgsql-performance
"Rigmor Ukuhe" <rigmor.ukuhe@finestmedia.com> writes:
> query: select "NP_ID" from a WHERE "NP_ID" > '0' [is slow]
>
> query: select "NP_ID" from a WHERE "NP_ID" > '1' [is fast]
>
> There are about 37K rows and only about 100 of then are not "NP_ID" = 0
Yeah, it's scanning over all the zero values when you say "> 0" :-(
This is fixed for 7.5:
2003-12-20 20:23 tgl
* src/: backend/access/nbtree/nbtinsert.c,
backend/access/nbtree/nbtpage.c, backend/access/nbtree/nbtsearch.c,
include/access/nbtree.h: Improve btree's
initial-positioning-strategy code so that we never need to step
more than one entry after descending the search tree to arrive at
the correct place to start the scan. This can improve the behavior
substantially when there are many entries equal to the chosen
boundary value. Per suggestion from Dmitry Tkach, 14-Jul-03.
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2004-01-15 16:40 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-01-15 16:02 Weird query speed Rigmor Ukuhe <rigmor.ukuhe@finestmedia.com>
2004-01-15 16:40 ` 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