Strange Postgresql Indexing Behavior
От | Brian Knox |
---|---|
Тема | Strange Postgresql Indexing Behavior |
Дата | |
Msg-id | Pine.LNX.4.40.0203131618190.25172-100000@tao.office.aol.com обсуждение исходный текст |
Ответ на | Re: Regular Expression for 'and' instead of 'or' (Tom Lane <tgl@sss.pgh.pa.us>) |
Ответы |
Re: Strange Postgresql Indexing Behavior
|
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: