为什么以下Postgres SQL查询需要这么长时间？

一尘不染

为什么以下Postgres SQL查询需要这么长时间？

sql

原始查询如下

SELECT "TIME", "TRADEPRICE"
  FROM "YEAR" where "DATE"='2010-03-01' 
  and "SECURITY"='STW.AX' 
  AND "TIME" < '10:16:00' 
  AND "TYPE" = 'TRADE'
  ORDER BY "TIME" ASC LIMIT 3

我建立了以下三个指标

Columns "DATE" DESC NULLS LAST
Columns "SECURITY" DESC NULLS LAST
Columns "TIME" DESC NULLS LAST

我不索引TYPE，因为它仅采用两个可能值之一

解释分析产生以下

"Limit  (cost=50291.28..50291.28 rows=3 width=16) (actual time=1794484.566..1794484.567 rows=3 loops=1)"
"  ->  Sort  (cost=50291.28..50291.29 rows=4 width=16) (actual time=1794484.562..1794484.563 rows=3 loops=1)"
"        Sort Key: "TIME""
"        Sort Method:  top-N heapsort  Memory: 25kB"
"        ->  Bitmap Heap Scan on "YEAR"  (cost=48569.54..50291.24 rows=4 width=16) (actual time=1794411.662..1794484.498 rows=20 loops=1)"
"              Recheck Cond: (("SECURITY" = 'STW.AX'::bpchar) AND ("DATE" = '2010-03-01'::date))"
"              Filter: (("TIME" < '10:16:00'::time without time zone) AND ("TYPE" = 'TRADE'::bpchar))"
"              ->  BitmapAnd  (cost=48569.54..48569.54 rows=430 width=0) (actual time=1794411.249..1794411.249 rows=0 loops=1)"
"                    ->  Bitmap Index Scan on security_desc  (cost=0.00..4722.94 rows=166029 width=0) (actual time=1793917.506..1793917.506 rows=1291933 loops=1)"
"                          Index Cond: ("SECURITY" = 'STW.AX'::bpchar)"
"                    ->  Bitmap Index Scan on date_desc  (cost=0.00..43846.35 rows=2368764 width=0) (actual time=378.698..378.698 rows=2317130 loops=1)"
"                          Index Cond: ("DATE" = '2010-03-01'::date)"
"Total runtime: 1794485.224 ms"

该数据库大约有10亿行在Core2Quad上运行，并在Ubuntu 64bit上具有8gig RAM。当然，此查询不应花费半小时

阅读 244

2021-05-23

共1个答案

一尘不染

该数据库大约有10亿行在Core2Quad上运行，并在Ubuntu 64bit上具有8gig RAM。当然，此查询不应花费半小时

由于您设置索引的方式，这花费了半个小时。

您的查询没有多列索引，可用于直接访问所需的行。接下来要做的最好的事情是对几乎没有选择的索引进行位图索引扫描，并对结果集进行top-3排序。

有问题的两个索引在安全性和日期方面分别产生130万行和230万行。合并它们会非常慢，因为您要随机查找一百万行并过滤每一行。

更糟的是，您的数据结构使得两个高度相关的字段（日期和时间）将被分别存储和操纵。这使查询计划者感到困惑，因为Postgres不会收集相关数据。因此，您的查询几乎总是诉诸于对大量数据进行过滤，并根据单独的条件对过滤后的数据集进行排序。

我建议进行以下更改：

更改表并添加类型为datetime的列timestamp with time zone。将您的日期和时间列合并到其中。
相应地删除日期和时间字段以及它们的索引。同时删除安全性索引。
创建索引（安全性，日期时间）。（除非您的订购条件中也包含这些子句，否则请不要把null放在首位/ null放在最后）。
如果您需要执行对日期或日期范围内所有交易进行统计的查询，则可以选择在（日期时间）或（日期时间，安全性）上添加单独的索引。
完成以上操作后，真空分析整个混乱情况。

然后，您可以像下面这样重写查询：

SELECT "TIME", "TRADEPRICE"
  FROM "YEAR"
  WHERE '2010-03-01 00:00:00' <= "DATETIME" AND "DATETIME" < '2010-03-01 10:16:00'
  AND "SECURITY"='STW.AX' 
  AND "TYPE" = 'TRADE'
  ORDER BY "DATETIME" ASC LIMIT 3

这将产生最乐观的计划：从（安全性，日期时间）的筛选索引扫描中检索前3行，我希望（因为您有10亿行）最多最多需要25毫秒。

2021-05-23