Postgres คำนวณค่าประมาณจำนวนแถวในระหว่างการวางแผนแบบสอบถามอย่างไร

Aug 20 2020

ฉันมีคำถามง่ายๆเช่น:

SELECT 
    * 
FROM 
    example 
WHERE 
    filter_1 = ? 
    AND filter_2 = ? 
LIMIT 
    10

ตารางมีขนาดค่อนข้างใหญ่ (ประมาณ 100 ล้านแถว) และมีดัชนีคล้ายกับรายการต่อไปนี้ (ดัชนีจริงมีคอลัมน์อีกหนึ่งคอลัมน์ทางด้านขวา แต่ไม่ควรสร้างความแตกต่าง):

CREATE INDEX example_idx
ON public.example 
USING btree (filter_1, filter_2, (...));

ตอนนี้เรามาอธิบายปัญหา: เมื่อฉันเรียกใช้แบบสอบถามของฉันในคำสั่งที่เตรียมไว้การดำเนินการ 5 รายการแรกใช้แผนกำหนดเอง จากนั้นแผนทั่วไปจะถูกมองว่ามีค่าใช้จ่ายน้อยกว่าและใช้เพื่อเตือนอายุการใช้งานของใบแจ้งยอดที่เตรียมไว้

นี่คือการวิเคราะห์อธิบายเมื่อ Postgres ใช้แผนแบบกำหนดเอง:

Limit  (cost=0.57..39.35 rows=10 width=78) (actual time=0.024..0.034 rows=8 loops=1)
  ->  Index Scan using example_idx on example c0  (cost=0.57..12345.29 rows=3183 width=78) (actual time=0.024..0.032 rows=8 loops=1)
        Index Cond: (((filter_1)::text = 'rare_value_1'::text) AND (filter_2 = 'frequent_value_2'::custom_enum))
Planning Time: 0.098 ms
Execution Time: 0.045 ms

นี่คือคำอธิบายเมื่อ Postgres ใช้แผนทั่วไป:

Limit  (cost=0.00..11.31 rows=10 width=78)
  ->  Seq Scan on example_idx c0  (cost=0.00..3469262.28 rows=3067235 width=78)
        Filter: (((filter_1)::text = $1) AND (filter_2 = $2))

ที่นี่เราจะเห็นได้อย่างชัดเจนว่าค่าใช้จ่ายของแผนทั่วไปนั้นต่ำกว่า

ปัญหาของฉันคือวิธีคำนวณจำนวนแถวโดยประมาณใน Index Scan และ Seq Scan

เอกสารอธิบายว่าฉันทำตามการคำนวณอย่างไรและหากฉันมาถึง3183ซึ่งเป็นจำนวนแถวโดยประมาณสำหรับแผนกำหนดเอง:

rare_value_1และfrequent_value_2ทั้งคู่อยู่ในรายการ MCV และความถี่ของพวกเขาคือ0.00002667และ0.99783ตามลำดับ 119622152นอกจากนี้จำนวนแถวของตารางประมาณ

0.00002667 * 0.99783 * 119622152 = 3183

คำถามที่เหลือคือจะทำอย่างไรสำหรับแผนทั่วไป?

ฉันพบว่าด้วยเหตุผลที่ไม่ทราบสาเหตุความถี่ MCV จะถูกละเว้น และ Postgresql เพียงแค่ดูที่n_distinctค่าสำหรับคอลัมน์ filter_1 และ filter_2 (13 และ 3 ตามลำดับ):

estimated row count = estimated total number of rows in table / ( n_distinct("filter_1") * n_distinct("filter_2") )
                    = 119622152 / (13 * 3)
                    = 3067235

คำถามของฉันคือทำไม? เหตุใด Postgresql จึงใช้วิธีดั้งเดิมในการประมาณจำนวนแถวเนื่องจากสามารถเข้าถึงสถิติที่ดีกว่าในรูปแบบของความถี่ MCV

Postgresql version: 11 (ดังนั้นเราจึงไม่สามารถใช้ตัวเลือก "force_custom_plan" ได้ในขณะนี้)

คำตอบ

2 LaurenzAlbe Aug 20 2020 at 19:34

ไม่มีวิธีใดที่ดีกว่าในการประมาณจำนวนแถวสำหรับแผนทั่วไป

หากคุณเลือกที่จะไปตามความถี่ของค่าที่พบบ่อยที่สุด (MCV) จะมีปัญหาหลายประการ:

  • คุณเลือก MCV แบบไหน?

  • การประมาณจะดีกว่าโดยสมมติว่าพารามิเตอร์ทั้งสองคือ MCV แต่นั่นเป็นข้อสันนิษฐานที่ไม่มีมูล หากทั้งคู่เป็นค่าที่หายากค่าประมาณจะอยู่ห่างจากเครื่องหมายมากกว่าค่าประมาณปัจจุบันด้วยซ้ำ

การเลือกค่าเฉลี่ยเป็นสิ่งที่ดีที่สุดที่เราทำได้นั่นคือสิ่งที่อยู่ตรงกลาง กรณีของคุณอยู่ไกลจากเครื่องหมายเนื่องจากค่าหนึ่งหายากมากซึ่งจะทำให้ค่าประมาณของแผนกำหนดเองลดลง แต่เนื่องจากมีค่าที่แตกต่างกันน้อยและหนึ่งในนั้นเกิดขึ้นบ่อยมากการคัดเลือกจึงไม่ดีโดยเฉลี่ยและแผนทั่วไป ค่าประมาณสูงเกินไป

คุณมีสองทางเลือก:

  • ใช้ไดนามิก SQL เพื่อให้คุณได้รับแผนกำหนดเองเสมอ

  • เปลี่ยนแบบสอบถามที่จะอ่าน

    WHERE filter_1 || '' = ?
    

    เพื่อให้ไม่สามารถใช้ดัชนีได้