求助：优化含JOIN的SQL查询性能，缩短执行时长

数据库结构概述 #

• 表instruments：包含isin、trading_venue、start_trading_date、end_trading_date字段；同一工具可在多个场所交易，对应不同的起止交易日期；该表在isin及交易日期字段上建有索引。
• 视图instrument_start_end_dates：可获取每个工具isin在所有交易场所中的最早start_trading_date和最晚end_trading_date。
• 表trades：每条交易记录包含date_time时间戳字段及交易工具的isin；该表在date_time和isin字段上建有索引。

需求 #

获取所有交易日期处于对应工具交易起止日期范围内的交易记录。

当前查询语句 #

SELECT *
FROM execs_prop e
JOIN isin_start_end_dates ised on ised.isin = e.isin_cusip
WHERE DATE(e.date_time AT TIME ZONE 'UTC') = '2024-03-04'
  AND DATE(e.date_time AT TIME ZONE 'UTC') >= ised.start_trading_date 
  AND DATE(e.date_time AT TIME ZONE 'UTC') <= ised.end_trading_date
ORDER BY e.date_time;

问题 #

该查询返回结果符合预期，但执行速度较慢，通常需要约8分钟。

EXPLAIN ANALYZE结果 #

"Gather Merge  (cost=1348478.40..1431695.31 rows=713238 width=159) (actual time=199969.600..199999.190 rows=26784 loops=1)"
"  Workers Planned: 2"
"  Workers Launched: 2"
"  ->  Sort  (cost=1347478.38..1348369.92 rows=356619 width=159) (actual time=199727.735..199728.562 rows=8928 loops=3)"
"        Sort Key: e.date_time"
"        Sort Method: quicksort  Memory: 2664kB"
"        Worker 0:  Sort Method: quicksort  Memory: 2949kB"
"        Worker 1:  Sort Method: quicksort  Memory: 2359kB"
"        ->  Hash Join  (cost=1191253.51..1258520.93 rows=356619 width=159) (actual time=189405.742..199719.816 rows=8928 loops=3)"
"              Hash Cond: ((e.isin_cusip)::bpchar = instruments.isin)"
"              Join Filter: ((date(timezone('UTC'::text, e.date_time)) >= (min(instruments.first_trdg_date))) AND (date(timezone('UTC'::text, e.date_time))   Parallel Seq Scan on execs_prop e  (cost=0.00..53271.59 rows=3742 width=138) (actual time=0.094..279.061 rows=14837 loops=3)"
"                    Filter: (date(timezone('UTC'::text, date_time)) = '2024-03-04'::date)"
"                    Rows Removed by Filter: 583789"
"              ->  Hash  (cost=1147913.49..1147913.49 rows=2360642 width=21) (actual time=189392.680..189392.683 rows=9132958 loops=3)"
"                    Buckets: 65536 (originally 65536)  Batches: 256 (originally 64)  Memory Usage: 3585kB"
"                    ->  GroupAggregate  (cost=0.56..1124307.07 rows=2360642 width=21) (actual time=1.361..184799.653 rows=9132958 loops=3)"
"                          Group Key: instruments.isin"
"                          ->  Index Only Scan using idx_instruments_isin_first_last_date on instruments  (cost=0.56..996661.48 rows=13871889 width=21) (actual time=1.156..179268.392 rows=13498015 loops=3)"
"                                Heap Fetches: 26547906"
"Planning Time: 0.409 ms"
"Execution Time: 200001.209 ms"

临时更新1：

• 对instruments表执行VACUUM FULL；
• 删除execs_prop表现有索引，新增存储UTC时区日期的索引。

执行时间已从8分钟缩短至3分钟。

已注意到当前是将execs_prop表与动态视图isin_start_end_dates关联（每次查询都会重新生成并扫描整个instruments表），计划改用物化视图以进一步提升执行速度，后续会反馈更多信息。

内容的提问来源于stack exchange，提问作者Selim