postgresql – 为什么真空分析改变查询计划而分析没有?
我想在Postgres中利用仅索引扫描的强大功能,并尝试使用一个表:
CREATE TABLE dest.contexts ( id integer NOT NULL,phrase_id integer NOT NULL,lang character varying(5) NOT NULL,ranking_value double precision,index_min integer,index_max integer,case_sensitive boolean,is_enabled boolean,is_to_sync boolean NOT NULL DEFAULT true ); insert into dest.contexts select * from source.contexts; alter table dest.contexts add constraint pk_contexts primary key (id,phrase_id,lang); CREATE INDEX idx_contexts_ ON dest.contexts USING btree (id,is_enabled,lang,ranking_value,index_min,index_max,case_sensitive); 索引涵盖了我想在下一个查询中使用的所有列: explain analyze select ranking_value,case_sensitive from dest.contexts where id = 456 and is_enabled 我在创建后立即检查计划: Bitmap Heap Scan on contexts (cost=4.41..31.46 rows=12 width=17) (actual time=0.045..0.045 rows=0 loops=1) Recheck Cond: (id = 456) Filter: is_enabled -> Bitmap Index Scan on idx_contexts_ (cost=0.00..4.40 rows=12 width=0) (actual time=0.038..0.038 rows=0 loops=1) Index Cond: ((id = 456) AND (is_enabled = true)) Planning time: 0.631 ms Execution time: 0.093 ms 这很奇怪,但还行…… 几秒钟后它变成了另一个(autovacuum?) Index Scan using pk_contexts on contexts (cost=0.28..17.93 rows=6 width=17) (actual time=0.027..0.027 rows=0 loops=1) Index Cond: (id = 456) Filter: is_enabled Planning time: 0.185 ms Execution time: 0.070 ms 我尝试强制它使用仅索引扫描: analyze dest.contexts 但它没有改变任何东西.然后我做 vacuum verbose analyze dest.contexts; INFO: vacuuming "dest.contexts" INFO: index "pk_contexts" now contains 4845 row versions in 21 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted,0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index "idx_contexts_" now contains 4845 row versions in 37 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted,0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: "contexts": found 0 removable,4845 nonremovable row versions in 41 out of 41 pages DETAIL: 0 dead row versions cannot be removed yet. There were 0 unused item pointers. 0 pages are entirely empty. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: analyzing "dest.contexts" INFO: "contexts": scanned 41 of 41 pages,containing 4845 live rows and 0 dead rows; 4845 rows in sample,4845 estimated total rows 在这里,我终于得到了我想要的东西: Index Only Scan using idx_contexts_ on contexts (cost=0.28..4.40 rows=6 width=17) (actual time=0.014..0.014 rows=0 loops=1) Index Cond: ((id = 456) AND (is_enabled = true)) Filter: is_enabled Heap Fetches: 0 Planning time: 0.247 ms Execution time: 0.052 ms 以下是问题: >为什么不分析教它使用大的“全覆盖”指数?
Analyze负责收集统计数据,并分析对查询计划和计划成本的选择有影响.
索引不包含有关元组的可见性信息.因此,虽然select中的所有列都在索引中,但也会应用读取数据页和对结果集进行过滤.如果页面中显示所有元组,Postgres不需要额外的过滤操作. Postgres使用可见性图(vm)来实现这一点. 真空使用并更新可见性图,同时回收死元组.因此,如果可能,vacuum会更改查询计划以使用仅索引扫描. https://www.postgresql.org/docs/9.6/static/sql-vacuum.html https://www.postgresql.org/docs/9.6/static/storage-vm.html (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |