postgresql - 基于第二张表数据的部分索引

Question

我有一个 master/detail 表的情况。对于主表中的每个条目，我在详细表中有几十个条目。

假设这些是我的表:

+-----------------------
| Master
+-----------------------
| master_key integer,
| insert_date timestamp 
+-----------------------

+-----------------------
| Detail
+-----------------------
| detail_key integer,
| master_key integer
| quantity numeric
| amount numeric
+-----------------------

我最常用的查询是这样的

SELECT extract(year from insert_date) AS Insert_Year, extract(month from insert_date) AS Insert_Month, sum(quantity) AS Quantity, sum(amount) AS Amount
FROM Master, Detail
WHERE (amount not null) and (insert_date <= '2016-12-31') and (insert_date >= '2015-01-01') and (Detail.master_key=Master.master_key)
GROUP BY Insert_Year, Insert_Month
ORDER BY Insert_Year ASC, Insert_Month ASC;

这个查询变得很慢，因为两个表中都有大量多年的数据。

因为我在两个表上都有索引，EXPLAIN ANALYZE 告诉我 INDEX 扫描采用的模式超过了洞执行时间的 80%。

"Sort  (cost=44013.52..44013.53 rows=1 width=19) (actual time=17073.129..17073.129 rows=16 loops=1)"
"  Sort Key: (date_part('year'::text, master.insert_date)), (date_part('month'::text, master.insert_date))"
"  Sort Method: quicksort  Memory: 26kB"
"  ->  HashAggregate  (cost=44013.49..44013.51 rows=1 width=19) (actual time=17073.046..17073.053 rows=16 loops=1)"
"        Group Key: date_part('year'::text, master.insert_date), date_part('month'::text, master.insert_date)"
"        ->  Nested Loop  (cost=0.43..43860.32 rows=15317 width=19) (actual time=0.056..15951.178 rows=843647 loops=1)"
"              ->  Seq Scan on master  (cost=0.00..18881.38 rows=3127 width=12) (actual time=0.027..636.202 rows=182338 loops=1)"
"                    Filter: ((date(insert_date) >= '2015-01-01'::date) AND (date(insert_date) <= '2016-12-31'::date))"
"                    Rows Removed by Filter: 443031"
"              ->  Index Scan using idx_detail_master_key on detail  (cost=0.43..7.89 rows=7 width=15) (actual time=0.055..0.077 rows=5 loops=182338)"
"                    Index Cond: (master_key = master.master_key)"
"                    Filter: (amount IS NOT NULL)"
"                    Rows Removed by Filter: 2"
"Planning time: 105.317 ms"
"Execution time: 17073.396 ms"

所以我的想法是通过定义部分索引来减少索引大小。在大多数情况下，只查询最近 2 年的数据。

所以我尝试了类似的方法:

CREATE INDEX idx_detail_table_master_keys
ON detail (master_key)
WHERE master_key in (SELECT master_key FROM master WHERE (extract( year from insert_date) = 2016) or (extract( year from insert_date) = 2015))

因为这不是最终版本，它应该只是一个概念证明，但它失败了。 PGAdmin 告诉我不允许在创建索引时使用子选择。

所以我的问题是:基于其他表的数据创建部分索引是否可行？

当然，如果有任何提示可以加速这样的星座，我将不胜感激。

问候

Answer 1

不可能基于另一个表的数据创建部分索引，因为像postgresql这样的关系数据库有三种连接方式:嵌套循环、散列连接和排序合并。所有这些方法都单独加载连接的表。由于数据库优化器决定将使用哪些方法以及连接将在哪个方向执行，因此创建覆盖另一个表数据的表索引没有意义。这就是为什么你不能定义这样的索引。有关此主题的更详细说明可在此处找到:http://use-the-index-luke.com/sql/join (以及在线图书的以下部分)

进一步优化见加百列使者的评论