Menafsirkan waktu startup dan memvariasikan rencana untuk pemindaian seq Postgres

Question

Saat mengajukan pertanyaan baru - baru ini , beberapa komponen waktu startup misterius muncul di keluaran MENJELASKAN ANALISIS saya. Saya bermain lebih jauh, dan menemukan bahwa waktu startup turun mendekati 0 jika saya menghapus WHEREklausa regex .

Saya menjalankan skrip bash berikut sebagai tes:

for i in $(seq 1 10) do if (( $RANDOM % 2 == 0 ))
    then
        echo "Doing plain count"
        psql -e -c "EXPLAIN ANALYZE SELECT count(*) FROM ui_events_v2"
    else
        echo "Doing regex count"
        psql -e -c "EXPLAIN ANALYZE SELECT count(*) FROM ui_events_v2 WHERE page ~ 'foo'"
    fi
done

Kueri pertama mengembalikan hitungan ~ 30 juta, dan kueri kedua hanya menghitung 7 baris. Mereka berjalan pada replika baca PG 12.3 di RDS dengan aktivitas lain yang minimal. Kedua versi tersebut membutuhkan waktu yang kira-kira sama, seperti yang saya perkirakan. Berikut ini beberapa keluaran yang difilter dengan grep:

Doing plain count
               ->  Parallel Seq Scan on ui_events_v2  (cost=0.00..3060374.07 rows=12632507 width=0) (actual time=0.086..38622.215 rows=10114306 loops=3)
Doing regex count
               ->  Parallel Seq Scan on ui_events_v2  (cost=0.00..3091955.34 rows=897 width=0) (actual time=16856.679..41398.062 rows=2 loops=3)
Doing plain count
               ->  Parallel Seq Scan on ui_events_v2  (cost=0.00..3060374.07 rows=12632507 width=0) (actual time=0.162..39454.499 rows=10114306 loops=3)
Doing plain count
               ->  Parallel Seq Scan on ui_events_v2  (cost=0.00..3060374.07 rows=12632507 width=0) (actual time=0.036..39213.171 rows=10114306 loops=3)
Doing regex count
               ->  Parallel Seq Scan on ui_events_v2  (cost=0.00..3091955.34 rows=897 width=0) (actual time=12711.308..40015.734 rows=2 loops=3)
Doing plain count
               ->  Parallel Seq Scan on ui_events_v2  (cost=0.00..3060374.07 rows=12632507 width=0) (actual time=0.244..39277.683 rows=10114306 loops=3)
Doing regex count
^CCancel request sent

Jadi, beberapa pertanyaan:

1. Apa yang masuk ke komponen startup "waktu aktual" dalam pemindaian regex, dan mengapa jauh lebih besar? (10-20 detik vs 0-1)

2. Meskipun "biaya" dan "waktu" bukanlah unit yang sebanding, perencana tampaknya berpikir biaya awal harus 0 dalam semua kasus - apakah itu dibodohi?

3. Mengapa strateginya tampak berbeda? Kedua rencana menyebutkan Partial Aggregate, tetapi kueri regex mengatakan baris sebenarnya adalah 2, tetapi versi biasa mengatakan baris sebenarnya adalah ~ 10 juta (saya kira ini adalah semacam rata-rata antara 2 pekerja dan 1 pemimpin, berjumlah ~ 30 juta). Jika saya harus menerapkan ini sendiri, saya mungkin akan menjumlahkan hasil dari beberapa count(*)operasi, daripada menggabungkan baris dan menghitung - apakah rencananya menunjukkan bagaimana tepatnya melakukannya?

Jadi saya tidak menyembunyikan apa pun, di bawah ini adalah versi lengkap dari rencana kueri untuk masing-masing:

EXPLAIN ANALYZE SELECT count(*) FROM ui_events_v2
                                                                       QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=3093171.59..3093171.60 rows=1 width=8) (actual time=39156.499..39156.499 rows=1 loops=1)
   ->  Gather  (cost=3093171.37..3093171.58 rows=2 width=8) (actual time=39156.356..39157.850 rows=3 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Partial Aggregate  (cost=3092171.37..3092171.38 rows=1 width=8) (actual time=39154.405..39154.406 rows=1 loops=3)
               ->  Parallel Seq Scan on ui_events_v2  (cost=0.00..3060587.90 rows=12633390 width=0) (actual time=0.033..38413.690 rows=10115030 loops=3)
 Planning Time: 7.968 ms
 Execution Time: 39157.942 ms
(8 rows)


EXPLAIN ANALYZE SELECT count(*) FROM ui_events_v2 WHERE page ~ 'foo'
                                                                   QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=3093173.83..3093173.84 rows=1 width=8) (actual time=39908.495..39908.495 rows=1 loops=1)
   ->  Gather  (cost=3093173.61..3093173.82 rows=2 width=8) (actual time=39908.408..39909.848 rows=3 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Partial Aggregate  (cost=3092173.61..3092173.62 rows=1 width=8) (actual time=39906.317..39906.318 rows=1 loops=3)
               ->  Parallel Seq Scan on ui_events_v2  (cost=0.00..3092171.37 rows=897 width=0) (actual time=17250.058..39906.308 rows=2 loops=3)
                     Filter: (page ~ 'foo'::text)
                     Rows Removed by Filter: 10115028
 Planning Time: 0.803 ms
 Execution Time: 39909.921 ms
(10 rows)

Answer 1

1. Rahasianya ada di Rows Removed by Filter: 10115028:

   Pemindaian berurutan membutuhkan waktu 17 detik untuk menemukan dan mengembalikan baris hasil pertama.

2. Pengoptimal tidak tahu berapa lama waktu yang dibutuhkan hingga baris pertama melewati filter. Karena tidak ada perbedaan untuk kualitas paket, itu hanya menetapkan biaya awal menjadi 0.

3. Kedua rencana bekerja sama: masing-masing dari tiga proses memindai sepertiga tabel dan menghitung baris ( Partial Aggregateyang mengembalikan 1 baris). Dalam kasus pertama, 10 juta baris dihitung oleh setiap pekerja.

   Ketiga angka ini dikumpulkan dan dijumlahkan di atas Finalize Aggregatepanggung.