From: | Tomasz Myrta <jasiek(at)klaster(dot)net> |
---|---|
To: | rigmor(dot)ukuhe(at)finestmedia(dot)com |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Index problem |
Date: | 2003-09-24 17:03:06 |
Message-ID: | 3F71CE4A.2090305@klaster.net |
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:
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
From | Date | Subject | |
---|---|---|---|
Next Message | Richard Jones | 2003-09-24 17:05:23 | Re: Performance issue |
Previous Message | Tom Lane | 2003-09-24 14:01:09 | Re: [HACKERS] osdl-dbt3 run results - puzzled by the execution |