logo标签列表

为什么Postgres查询计划器受到LIMIT的影响？

sqlpostgresqlpostgresql-9.3query-planner
3
EXPLAIN ANALYZE SELECT     "alerts"."id", 
            "alerts"."created_at", 
            't1'::text AS src_table 
 FROM       "alerts" 
 INNER JOIN "devices" 
 ON         "devices"."id" = "alerts"."device_id" 
 INNER JOIN "sites" 
 ON         "sites"."id" = "devices"."site_id" 
 WHERE      "sites"."cloud_id" = 111
 AND        "alerts"."created_at" >= '2019-08-30'
 ORDER BY   "created_at" DESC limit 9;

 Limit  (cost=1.15..36021.60 rows=9 width=16) (actual time=30.505..29495.765 rows=9 loops=1)
  ->  Nested Loop  (cost=1.15..232132.92 rows=58 width=16) (actual time=30.504..29495.755 rows=9 loops=1)
        ->  Nested Loop  (cost=0.86..213766.42 rows=57231 width=24) (actual time=0.029..29086.323 rows=88858 loops=1)
              ->  Index Scan Backward using alerts_created_at_index on alerts  (cost=0.43..85542.16 rows=57231 width=24) (actual time=0.014..88.137 rows=88858 loops=1)
                    Index Cond: (created_at >= '2019-08-30 00:00:00'::timestamp without time zone)
              ->  Index Scan using devices_pkey on devices  (cost=0.43..2.23 rows=1 width=16) (actual time=0.016..0.325 rows=1 loops=88858)
                    Index Cond: (id = alerts.device_id)
        ->  Index Scan using sites_pkey on sites  (cost=0.29..0.31 rows=1 width=8) (actual time=0.004..0.004 rows=0 loops=88858)
              Index Cond: (id = devices.site_id)
              Filter: (cloud_id = 7231)
              Rows Removed by Filter: 1
Total runtime: 29495.816 ms

现在我们改为LIMIT 10：

 EXPLAIN ANALYZE SELECT     "alerts"."id", 
            "alerts"."created_at", 
            't1'::text AS src_table 
 FROM       "alerts" 
 INNER JOIN "devices" 
 ON         "devices"."id" = "alerts"."device_id" 
 INNER JOIN "sites" 
 ON         "sites"."id" = "devices"."site_id" 
 WHERE      "sites"."cloud_id" = 111
 AND        "alerts"."created_at" >= '2019-08-30'
 ORDER BY   "created_at" DESC limit 10;

Limit  (cost=39521.79..39521.81 rows=10 width=16) (actual time=1.557..1.559 rows=10 loops=1)
  ->  Sort  (cost=39521.79..39521.93 rows=58 width=16) (actual time=1.555..1.555 rows=10 loops=1)
        Sort Key: alerts.created_at
        Sort Method: quicksort  Memory: 25kB
        ->  Nested Loop  (cost=5.24..39520.53 rows=58 width=16) (actual time=0.150..1.543 rows=11 loops=1)
              ->  Nested Loop  (cost=4.81..16030.12 rows=2212 width=8) (actual time=0.137..0.643 rows=31 loops=1)
                    ->  Index Scan using sites_cloud_id_index on sites  (cost=0.29..64.53 rows=31 width=8) (actual time=0.014..0.057 rows=23 loops=1)
                          Index Cond: (cloud_id = 7231)
                    ->  Bitmap Heap Scan on devices  (cost=4.52..512.32 rows=270 width=16) (actual time=0.020..0.025 rows=1 loops=23)
                          Recheck Cond: (site_id = sites.id)
                          ->  Bitmap Index Scan on devices_site_id_index  (cost=0.00..4.46 rows=270 width=0) (actual time=0.006..0.006 rows=9 loops=23)
                                Index Cond: (site_id = sites.id)
              ->  Index Scan using alerts_device_id_index on alerts  (cost=0.43..10.59 rows=3 width=24) (actual time=0.024..0.028 rows=0 loops=31)
                    Index Cond: (device_id = devices.id)
                    Filter: (created_at >= '2019-08-30 00:00:00'::timestamp without time zone)
                    Rows Removed by Filter: 12
Total runtime: 1.603 ms

警报表有数百万条记录，其他表只有数千条。

我可以通过简单地不使用限制条件limit < 10 来优化查询。我不明白的是为什么LIMIT会影响性能。也许有比硬编码这个神奇数字 "10" 更好的方法。

1个回答
8
结果行数会影响PostgreSQL优化器，因为快速返回前几行的计划不一定是尽可能快地返回整个结果集的计划。
在你的情况下，PostgreSQL认为对于较小的LIMIT值，通过使用索引按照ORDER BY子句的顺序扫描alerts表并仅使用嵌套循环加入其他表，直到找到9行将更快。
这种策略的好处是它不必计算联接的完整结果，然后对其进行排序并且只保留前几个结果行。危险在于寻找9个匹配的行所需的时间比预期的要长，这就是影响你的原因： Index Scan Backward using alerts_created_at_index on alerts (cost=0.43..85542.16 rows=57231 width=24) (actual time=0.014..88.137 rows=88858 loops=1) 因此，PostgreSQL必须处理88858行并使用嵌套循环联接(如果需要经常循环，则效率低下)直到找到9行结果。这可能是因为它低估了条件的选择性，或者因为许多匹配的行恰好具有较低的created_at值。
数字10恰好是PostgreSQL认为不再使用该策略更加高效的分界点，这是一个随着数据库中数据的更改而变化的值。
你可以通过使用不匹配索引的ORDER BY子句来完全避免使用该计划：
ORDER BY (created_at + INTERVAL '0 days') DESC
1
感謝您提供詳盡的回答！我認為這個特定案例在網路上沒有太多曝光。 - Julius Žaromskis
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接