为什么我在Postgresql中的视图不使用索引？

编程入门行业动态更新时间:2024-10-28 06:28:04

本文介绍了为什么我在Postgresql中的视图不使用索引？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！问题描述

我有一张大桌子（星表），其中有一个子集。我将子集实现为两个表的并集，在这里我使用了交叉索引。

问题是视图中的查询没有

对大表的查询很快：

从ucac4中选择count（*），其中rnm在其中（从grid_catalog限制5中选择ucac4_rnm）; count ------- 5 （1行）时间：12.132 ms

即使我希望对视图的查询也无法快速进行。

从grid_catalog_view中选择计数（*），并在其中标识（从grid_catalog限制5中选择ucac4_rnm）；计数 ------- 5 （1行）时间：1056237.045 ms

该查询的解释：

总计（成本= 23175810.51..23175810.52行= 1宽度= 0）->哈希联接（cost = 23081888.41..23172893.67行= 1166734宽度= 0）哈希条件：（ucac4.rnm = public.grid_catalog.ucac4_rnm）->唯一（费用= 23081888.17..23140224.87行= 2333468宽度= 44）->排序（成本= 23081888.17..23087721.84行= 2333468宽度= 44）排序键：ucac4.ra，ucac4。 dec，ucac4.pmrac，ucac4.pmdc，ucac4.rnm，ucac4.nest4，ucac4。 nest6，ucac4.nest7，public.grid_catalog.subset ->追加（费用= 63349.87..22763295.24行= 2333468宽度= 44）->哈希联接（cost = 63349.87..22738772.75 row = 2333467 width = 44）哈希Cond：（ucac4.rnm = public.grid_catalog.ucac4_rnm）->在ucac4上进行Seq扫描（成本= 0.00..16394129.04行= 455124304宽度= 40）->哈希（费用= 34048.69..34048.69行= 2344094宽度= 8）->在grid_catalog上进行序列扫描（cost = 0.00..34048.69 rows = 2344094 width = 8）过滤器：（petrov_prikey IS NULL）->哈希联接（cost = 415.51..1187.80行= 1宽度= 36）哈希条件：（petrov.prikey = public.grid_catalog.petrov_prikey）->在petrov上进行Seq扫描（费用= 0.00..709.15行= 7215宽度= 32）->散列（费用= 282.08..282.08行= 10675宽度= 8）->在grid_catalog上使用grid_catalog_petrov_prikey_idx进行索引扫描（cost = 0.00..282.08 row s = 10675 width = 8）->散列（费用= 0.18..0.18行= 5宽度= 4）-> HashAggregate（cost = 0.13..0.18 row = 5 width = 4）->限制（费用= 0.00..0.07行= 5宽度= 4）->在grid_catalog上进行序列扫描（cost = 0.00..34048.69 rows = 2354769 width = 4）（22行）

说明分析（在评论中的请求）为：

总计（费用= 23175810.51..23175810.52行= 1宽度= 0）（实际时间= 1625067.627..1625067.628行= 1循环= 1）->哈希联接（cost = 23081888.41..23172893.67行= 1166734宽度= 0）（实际时间= 1621395.200..1625067.618行= 5循环= 1）哈希Cond：（ucac4.rnm = public.grid_catalog.ucac4_rnm）->唯一（费用= 23081888.17..23140224.87行= 2333468宽度= 44）（实际时间= 1620897.932..1624102.849行= 1597359循环 = 1）->排序（cost = 23081888.17..23087721.84行= 2333468宽度= 44）（实际时间= 1620897.928..1622191.358行= 1597359 l oops = 1）排序键：ucac4.ra，ucac4。 dec ， ucac4.pmrac，ucac4.pmdc，ucac4.rnm，ucac4.nest4，ucac4.nest6，ucac4.nest7，pu blic.grid_catalog.subset 排序方法：外部合并磁盘：87536kB ->追加（费用= 63349.87..22763295.24行= 2333468宽度= 44）（实际时间= 890293.619..1613769.160行= 15973 59个循环= 1）->哈希联接（cost = 63349.87..22738772.75 row = 2333467 width = 44）（实际时间= 890293.617..1611550.313 r ows = 1590144循环= 1） Hash Cond：（ucac4.rnm = public。 grid_catalog.ucac4_rnm）->在ucac4上进行Seq扫描（成本= 0.00..16394129.04行= 455124304宽度= 40）（实际时间= 886086.630..1 359934.589行= 113780093循环= 1）->哈希（费用= 34048.69..34048.69行= 2344094宽度= 8）（实际时间= 4203.785..4203.785行= 1590 144循环= 1）->在grid_catalog上进行Seq扫描（成本= 0.00..34048.69行= 2344094宽度= 8）（实际时间= 0.014。 .2813.031行= 1590144循环= 1）过滤器：（petrov_prikey IS NULL）->哈希联接（成本= 415.51..1187.80行= 1宽度= 36）（实际时间= 101.604..165.749行= 7215循环= 1）哈希条件：（petrov.prikey = public.grid_catalog.petrov_prikey）->在petrov上进行Seq扫描（费用= 0.00..709.15行= 7215宽度= 32）（实际时间= 58.280..108.043行= 7 215个循环= 1）->哈希（费用= 282.08..282.08行= 10675宽度= 8）（实际时间= 43.276..43.276行= 7215循环= 1）->在grid_catalog上使用grid_catalog_petrov_prikey_idx进行索引扫描（成本= 0.00..282.08行 = 10675宽度= 8）（实际时间= 19.387..37.533行= 7215循环= 1）->哈希（成本= 0.18..0.18行= 5宽度= 4）（实际时间= 0.035..0.035行= 5循环= 1）-> HashAggregate（费用= 0.13..0.18行= 5宽度= 4）（实际时间= 0.026..0.030行= 5循环= 1）->限制（费用= 0.00..0.07行= 5宽度= 4）（实际时间= 0.009..0.017行= 5循环= 1）->在grid_catalog上进行Seq扫描（成本= 0.00..34048.69行= 2354769宽度= 4）（实际时间= 0.007..0.009行= 5个循环= 1）总运行时间：1625108.504 ms （24行）时间：1625466.830 ms

查看时间扫描视图：

从grid_catalog_view选择count（*）； count --------- 1597359 （1行）时间：1033732.786 ms

我的视图定义为：

PS1 =＃\d grid_catalog_view 查看 public.grid_catalog_view 列|类型修饰符 -------- + ------------------ + ----------- ra |双精度| dec |双精度| pmrac |整数| pmdc |整数| ident |整数| nest4 |整数| nest6 |整数| nest7 |整数| 子集|整数| 视图定义：选择ucac4.ra，ucac4。 dec，ucac4.pmrac，ucac4.pmdc，ucac4.rnm AS ident，ucac4.nest4，ucac4.nest6，ucac4.nest7，grid_catalog。来自ucac4，grid_catalog 的子集其中ucac4.rnm = grid_catalog.ucac4_rnm AND grid_catalog.petrov_prikey是NULL UNION 选择petrov.ra，petrov。 dec，0 AS pmrac ，0 AS pmdc，grid_catalog.petrov_prikey AS身份，petrov.nest4，petrov.nest6，petrov.nest7，grid_catalog.subset FROM petrov，grid_catalog WHERE petrov.prikey = grid_catalog.petrov_prikey AND grid_catalog.ucac4_rnm一片空白;

大表的定义为：

PS1 =＃ucac4 表 public.ucac4 列|类型修饰符 ---------- + ------------------ + ----------- radi | bigint | spdi | bigint | 岩浆| smallint | maga | smallint | sigmag | smallint | objt | smallint | cdf | smallint | ...删除的条目不具有相关性... ra |双精度| dec |双精度| x |双精度| 年|双精度| z |双精度| nest4 |整数| nest6 |整数| nest7 |整数| 索引： ucac4_pkey主键，btree（rnm） q3c_ucac4_idx btree（q3c_ang2ipix（ra， dec））群集 ucac4_nest4_idx btree（nest4） ucac4_nest6_idx btree（nest6） ucac4_nest7_idx btree（nest7）引用者：表 grid_catalog CONSTRAINT grid_catalog_ucac4_rnm_fkey FOREIGNS键（ucac44 ）

有人知道为什么我的索引似乎没有被使用吗？

解决方案

据我所知，这是postgres的局限性-很难避免以这种方式扫描联合表上的整个表。

请参阅：

www.postgresql-archive/Poor-plan-when-joining-against-a -union- contains-a-join-td5747690.html

和

www.postgresql-archive/Pushing-IN-subquery-down -through-UNION-ALL-td3398684.html

，也可能相关

https：// dba。 stackexchange/questions/47572/in-postgresql-9-3-union-view-with-where-clause-not-taken-in-accounting

基本上-我想您需要重新查看视图定义！抱歉，没有确定的解决方案。

I have a large table (star catalog), of which I have a subset. I implement the subset as a union of two tables, where I make use of a cross index.

The issue is that a query from the view doesn't seem to be using the index, the time takes the same as a scan through the table.

A query against the large table goes quickly:

select count(*) from ucac4 where rnm in (select ucac4_rnm from grid_catalog limit 5); count ------- 5 (1 row) Time: 12.132 ms

A query against the view does not go quickly, even though I would expect it to.

select count(*) from grid_catalog_view where ident in (select ucac4_rnm from grid_catalog limit 5); count ------- 5 (1 row) Time: 1056237.045 ms

An explain of this query yeilds:

Aggregate (cost=23175810.51..23175810.52 rows=1 width=0) -> Hash Join (cost=23081888.41..23172893.67 rows=1166734 width=0) Hash Cond: (ucac4.rnm = public.grid_catalog.ucac4_rnm) -> Unique (cost=23081888.17..23140224.87 rows=2333468 width=44) -> Sort (cost=23081888.17..23087721.84 rows=2333468 width=44) Sort Key: ucac4.ra, ucac4."dec", ucac4.pmrac, ucac4.pmdc, ucac4.rnm, ucac4.nest4, ucac4.nest6, ucac4.nest7, public.grid_catalog.subset -> Append (cost=63349.87..22763295.24 rows=2333468 width=44) -> Hash Join (cost=63349.87..22738772.75 rows=2333467 width=44) Hash Cond: (ucac4.rnm = public.grid_catalog.ucac4_rnm) -> Seq Scan on ucac4 (cost=0.00..16394129.04 rows=455124304 width=40) -> Hash (cost=34048.69..34048.69 rows=2344094 width=8) -> Seq Scan on grid_catalog (cost=0.00..34048.69 rows=2344094 width=8) Filter: (petrov_prikey IS NULL) -> Hash Join (cost=415.51..1187.80 rows=1 width=36) Hash Cond: (petrov.prikey = public.grid_catalog.petrov_prikey) -> Seq Scan on petrov (cost=0.00..709.15 rows=7215 width=32) -> Hash (cost=282.08..282.08 rows=10675 width=8) -> Index Scan using grid_catalog_petrov_prikey_idx on grid_catalog (cost=0.00..282.08 row s=10675 width=8) -> Hash (cost=0.18..0.18 rows=5 width=4) -> HashAggregate (cost=0.13..0.18 rows=5 width=4) -> Limit (cost=0.00..0.07 rows=5 width=4) -> Seq Scan on grid_catalog (cost=0.00..34048.69 rows=2354769 width=4) (22 rows)

The explain analyze (request in a comment) is:

Aggregate (cost=23175810.51..23175810.52 rows=1 width=0) (actual time=1625067.627..1625067.628 rows=1 loops=1) -> Hash Join (cost=23081888.41..23172893.67 rows=1166734 width=0) (actual time=1621395.200..1625067.618 rows=5 loops=1) Hash Cond: (ucac4.rnm = public.grid_catalog.ucac4_rnm) -> Unique (cost=23081888.17..23140224.87 rows=2333468 width=44) (actual time=1620897.932..1624102.849 rows=1597359 loops =1) -> Sort (cost=23081888.17..23087721.84 rows=2333468 width=44) (actual time=1620897.928..1622191.358 rows=1597359 l oops=1) Sort Key: ucac4.ra, ucac4."dec", ucac4.pmrac, ucac4.pmdc, ucac4.rnm, ucac4.nest4, ucac4.nest6, ucac4.nest7, pu blic.grid_catalog.subset Sort Method: external merge Disk: 87536kB -> Append (cost=63349.87..22763295.24 rows=2333468 width=44) (actual time=890293.619..1613769.160 rows=15973 59 loops=1) -> Hash Join (cost=63349.87..22738772.75 rows=2333467 width=44) (actual time=890293.617..1611550.313 r ows=1590144 loops=1) Hash Cond: (ucac4.rnm = public.grid_catalog.ucac4_rnm) -> Seq Scan on ucac4 (cost=0.00..16394129.04 rows=455124304 width=40) (actual time=886086.630..1 359934.589 rows=113780093 loops=1) -> Hash (cost=34048.69..34048.69 rows=2344094 width=8) (actual time=4203.785..4203.785 rows=1590 144 loops=1) -> Seq Scan on grid_catalog (cost=0.00..34048.69 rows=2344094 width=8) (actual time=0.014. .2813.031 rows=1590144 loops=1) Filter: (petrov_prikey IS NULL) -> Hash Join (cost=415.51..1187.80 rows=1 width=36) (actual time=101.604..165.749 rows=7215 loops=1) Hash Cond: (petrov.prikey = public.grid_catalog.petrov_prikey) -> Seq Scan on petrov (cost=0.00..709.15 rows=7215 width=32) (actual time=58.280..108.043 rows=7 215 loops=1) -> Hash (cost=282.08..282.08 rows=10675 width=8) (actual time=43.276..43.276 rows=7215 loops=1) -> Index Scan using grid_catalog_petrov_prikey_idx on grid_catalog (cost=0.00..282.08 rows =10675 width=8) (actual time=19.387..37.533 rows=7215 loops=1) -> Hash (cost=0.18..0.18 rows=5 width=4) (actual time=0.035..0.035 rows=5 loops=1) -> HashAggregate (cost=0.13..0.18 rows=5 width=4) (actual time=0.026..0.030 rows=5 loops=1) -> Limit (cost=0.00..0.07 rows=5 width=4) (actual time=0.009..0.017 rows=5 loops=1) -> Seq Scan on grid_catalog (cost=0.00..34048.69 rows=2354769 width=4) (actual time=0.007..0.009 rows= 5 loops=1) Total runtime: 1625108.504 ms (24 rows) Time: 1625466.830 ms

To see the time to scan through the view:

select count(*) from grid_catalog_view; count --------- 1597359 (1 row) Time: 1033732.786 ms

My view is defined as:

The large table is defined as:

Any idea why my index doesn't seem to be used?

解决方案

As far as I can see it's a limitation in postgres - it's hard to make it avoid scanning the whole table on a union in this way.

See:

www.postgresql-archive/Poor-plan-when-joining-against-a-union-containing-a-join-td5747690.html

and

www.postgresql-archive/Pushing-IN-subquery-down-through-UNION-ALL-td3398684.html

and also maybe related