I'm having an interesting time trying to figure out some behavior with
postgresql indexes that I am trying to understand.
I have a table, historyticket. In that table, I have a column,
fk_opener_id, which is an integer column. I have an index (default btree
index) on the fk_opener_id column in that table.
When I select from this table with the following query:
select * from historyticket where fk_opener_id = ?
The query sometimes uses the index, and sometimes does a sequential scan.
I experimented for a little bit and found out that if the number of rows
that match the query is greater than a certain number (somewhere around
1000 rows from what I can tell) then the index is not used.
For example:
=============
testing=# explain select * from historyticket where fk_opener_id = 67;
NOTICE: QUERY PLAN:
Seq Scan on historyticket (cost=0.00..768.62 rows=1246 width=419)
testing=# select count(*) from historyticket where fk_opener_id = 67;
count
-------
1158
(1 row)
---------
testing=# explain select * from historyticket where fk_opener_id = 4;
NOTICE: QUERY PLAN:
Index Scan using fk_opener_id on historyticket (cost=0.00..179.47 rows=47
width=419)
testing=# select count(*) from historyticket where fk_opener_id = 4;
count
-------
79
(1 row)
===============
I did more queries and confirmed that when the number of rows returned is
below a certain number (I don't have enough data to determine the exact
number) the index is used, and when it is above a certain number, it is
not used.
Can anyone explain to me what is happening / why it is happening / how to
make the indexes work correctly?
Thanks.
Brian