blocks|key|4478919|text|无论出于何种原因，查询优化器都可能认为最好不要使用索引。此外，如果统计元数据已过期，则可能需要对表执行ANALYZE。有关更多详细信息，请参见this+article+(或others+like+it)。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|4478920|entityMap|0|LINK|mutability|MUTABLE|url|http://www.postgresonline.com/journal/archives/78-Why-is-my-index-not-being-used.html|1|http://www.google.com/search?sourceid=chrome&ie=UTF-8&q=postgresql%2520not%2520using%2520index^0|1F|7|1Z|C|0|2E|E|1|0^^$0|@$1|2|3|4|5|6|7|P|8|@$9|Q|A|R|B|C]]|D|@$9|S|A|T|1|U]|$9|V|A|W|1|X]]|E|$]]|$1|F|3|-4|5|6|7|Y|8|@]|D|@]|E|$]]]|G|$H|$5|I|J|K|E|$L|M]]|N|$5|I|J|K|E|$L|O]]]]

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 <code>ANALYZE</code> on the table if the statistical metadata is out of date. See <a href="http://www.postgresonline.com/journal/archives/78-Why-is-my-index-not-being-used.html" rel="nofollow">this article</a> (or <a href="http://www.google.com/search?sourceid=chrome&amp;ie=UTF-8&amp;q=postgresql%20not%20using%20index" rel="nofollow">others like it</a>) for more detailed information.

blocks|key|695798|text|w/o+explain+analyze很难说清楚原因，但有几点：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|695799|695800|GP使用非常高的random_page_cost，seq_page_cost是1。random_page_cost的默认值是100，这完全不鼓励优化器使用索引scans|unordered-list-item|695801|enable_seqscan+=+off不会完全关闭seq扫描。seq扫描非常繁重|695802|695803|如果表很小(100+-+10k条记录)，则按顺序读取它并忽略所有的索引可能会更快|695804|entityMap^0|4|F|0|0|0|0|K|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@$9|R|A|S|B|C]]|D|@]|E|$]]|$1|F|3|-4|5|6|7|T|8|@]|D|@]|E|$]]|$1|G|3|H|5|I|7|U|8|@]|D|@]|E|$]]|$1|J|3|K|5|I|7|V|8|@$9|W|A|X|B|C]]|D|@]|E|$]]|$1|L|3|-4|5|6|7|Y|8|@]|D|@]|E|$]]|$1|M|3|N|5|6|7|Z|8|@]|D|@]|E|$]]|$1|O|3|-4|5|6|7|10|8|@]|D|@]|E|$]]]|P|$]]

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

<ul>
<li>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</li>
<li><code>enable_seqscan = off</code> doesn't turn off seq scan completely. seq scans are just heavily 
penalised</li>
<li>if table is small (100 - 10k records) it's might be faster to read it sequentially and ignore index at all</li>
</ul>

blocks|key|695772|text|与传统的RDBMS不同，索引可能不是在Greenplum中访问数据的首选方法。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|695773|Greenplum是开箱即用的，它优先于表扫描而不是索引扫描，您必须进行大量调整才能改变这一点。您可以设置其他参数来帮助GP优化器选择索引，包括set+enable_nestloop+on,+cpu_index_tuple_cost和其他索引。查看GP+Admin+guide的附录D，了解完整的可调参数集。|offset|length|style|CODE|695774|另外，您是如何分发数据的？这在优化器选择处理查询的方式中起着一定的作用。|695775|entityMap^0|0|20|18|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|M|8|@$D|N|E|O|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|P|8|@]|9|@]|A|$]]|$1|J|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|K|$]]

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 <code>set enable_nestloop on, cpu_index_tuple_cost</code> 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.

blocks|key|949316|text|如果您的表已分区，则不使用索引还有一个可能的原因:您的表有索引，但部分或全部分区没有。您可以通过查看系统视图pg_indexes来查看此信息。是否有分区的条目？|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|949317|这个问题的根本原因可能是alter+table+TABLE+add+partition...没有自动创建您为表定义的索引。|949318|有两种解决方案：|949319|添加分区后的|949320|create+index+INDEXNAME+on+PARTITIONTABLENAME(ROWLIST..)。搜索系统视图并获取PARTITIONTABLENAME！它与表上的PARTITIONNAME.|unordered-list-item|949321|Delay+create+index不同，在这一点上，您已经添加了需要的所有分区。这是因为在表上创建索引会自动在所有现有分区上创建索引。|949322|949323|顺便说一句，删除表上的索引并不会删除分区上的索引。|949324|很抱歉，我不能给你任何参考的GP管理指南，因为我要么是盲目的，要么是错误的，或者管理指南完全忽略了这个问题。|949325|entityMap^0|1I|A|0|C|Y|0|0|0|0|1J|1T|I|2H|D|0|6|C|0|0|0|0^^$0|@$1|2|3|4|5|6|7|X|8|@$9|Y|A|Z|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|10|8|@$9|11|A|12|B|C]]|D|@]|E|$]]|$1|H|3|I|5|6|7|13|8|@]|D|@]|E|$]]|$1|J|3|K|5|6|7|14|8|@]|D|@]|E|$]]|$1|L|3|M|5|N|7|15|8|@$9|16|A|17|B|C]|$9|18|A|19|B|C]|$9|1A|A|1B|B|C]]|D|@]|E|$]]|$1|O|3|P|5|N|7|1C|8|@$9|1D|A|1E|B|C]]|D|@]|E|$]]|$1|Q|3|-4|5|6|7|1F|8|@]|D|@]|E|$]]|$1|R|3|S|5|6|7|1G|8|@]|D|@]|E|$]]|$1|T|3|U|5|6|7|1H|8|@]|D|@]|E|$]]|$1|V|3|-4|5|6|7|1I|8|@]|D|@]|E|$]]]|W|$]]

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 <code>pg_indexes</code>. Are there entries for the partitions?

The root cause for this problem probably is that <code>alter table TABLE add partition...</code> does not automatically create the indexes that you have defined for TABLE.

There are two solutions:

<ul>
<li><code>create index INDEXNAME on PARTITIONTABLENAME(ROWLIST..)</code> after adding a partition. Search the system view <code>pg_partitions</code> the get the <code>PARTITIONTABLENAME</code>! It's not the same as the <code>PARTITIONNAME</code>.</li>
<li>Delay <code>create index</code> 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.</li>
</ul>

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.

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:

<pre><code>Explain select * from T where C='xxx'
</code></pre>

This searches in all the segments sequentially , without considering the index which i have created.

I have used the following flags

<pre><code>enable_seqscan = off
enable_bitmapscan = off
enable_indexscan = on
</code></pre>

Am I missing anything?Kindly explain?

Thanks
Ganesh.R

Greenplum indexing not used on query

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我已经创建了一个表T，其中有一个在列C (btree index)上创建的索引，但是当我运行select查询时，这个索引并没有被使用。例如：Explain select * from T where C='xxx'这将按顺序在所有段中搜索，而不考虑我创建的索引。我使用了以下标志enable_seqscan = offenable_bitmapscan = offenable_indexscan =

问查询中未使用Greenplum索引
EN

回答 4

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问查询中未使用Greenplum索引EN

回答 4

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问查询中未使用Greenplum索引
EN