为什么我在Postgresql中的视图不使用索引？ [英] why does my view in postgresql not use the index?
本文介绍了为什么我在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的局限性-很难避免以这种方式扫描联合表上的整个表。
请参阅：
和
https://www.postgresql-archive.org/Pushing-IN-subquery-down -through-UNION-ALL-td3398684.html
，也可能相关
基本上-我想您需要重新查看视图定义！抱歉，没有确定的解决方案。
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:
PS1=# \d grid_catalog_view
View "public.grid_catalog_view"
Column | Type | Modifiers
--------+------------------+-----------
ra | double precision |
dec | double precision |
pmrac | integer |
pmdc | integer |
ident | integer |
nest4 | integer |
nest6 | integer |
nest7 | integer |
subset | integer |
View definition:
SELECT ucac4.ra, ucac4."dec", ucac4.pmrac, ucac4.pmdc, ucac4.rnm AS ident, ucac4.nest4, ucac4.nest6, ucac4.nest7, grid_catalog.subset
FROM ucac4, grid_catalog
WHERE ucac4.rnm = grid_catalog.ucac4_rnm AND grid_catalog.petrov_prikey IS NULL
UNION
SELECT petrov.ra, petrov."dec", 0 AS pmrac, 0 AS pmdc, grid_catalog.petrov_prikey AS ident, 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 IS NULL;
The large table is defined as:
PS1=# \d ucac4
Table "public.ucac4"
Column | Type | Modifiers
----------+------------------+-----------
radi | bigint |
spdi | bigint |
magm | smallint |
maga | smallint |
sigmag | smallint |
objt | smallint |
cdf | smallint |
... deleted entries not of relavance ...
ra | double precision |
dec | double precision |
x | double precision |
y | double precision |
z | double precision |
nest4 | integer |
nest6 | integer |
nest7 | integer |
Indexes:
"ucac4_pkey" PRIMARY KEY, btree (rnm)
"q3c_ucac4_idx" btree (q3c_ang2ipix(ra, "dec")) CLUSTER
"ucac4_nest4_idx" btree (nest4)
"ucac4_nest6_idx" btree (nest6)
"ucac4_nest7_idx" btree (nest7)
Referenced by:
TABLE "grid_catalog" CONSTRAINT "grid_catalog_ucac4_rnm_fkey" FOREIGN KEY (ucac4_rnm) REFERENCES ucac4(rnm)
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:
and
https://www.postgresql-archive.org/Pushing-IN-subquery-down-through-UNION-ALL-td3398684.html
and also maybe related
Basically - I guess you need to revisit your view definition! Sorry for no definitive solution.
这篇关于为什么我在Postgresql中的视图不使用索引？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
