Index problem

From: "Rigmor Ukuhe" <rigmor(dot)ukuhe(at)finestmedia(dot)com>
To: <pgsql-performance(at)postgresql(dot)org>
Subject: Index problem
Date: 2003-09-24 10:09:37
Message-ID: OEEHLFAIJHHMABJPIANIKEMACFAA.rigmor.ukuhe@finestmedia.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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:

Index Scan using PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id,
PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id,
PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id,
PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id,
PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id,
PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id,
PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id,
PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id,
PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id,
PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id,
PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id,
PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id,
PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id,
PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id,
PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id,
PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id,
PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id,
PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id,
PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id,
PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id,
PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id,
PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id,
PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id,
PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id,
PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id,
PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id,
PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id,
PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id,
PRIORITY_STATISTICS_reo_id on PRIORITY_STATISTICS (cost=0.00..394.06
rows=102 width=12)

What causes this behaviour? is there any workaround? Suggestions?

best,
Rigmor Ukuhe
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com)
Version: 6.0.515 / Virus Database: 313 - Release Date: 01.09.2003

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Jeff 2003-09-24 12:12:17 Re: LIKE query running slow
Previous Message Manfred Koizar 2003-09-24 09:14:19 Re: osdl-dbt3 run results - puzzled by the execution