Postgres 查询计划为什么行估计如此错误 [英] Postgres Query Plan why Row estimation is so wrong

问题描述

Postgres 版本:12

查询:解释(分析为真、详细为真、成本为真、缓冲区为真、时间为真)SELECT MIN("id"), MAX("id") FROM "public"."hotel_slot_inventory"WHERE ( "updated_at" >='2021-03-02 13:30:03' AND "updated_at" < '2021-03-03 06:15:19.127884' );

Query: EXPLAIN (ANALYZE TRUE, VERBOSE TRUE, COSTS TRUE, BUFFERS TRUE, TIMING TRUE) SELECT MIN("id"), MAX("id") FROM "public"."hotel_slot_inventory" WHERE ( "updated_at" >= '2021-03-02 13:30:03' AND "updated_at" < '2021-03-03 06:15:19.127884' );

查询计划:

 Result  (cost=512.44..512.45 rows=1 width=8) (actual time=57839.244..57839.250 rows=1 loops=1)
   Output: $0, $1
   Buffers: shared hit=1 read=454374 written=185
   I/O Timings: read=54564.571 write=2.686
   InitPlan 1 (returns $0)
     ->  Limit  (cost=0.57..256.22 rows=1 width=4) (actual time=57599.761..57599.764 rows=1 loops=1)
           Output: hotel_slot_inventory.id
           Buffers: shared read=453546 written=185
           I/O Timings: read=54330.640 write=2.686
           ->  Index Only Scan using hotel_slot_inventory_id_updated_at_idx on public.hotel_slot_inventory  (cost=0.57..3285663.29 rows=12852 width=4) (actual time=57599.758..57599.759 rows=1 loops=1)
                 Output: hotel_slot_inventory.id
                 Index Cond: ((hotel_slot_inventory.id IS NOT NULL) AND (hotel_slot_inventory.updated_at >= '2021-03-02 13:30:03'::timestamp without time zone) AND (hotel_slot_inventory.updated_at < '2021-03-03 06:15:19.127884'::timestamp without time zone))
                 Heap Fetches: 0
                 Buffers: shared read=453546 written=185
                 I/O Timings: read=54330.640 write=2.686
   InitPlan 2 (returns $1)
     ->  Limit  (cost=0.57..256.22 rows=1 width=4) (actual time=239.468..239.470 rows=1 loops=1)
           Output: hotel_slot_inventory_1.id
           Buffers: shared hit=1 read=828
           I/O Timings: read=233.931
           ->  Index Only Scan Backward using hotel_slot_inventory_id_updated_at_idx on public.hotel_slot_inventory hotel_slot_inventory_1  (cost=0.57..3285663.29 rows=12852 width=4) (actual time=239.465..239.465 rows=1 loops=1)
                 Output: hotel_slot_inventory_1.id
                 Index Cond: ((hotel_slot_inventory_1.id IS NOT NULL) AND (hotel_slot_inventory_1.updated_at >= '2021-03-02 13:30:03'::timestamp without time zone) AND (hotel_slot_inventory_1.updated_at < '2021-03-03 06:15:19.127884'::timestamp without time zone))
                 Heap Fetches: 0
                 Buffers: shared hit=1 read=828
                 I/O Timings: read=233.931
 Planning Time: 10.577 ms
 Execution Time: 57839.332 ms
(28 rows)

在两个 InitPlan 中，rows=12852 而实际 rows=1.这是为什么?Index only scan后单独添加了limit子句.

In both the InitPlan, rows=12852 while actual rows=1. why is that? Limit clause has been added separately after Index only scan.

编辑索引膨胀详细信息:

真实大小:3751411712 = 3.49 GB

real_size: 3751411712 = 3.49 GB

额外大小:470237184 = 448 MB

extra_size: 470237184 = 448 MB

额外比率:12.53

填充因子:90

膨胀大小:107053056 = 102 MB

bloat_size: 107053056 = 102 MB

膨胀比率:2.85

表格膨胀大小:膨胀大小:475283456 = 453 MB

Table Bloat Size: bloat_size: 475283456 = 453 MB

膨胀比率:5.088

推荐答案

看起来索引膨胀变形了.试试

Looks like an index is bloated out of shape. Try

REINDEX INDEX hotel_slot_inventory_id_updated_at_idx;

或

VACUUM (FULL) hotel_slot_inventory;

这篇关于Postgres 查询计划为什么行估计如此错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！