Re: Index problem - Mailing list pgsql-performance

From Tomasz Myrta
Subject Re: Index problem
Date
Msg-id 3F71CE4A.2090305@klaster.net
Whole thread Raw
In response to Index problem  ("Rigmor Ukuhe" <rigmor.ukuhe@finestmedia.com>)
List 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


pgsql-performance by date:

Previous
From: Joseph Bove
Date:
Subject: Re: Performance issue
Next
From: Richard Jones
Date:
Subject: Re: Performance issue