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屋!