X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id E7BB4D1B4F9 for ; Wed, 24 Sep 2003 17:04:05 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 76190-06 for ; Wed, 24 Sep 2003 14:03:34 -0300 (ADT) Received: from serwer.skawsoft.com.pl (serwer.skawsoft.com.pl [213.25.37.66]) by svr1.postgresql.org (Postfix) with ESMTP id 2BC23D1B55D for ; Wed, 24 Sep 2003 14:03:33 -0300 (ADT) Received: from klaster.net (core-1.citynet.pl [80.48.135.69]) by serwer.skawsoft.com.pl (Postfix) with ESMTP id 7C9D62B3CD; Wed, 24 Sep 2003 19:03:19 +0200 (CEST) Message-ID: <3F71CE4A.2090305@klaster.net> Date: Wed, 24 Sep 2003 19:03:06 +0200 From: Tomasz Myrta User-Agent: Mozilla/5.0 (Windows; U; Win 9x 4.90; pl-PL; rv:1.5a) Gecko/20030718 X-Accept-Language: pl, en-us, en MIME-Version: 1.0 To: rigmor.ukuhe@finestmedia.com Cc: pgsql-performance@postgresql.org Subject: Re: Index problem References: In-Reply-To: Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200309/308 X-Sequence-Number: 3674 > Hi, > > I have a table containing columns: > > "END_DATE" timestamptz NOT NULL > "REO_ID" int4 NOT NULL > > and i am indexed "REO_ID" coulumn. > I have a query: > > select "REO_ID", "END_DATE" from "PRIORITY_STATISTICS" where "REO_ID" IN > ('112851' ,'112859' ,'112871' ,'112883' ,'112891' ,'112904' ,'112915' > ,'112924' ,'112939' ,'112947' ,'112960' ,'112984' ,'112999' ,'113013' > ,'113032' ,'113059' ,'113067' ,'113084' ,'113096' ,'113103' ,'113110' > ,'113117' ,'113125' ,'113132' ,'113139' ,'113146' ,'113153' ,'113160' > ,'113167' ,'113174' ,'113181' ,'113188' ,'113195' ,'113204' ,'113268' > ,'113279' ,'113294' ,'113302' ,'113317' ,'113340' ,'113358' ,'113385' > ,'113404' ,'113412' ,'113419' ,'113429' ,'113436' ,'113443' ,'113571' > ,'113636' ,'113649' ,'113689' ,'113705' ,'113744' ,'113755' ,'113724' > ,'113737' ,'113812' ,'113828' ,'113762' ,'113842' ,'113869' ,'113925' > ,'113976' ,'114035' ,'114044' ,'114057' ,'114070' ,'114084' ,'114094' > ,'114119' ) > > and it is _not_ using that index > > But following query (notice there are less id-s in WHERE clause, but rest is > same) > > select "REO_ID", "END_DATE" from "PRIORITY_STATISTICS" where "REO_ID" IN > ('112851' ,'112859' ,'112871' ,'112883' ,'112891' ,'112904' ,'112915' > ,'112924' ,'112939' ,'112947' ,'112960' ,'112984' ,'112999' ,'113013' > ,'113032' ,'113059' ,'113067' ,'113084' ,'113096' ,'113103' ,'113110' > ,'113117' ,'113125' ,'113132' ,'113139' ,'113146' ,'113153' ,'113160' > ,'113167' ,'113174' ,'113181' ,'113188' ,'113195' ,'113204' ,'113268' > ,'113279' ,'113294' ,'113302' ,'113317' ,'113340' ,'113358' ,'113385' > ,'113404' ,'113412' ,'113419' ,'113429' ,'113436' ,'113443' ,'113571' > ,'113636' ,'113649' ,'113689' ,'113705' ,'113744' ,'113755' ,'113724' > ,'113737' ) > > will _is_ using index: Why not. It's just because the second query is more selective. Probably you don't have too many rows in your table and Postgres thinks it's better (faster) to use sequential scan than index one. Regards, Tomasz Myrta