logo标签列表

PostgreSQL的执行计划差异

sqlpostgresqlsql-execution-plan
5

这是我的第一篇文章...

我有一个查询需要的时间比我期望的要长（我们都希望能更快！） 根据我在WHERE子句中添加的内容，它可能会运行得更快。 我正在尝试理解为什么查询计划不同 并且 我能做些什么来提高整个查询的速度。

以下是查询1：

SELECT date_observed, base_value 
FROM device_read_data 
WHERE fk_device_rw_id IN 
(SELECT fk_device_rw_id FROM equipment_set_rw 
WHERE fk_equipment_set_id = CAST('ed151028-1fc0-11e3-b79f-47c0fd87d2b4' AS uuid))
AND date_observed 
BETWEEN '2013-12-01 07:45:00+00'::timestamptz
 AND '2014-01-01 07:59:59+00'::timestamptz
AND base_value ~ '[0-9]+(\.[0-9]+)?'
;

这里是查询计划#1：

"Hash Semi Join  (cost=11.65..5640243.59 rows=92194 width=16) (actual time=34.947..132522.023 rows=43609 loops=1)"
"  Hash Cond: (device_read_data.fk_device_rw_id = equipment_set_rw.fk_device_rw_id)"
"  ->  Seq Scan on device_read_data  (cost=0.00..5449563.56 rows=72157042 width=32) (actual time=0.844..123760.331 rows=71764376 loops=1)"
"        Filter: ((date_observed >= '2013-12-01 07:45:00+00'::timestamp with time zone)    AND (date_observed <= '2014-01-01 07:59:59+00'::timestamp with time zone) AND   ((base_value)::text ~ '[0-9]+(\.[0-9]+)?'::text))"
"        Rows Removed by Filter: 82135660"
"  ->  Hash  (cost=11.61..11.61 rows=3 width=16) (actual time=0.018..0.018 rows=1 loops=1)"
"        Buckets: 1024  Batches: 1  Memory Usage: 1kB"
"        ->  Bitmap Heap Scan on equipment_set_rw  (cost=4.27..11.61 rows=3 width=16) (actual time=0.016..0.016 rows=1 loops=1)"
"              Recheck Cond: (fk_equipment_set_id = 'ed151028-1fc0-11e3-b79f-47c0fd87d2b4'::uuid)"
"              ->  Bitmap Index Scan on uc_fk_equipment_set_id_fk_device_rw_id  (cost=0.00..4.27 rows=3 width=0) (actual time=0.011..0.011 rows=1 loops=1)"
"                    Index Cond: (fk_equipment_set_id = 'ed151028-1fc0-11e3-b79f-47c0fd87d2b4'::uuid)"
"Total runtime: 132530.290 ms"

这里是查询#2：
SELECT date_observed, base_value 
FROM device_read_data 
WHERE fk_device_rw_id IN 
(SELECT fk_device_rw_id FROM equipment_set_rw 
WHERE fk_equipment_set_id = CAST('ed151028-1fc0-11e3-b79f-47c0fd87d2b4' AS uuid))
AND date_observed 
BETWEEN  '2014-01-01 07:45:00+00'::timestamptz
 AND '2014-02-01 07:59:59+00'::timestamptz
AND base_value ~ '[0-9]+(\.[0-9]+)?'
;

这里是查询计划#2：

"Nested Loop  (cost=4.27..1869543.46 rows=20391 width=16) (actual time=0.041..2053.656 rows=12997 loops=1)"
"  ->  Bitmap Heap Scan on equipment_set_rw  (cost=4.27..9.73 rows=2 width=16) (actual time=0.015..0.017 rows=1 loops=1)"
"        Recheck Cond: (fk_equipment_set_id = 'ed151028-1fc0-11e3-b79f-47c0fd87d2b4'::uuid)"
"        ->  Bitmap Index Scan on uc_fk_equipment_set_id_fk_device_rw_id  (cost=0.00..4.27 rows=2 width=0) (actual time=0.010..0.010 rows=1 loops=1)"
"              Index Cond: (fk_equipment_set_id = 'ed151028-1fc0-11e3-b79f-47c0fd87d2b4'::uuid)"
"  ->  Index Scan using idx_device_read_data_date_observed_fk_device_rw_id on device_read_data  (cost=0.00..934664.91 rows=10195 width=32) (actual time=0.024..2050.656 rows=12997 loops=1)"
"        Index Cond: ((date_observed >= '2014-01-01 07:45:00+00'::timestamp with time zone) AND (date_observed <= '2014-02-01 07:59:59+00'::timestamp with time zone) AND (fk_device_rw_id = equipment_set_rw.fk_device_rw_id))"
"        Filter: ((base_value)::text ~ '[0-9]+(\.[0-9]+)?'::text)"
"Total runtime: 2055.068 ms"

我只修改了Where子句中的日期范围。你可以看到，在查询＃1中，表上执行了顺序扫描，而在查询＃2中执行了索引扫描。
我试图确定是什么原因导致了这种情况，但似乎找不到答案。
其他信息：
- (date_observed，fk_device_rw_id) 上有一个复合索引 - 从未在此表上进行删除操作。不需要自动清理。 - 我已经尝试过对该表进行了吸气处理，但没有效果。 - 我已重建该表上的索引。 - 我已分析此表。 - 该系统是Prod的副本，目前处于空闲状态。
系统信息：
- 在Linux上运行Postgres 9.2 - 16GB系统内存 - Shared_Buffers设置为4GB
还有哪些信息可以提供？我肯定有些事情漏掉了。
谢谢您的帮助。
编辑1：
我尝试了：set enable_seqscan = false 以下是解释计划结果：
"Hash Semi Join  (cost=2566484.50..7008502.81 rows=92194 width=16) (actual  time=18587.453..182228.966 rows=43609 loops=1)"
"  Hash Cond: (device_read_data.fk_device_rw_id = equipment_set_rw.fk_device_rw_id)"
"  ->  Bitmap Heap Scan on device_read_data  (cost=2566472.85..6817822.78 rows=72157042 width=32) (actual time=18562.247..172074.048 rows=71764376 loops=1)"
"        Recheck Cond: ((date_observed >= '2013-12-01 07:45:00+00'::timestamp with time zone) AND (date_observed <= '2014-01-01 07:59:59+00'::timestamp with time zone))"
"        Rows Removed by Index Recheck: 2102"
"        Filter: ((base_value)::text ~ '[0-9]+(\.[0-9]+)?'::text)"
"        Rows Removed by Filter: 12265137"
"        ->  Bitmap Index Scan on idx_device_read_data_date_observed_fk_device_rw_id  (cost=0.00..2548433.59 rows=85430682 width=0) (actual time=18556.228..18556.228 rows=84029513 loops=1)"
"              Index Cond: ((date_observed >= '2013-12-01 07:45:00+00'::timestamp with time zone) AND (date_observed <= '2014-01-01 07:59:59+00'::timestamp with time zone))"
"  ->  Hash  (cost=11.61..11.61 rows=3 width=16) (actual time=16.134..16.134 rows=1 loops=1)"
"        Buckets: 1024  Batches: 1  Memory Usage: 1kB"
"        ->  Bitmap Heap Scan on equipment_set_rw  (cost=4.27..11.61 rows=3 width=16) (actual time=16.128..16.129 rows=1 loops=1)"
"              Recheck Cond: (fk_equipment_set_id = 'ed151028-1fc0-11e3-b79f-47c0fd87d2b4'::uuid)"
"              ->  Bitmap Index Scan on uc_fk_equipment_set_id_fk_device_rw_id  (cost=0.00..4.27 rows=3 width=0) (actual time=16.116..16.116 rows=1 loops=1)"
"                    Index Cond: (fk_equipment_set_id = 'ed151028-1fc0-11e3-b79f-47c0fd87d2b4'::uuid)"
"Total runtime: 182244.181 ms"

正如预料的那样，查询花费的时间更长了。难道记录太多以至于无法加快速度吗？

我有哪些选择？

谢谢。

第二次修改

我尝试了重写的方法。恐怕结果与原始结果类似。以下是查询计划：

"Hash Join  (cost=11.65..6013386.19 rows=90835 width=16) (actual time=35.272..127965.785 rows=43609 loops=1)"
"  Hash Cond: (a.fk_device_rw_id = b.fk_device_rw_id)"
"  ->  Seq Scan on device_read_data a  (cost=0.00..5565898.74 rows=71450793 width=32) (actual time=13.050..119667.814 rows=71764376 loops=1)"
"        Filter: ((date_observed >= '2013-12-01 07:45:00+00'::timestamp with time zone) AND (date_observed <= '2014-01-01 07:59:59+00'::timestamp with time zone) AND ((base_value)::text ~ '[0-9]+(\.[0-9]+)?'::text))"
"        Rows Removed by Filter: 85426425"
"  ->  Hash  (cost=11.61..11.61 rows=3 width=16) (actual time=0.018..0.018 rows=1 loops=1)"
"        Buckets: 1024  Batches: 1  Memory Usage: 1kB"
"        ->  Bitmap Heap Scan on equipment_set_rw b  (cost=4.27..11.61 rows=3 width=16) (actual time=0.015..0.016 rows=1 loops=1)"
"              Recheck Cond: (fk_equipment_set_id = 'ed151028-1fc0-11e3-b79f-47c0fd87d2b4'::uuid)"
"              ->  Bitmap Index Scan on uc_fk_equipment_set_id_fk_device_rw_id  (cost=0.00..4.27 rows=3 width=0) (actual time=0.011..0.011 rows=1 loops=1)"
"                    Index Cond: (fk_equipment_set_id = 'ed151028-1fc0-11e3-b79f-47c0fd87d2b4'::uuid)"
"Total runtime: 127992.849 ms"

这似乎是一个简单的问题。从表中返回在特定日期范围内的记录。考虑到我现有的系统架构，也许在表中存在太多记录会对性能产生不利影响。

除非有其他建议，否则我可能需要采用分区方法。

感谢您到目前为止提供的帮助！

在文本上方添加了 explain.depesz.com 上计划的链接。感谢您提供如此详尽的问题。如果可以的话，我会给这个问题加上 +10 的评分。我会尽力找时间回来研究一下，目前正处于最后期限。 - Craig Ringer
谢谢你，Craig。我非常感激任何帮助。 - iamtheoracle
2个回答
2

我已经尝试了几种方法，目前对性能感到满意。

我将device_read_data表上的索引改为与原来相反的顺序。

原始索引：

CREATE UNIQUE INDEX idx_device_read_data_date_observed_fk_device_rw_id
  ON device_read_data
  USING btree (date_observed, fk_device_rw_id);

新索引：

CREATE UNIQUE INDEX idx_device_read_data_date_observed_fk_device_rw_id
  ON device_read_data
  USING btree (fk_device_rw_id, date_observed);

fk_device_rw_id列的基数较低。将该列放在索引的第一位有助于更快地过滤记录。

此外，请确保where子句中的列与复合索引中的顺序相同。（现在已经是这种情况了。）

我更改了date_observed列的统计信息，从而为查询规划程序提供更多信息。

最初它使用的是postgres默认值100。我将其设置为：

ALTER TABLE device_read_data ALTER COLUMN date_observed SET STATISTICS 1000;

以下是查询结果。要快得多...我可能可以通过其他统计数据进一步调整...但现在这样就可以了。我可能能够暂时延迟分区。
感谢您的帮助。
查询：
explain Analyze
SELECT date_observed, base_value 
FROM device_read_data 
WHERE fk_device_rw_id IN 
(SELECT fk_device_rw_id FROM equipment_set_rw 
WHERE fk_equipment_set_id = CAST('ed151028-1fc0-11e3-b79f-47c0fd87d2b4' AS uuid))
AND (date_observed >= '2013-12-01 07:45:00+00'::timestamptz AND date_observed <= '2014-        01-01 07:59:59+00'::timestamptz)
AND base_value ~ '[0-9]+(\.[0-9]+)?'
;

新查询计划：

"Nested Loop  (cost=1197.25..264699.54 rows=59694 width=16) (actual time=25.876..493.073 rows=43609 loops=1)"
"  ->  Bitmap Heap Scan on equipment_set_rw  (cost=4.27..9.73 rows=2 width=16) (actual time=0.018..0.019 rows=1 loops=1)"
"        Recheck Cond: (fk_equipment_set_id = 'ed151028-1fc0-11e3-b79f-47c0fd87d2b4'::uuid)"
"        ->  Bitmap Index Scan on uc_fk_equipment_set_id_fk_device_rw_id  (cost=0.00..4.27 rows=2 width=0) (actual time=0.012..0.012 rows=1 loops=1)"
"              Index Cond: (fk_equipment_set_id = 'ed151028-1fc0-11e3-b79f-47c0fd87d2b4'::uuid)"
"  ->  Bitmap Heap Scan on device_read_data  (cost=1192.99..132046.43 rows=29847 width=32) (actual time=25.849..486.701 rows=43609 loops=1)"
"        Recheck Cond: ((fk_device_rw_id = equipment_set_rw.fk_device_rw_id) AND (date_observed >= '2013-12-01 07:45:00+00'::timestamp with time zone) AND (date_observed <= '2014-01-01 07:59:59+00'::timestamp with time zone))"
"        Rows Removed by Index Recheck: 2076173"
"        Filter: ((base_value)::text ~ '[0-9]+(\.[0-9]+)?'::text)"
"        ->  Bitmap Index Scan on idx_device_read_data_date_observed_fk_device_rw_id  (cost=0.00..1185.53 rows=35640 width=0) (actual time=24.000..24.000 rows=43609 loops=1)"
"              Index Cond: ((fk_device_rw_id = equipment_set_rw.fk_device_rw_id) AND (date_observed >= '2013-12-01 07:45:00+00'::timestamp with time zone) AND (date_observed <= '2014-01-01 07:59:59+00'::timestamp with time zone))"
"Total runtime: 495.506 ms"
2
在你的第一个查询中，日期范围跨越整个月，而不是第二个查询中的一天。第一个查询中的日期范围与device_read_data表中约154M行数据中的72M行匹配，这几乎是该表中一半的行数。
对于这么多行数据来说，索引扫描通常比全表扫描慢（因为索引扫描必须读取索引页和数据页，获取这么多行数据所需的磁盘读取总数可能大于仅读取每个数据页）。
在运行第一个查询之前，可以将enable_seq_scan = false，以查看差异，并且如果你想冒险，请将explain作为explain (analyze, buffers) <query>运行，以查看在执行表扫描与索引扫描时需要读取多少个块。
编辑：针对您的特定问题，您可能可以使用部分索引来提高效率。您将不得不找出如何构建它们，使它们尽可能广泛地覆盖（编写每个问题的部分索引很诱人但是浪费），但您可以从以下内容开始：
create index idx_device_read_data_date_observed_base_value
on device_read_data (date_observed)
where base_value ~ '[0-9]+(\.[0-9]+)?'
;

该索引仅针对与base_value模式匹配的行构建。你最好比我们更清楚这是否是相当严格的条件（如果它减少了要考虑的行数，那对你来说是很好的）。

您还可以反转这个想法，根据该模式匹配的base_value建立索引，并使您的where条件类似于date_observed between '2013-12-01 and '2013-12-31'，每个月添加一个此类索引（这种方式很可能只会超出控制 - 我会切换到分区）。

另一个潜在的改进可能来自重写查询。以下是一种方法，可以消除IN条件，如果在给定fk_equipment_set_id中equipment_set_rw中没有fk_device_rw_id的重复项，则提供相同的结果。

SELECT a.date_observed, a.base_value 
FROM device_read_data a
       join equipment_set_rw b
         on a.fk_device_rw_id = b.fk_device_rw_id
WHERE b.fk_equipment_set_id = CAST('ed151028-1fc0-11e3-b79f-47c0fd87d2b4' AS uuid)
  AND a.date_observed BETWEEN '2014-01-01 07:45:00+00'::timestamptz
                            AND '2014-02-01 07:59:59+00'::timestamptz
  AND a.base_value ~ '[0-9]+(\.[0-9]+)?'
;
非常感谢您的反馈。实际上，这两个查询都是一个月的时间范围（yyyy-mm-dd）。查询1的记录数为：43609，查询2的记录数为：17318，device_read_data表格中的记录数为153900036。我会尝试您所建议的方法。 - iamtheoracle
我看错了，以为第二个只有一天。我添加了更具体的建议来尝试一些事情。 - yieldsfalsehood
再次感谢。我尝试了部分索引，但恐怕没有帮助。我不认为按月份建立索引的建议在目前是合理的。我有另一个数据库实例，我一直在构建分区组件...预计会出现这种性能问题。我试图让这个脚本的性能达到最佳状态，然后再转向分区选项。你认为在当前环境设置下，我已经达到了最佳性能吗？谢谢。 - iamtheoracle
我编辑了一种重写的方法 - 我意识到您只是为了获得几千个结果而访问那些数百万行数据中的绝大部分。我认为仍有希望避免分区。 - yieldsfalsehood
再次感谢，我已经在上面发布了结果。 - iamtheoracle
"在(date_observed，fk_device_rw_id)上有一个复合索引-那是唯一的索引吗？这是列指定的顺序吗？你可以创建两个单独的索引，分别在这些列上吗？" - yieldsfalsehood
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接