Question

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

Answer 1

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.

Answer 2

w/o explain analyze it is quite hard to tell why, but few points:

GP use very high random_page_cost and seq_page_cost is 1. The default value for random_page_cost is 100 which completely discourages optimiser to use index scans
enable_seqscan = off doesn t turn off seq scan completely. seq scans are just heavily
penalised
if table is small (100 - 10k records) it s might be faster to read it sequentially and ignore index at all

Answer 3

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.

Answer 4

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.
Delay 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.

友情链接