Reputation: 123
I have created a table T , which has an index created on a column C (btree index) , but when i run the select query this index is not being used.
Ex:
Explain select * from T where C='xxx'
This searches in all the segments sequentially , without considering the index which i have created.
I have used the following flags
enable_seqscan = off
enable_bitmapscan = off
enable_indexscan = on
Am I missing anything?Kindly explain?
Thanks Ganesh.R
Upvotes: 0
Views: 1717
Reputation: 3255
If your table is partitioned, there is one more possible reason for your index not being used: Your table has an index, but some or all of your partitions do not. You can check this be looking at the system view pg_indexes
. Are there entries for the partitions?
The root cause for this problem probably is that alter table TABLE add partition...
does not automatically create the indexes that you have defined for TABLE.
There are two solutions:
create index INDEXNAME on PARTITIONTABLENAME(ROWLIST..)
after adding a partition. Search the system view pg_partitions
the get the PARTITIONTABLENAME
! It's not the same as the PARTITIONNAME
.create index
on your table to the point where you have added all the partitions that you will need. That's because creating an index on a table automatically creates indices on all existing partitions.By the way, dropping the index on the table does not drop the indexes on the partitions.
I'm sorry I cannot give you any references to the GP Admin Guide as I'm either blind or wrong or the Admin Guide ignores this matter completely.
Upvotes: 0
Reputation: 2473
w/o explain analyze
it is quite hard to tell why, but few points:
enable_seqscan = off
doesn't turn off seq scan completely. seq scans are just heavilyUpvotes: 1
Reputation: 732
Unlike traditional RDBMSs, an index may not be the preferable way to access data in Greenplum.
Greenplum is tuned out of the box to prefer table scans over index scans, and you have to do a lot of tweaking to change that. You can set additional parameters to help the GP optimizer choose indexes including set enable_nestloop on, cpu_index_tuple_cost
and others. Check Appendix D of the GP Admin guide for a full set of tunable parameters.
Also, how do you have the data distributed? That can play a part in how the optimizer is choosing to process your query.
Upvotes: 0
Reputation:
It's possible that the query optimizer, for whatever reason, thinks that it's better not to use the index. Also, you may need to do an ANALYZE
on the table if the statistical metadata is out of date. See this article (or others like it) for more detailed information.
Upvotes: 1