Re: [PERFORM] Weird query speed

2004-01-15 Thread Tom Lane
"Rigmor Ukuhe" <[EMAIL PROTECTED]> 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

---(end of broadcast)---
TIP 8: explain analyze is your friend


[PERFORM] Weird query speed

2004-01-15 Thread Rigmor Ukuhe

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

>From where such difference comes?

There are about 37K rows and only about 100 of then are not "NP_ID" = 0

For a workaround i use WHERE "NP_ID" >= '1' and if works as speedy as '> 1'

Rigmor Ukuhe
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.560 / Virus Database: 352 - Release Date: 08.01.2004


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])