pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Weird 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