X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 7AF7FD1D89C for ; Thu, 15 Jan 2004 16:41:32 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 10829-10 for ; Thu, 15 Jan 2004 12:41:01 -0400 (AST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id D4ECED1DF87 for ; Thu, 15 Jan 2004 12:40:58 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.10/8.12.10) with ESMTP id i0FGekpH022804; Thu, 15 Jan 2004 11:40:46 -0500 (EST) To: "Rigmor Ukuhe" Cc: pgsql-performance@postgresql.org Subject: Re: Weird query speed In-reply-to: References: Comments: In-reply-to "Rigmor Ukuhe" message dated "Thu, 15 Jan 2004 18:02:52 +0200" Date: Thu, 15 Jan 2004 11:40:46 -0500 Message-ID: <22803.1074184846@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200401/157 X-Sequence-Number: 5332 "Rigmor Ukuhe" 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