Artikel ini adalah kelanjutan dari cerita tentang baru di PostgreSQL 12. Kami sudah menganalisis SQL / JSON (JSONPath patch) dalam artikel
"Apa yang mereka membeku pada fitur membeku 2019. Bagian I. JSONPath" , sekarang giliran CTE.
Cte
CTE adalah Common Table Expression - ekspresi tabel umum, mereka juga disebut DENGAN konstruk. Sebenarnya, ini adalah pembuatan tabel sementara, tetapi hanya ada untuk satu permintaan, dan bukan untuk sesi. Mereka dapat diakses di dalam permintaan ini. Permintaan seperti itu dibaca dengan baik, dapat dimengerti, mudah untuk memodifikasinya jika perlu. Ini adalah hal yang sangat populer, dan sudah lama ada di PostgreSQL.
Tetapi fasilitasnya bisa mahal. Masalahnya terkait dengan materialisasi dari ekspresi setelah AS di dalam DENGAN ... AS () konstruksi. Itu juga disebut ekspresi internal dan dievaluasi sebelum mulai menghitung sisanya, tidak dapat tertanam dalam kueri tingkat atas (tidak ada inlining). Merencanakan ungkapan ini tidak memperhitungkan sisa permintaan. Perilaku ini disebut penghalang untuk optimasi, atau pagar. Selain itu, materialisasi itu sendiri membutuhkan work_mem. Dan jika sampel besar, maka masalahnya dimulai (misalnya, ada
laporan oleh Ivan Frolkov di PGConf 2019).
Sembunyikan dan cari dengan pengoptimal, yang akan kami analisis di bawah ini, bukan bug secara umum, tetapi fitur. Tentu saja, ada beberapa situasi ketika penghitungan awal bagian ekspresi menghilangkan, katakanlah, operasi berulang yang tidak perlu dalam kueri rekursif. Di sisi lain, banyak pengembang menggunakan CTE sebagai pandangan tanpa memikirkan penghalang, dan sebagai hasilnya, permintaan dari CTE dieksekusi tidak hanya lebih lambat dari pada permintaan yang setara (tapi lebih canggih) dengan subquery, tetapi lebih lambat oleh perintah besarnya. Setelah mempertimbangkan pro dan kontra, komunitas mengambil langkah tegas: itu mengubah perilaku default.
Kami akan mengamati pekerjaan CTE di piring seperti itu:
CREATE TABLE xytable AS SELECT x, x AS y FROM generate_series(1,10000000) AS x; CREATE INDEX ON xytable(x,y);
Table "public.xytable" Column | Type | Collation | Nullable | Default --------------+---------+------------------+----------------+--------- x | integer | | | y | integer | | | Indexes: "xytable_x_y_idx" btree (x, y)
Mari kita mulai dengan permintaan sederhana:
SELECT * FROM xytable WHERE x=2 AND y>1; QUERY PLAN
Semuanya dianggap instan, hanya indeks yang digunakan.
Kueri dengan subquery yang menghitung sama, tetapi dengan sintaks sedikit lebih rumit:
SELECT * FROM (SELECT * FROM xytable WHERE y>1) AS t WHERE x=2; QUERY PLAN
Semuanya tertata, perhitungan indeks sangat cepat.
Dan sekarang satu lagi permintaan yang setara secara logis, tetapi dengan CTE:
WITH yy AS ( SELECT * FROM xytable WHERE y>1) SELECT * FROM yy WHERE x=2; QUERY PLAN
Penundaan seperti itu sudah terlihat oleh mata telanjang. Anda tidak akan minum kopi, tetapi ada cukup waktu untuk melihat surat (ketika kami memiliki versi ke-11 atau lebih awal).
Dan inilah yang terjadi: dalam kasus subqueries, optimizer segera menyadari bahwa kondisi x = 2 dan y> 1 dapat digabungkan menjadi satu filter dan dicari berdasarkan indeks. Dalam kasus CTE, optimizer tidak punya pilihan: ia harus terlebih dahulu menangani kondisi di dalam WITH ... AS build, mematerialisasikan hasilnya, dan baru kemudian bekerja.
Dan di sini intinya bukan bahwa materialisasi akan membutuhkan sumber daya: jika kondisinya adalah y <3, maka tidak jutaan catatan harus terwujud, tetapi hanya 2. Waktu yang mengerikan untuk kueri sederhana dihabiskan untuk pencarian berurutan, pengoptimal tidak dapat menggunakan pencarian indeks karena sehingga indeks komposit dibangun di atas x, dan hanya pada saat itu saja, dan dia tidak akan tahu apa-apa tentang kueri dengan kondisi x = 2 sampai dia memenuhi kondisi CTE internal. Itu di luar penghalang.
Jadi, sebelum PostgreSQL 12, standarnya adalah materialisasi, sekarang tidak ada. Kami meluncurkan permintaan yang sama berdasarkan versi baru. Barrier, seolah-olah, pengoptimal segera melihat seluruh permintaan:
WITH yy AS ( SELECT * FROM xytable WHERE y>1) SELECT * FROM yy WHERE x=2;
QUERY PLAN ------------------------------------------ Index Only Scan using xytable_x_y_idx1 on xytable (cost=0.43..8.46 rows=1 width=8) (actual time=0.015..0.016 rows=1 loops=1) Index Cond: ((x = 2) AND (y > 1)) Heap Fetches: 1 Planning Time: 0.067 ms Execution Time: 0.029 ms (5 rows)
Pengoptimal langsung belajar menggabungkan kondisi dalam urutan optimal - seperti halnya dengan subqueries.
Tetapi default adalah default, dan untuk kepemilikan lengkap dari situasi sekarang, dalam versi 12 ada materialisasi CTE yang terkontrol dan terkontrol:
WITH cte_name AS [NOT] MATERIALIZED
Mari terwujud:
EXPLAIN ANALYZE WITH yy AS MATERIALIZED ( SELECT * FROM xytable WHERE y>1) SELECT * FROM yy WHERE x=2;
QUERY PLAN --------------------------- CTE Scan on yy (cost=356423.68..581401.19 rows=49995 width=8) (actual time=661.038..3603.292 rows=1 loops=1) Filter: (x = 2) Rows Removed by Filter: 9999998 CTE yy -> Bitmap Heap Scan on cte (cost=187188.18..356423.68 rows=9999000 width=8) (actual time=661.032..2102.040 rows=9999999 loops=1) Recheck Cond: (y > 1) Heap Blocks: exact=44248 -> Bitmap Index Scan on xytable_x_y_idx1 (cost=0.00..184688.43 rows=9999000 width=0) (actual time=655.519..655.519 rows=9999999 loops=1) Index Cond: (y > 1) Planning Time: 0.086 ms Execution Time: 3612.840 ms (11 rows)
Semuanya seperti pada 11 dan sebelumnya, Anda dapat melihat mail dalam mode siaga hasil kueri. Kami melarang materialisasi, singkirkan penghalang:
EXPLAIN ANALYZE WITH yy AS NOT MATERIALIZED ( SELECT * FROM xytable WHERE y>1) SELECT * FROM yy WHERE x=2; QUERY PLAN
Sekali lagi, tidak ada jeda: itu penting secara instan.
Nuansa yang tersisa. Namun nuansa penting.
CTE terwujud secara default jika diakses lebih dari satu kali.
Sekilas, materialisasi dalam kasus semacam itu adalah keputusan yang masuk akal: mengapa menghitung hal yang sama dua kali. Dalam praktiknya, ini sering mengarah pada apa yang kami amati di atas. Untuk memaksa penolakan dari materialisasi, perlu untuk secara eksplisit memesan pengoptimal: TIDAK MATERIALISASI.
Kami mengeksekusi tanpa BUKAN permintaan MATERIALISASI dengan ganda DIMANA:
WITH yy AS ( SELECT * FROM xytable WHERE y > 1) SELECT ( SELECT count(*) FROM yy WHERE x=2), ( SELECT count(*) FROM yy WHERE x=2);
QUERY PLAN --------------------------------------------------------------------------- Result (actual time=3922.274..3922.275 rows=1 loops=1) CTE yy -> Seq Scan on xytable (actual time=0.023..1295.262 rows=9999999 loops=1) Filter: (y > 1) Rows Removed by Filter: 1 InitPlan 2 (returns $1) -> Aggregate (actual time=3109.687..3109.687 rows=1 loops=1) -> CTE Scan on yy (actual time=0.027..3109.682 rows=1 loops=1) Filter: (x = 2) Rows Removed by Filter: 9999998 InitPlan 3 (returns $2) -> Aggregate (actual time=812.580..812.580 rows=1 loops=1) -> CTE Scan on yy yy_1 (actual time=0.016..812.575 rows=1 loops=1) Filter: (x = 2) Rows Removed by Filter: 9999998 Planning Time: 0.136 ms Execution Time: 3939.848 ms (17 rows)
Dan sekarang kami akan secara eksplisit menulis larangan materialisasi:
WITH yy AS NOT MATERIALIZED ( SELECT * FROM xytable WHERE y > 1) SELECT ( SELECT count(*) FROM yy WHERE x=2), ( SELECT count(*) FROM yy WHERE x=2);
QUERY PLAN --------------------------------------------------------------------------- Result (actual time=0.035..0.035 rows=1 loops=1) InitPlan 1 (returns $0) -> Aggregate (actual time=0.024..0.024 rows=1 loops=1) -> Index Only Scan using xytable_x_y_idx on xytable (actual time=0.019..0.020 rows=1 loops=1) Index Cond: ((x = 2) AND (y > 1)) Heap Fetches: 1 InitPlan 2 (returns $1) -> Aggregate (actual time=0.006..0.006 rows=1 loops=1) -> Index Only Scan using xytable_x_y_idx on xytable cte_1 (actual time=0.004..0.005 rows=1 loops=1) Index Cond: ((x = 2) AND (y > 1)) Heap Fetches: 1 Planning Time: 0.253 ms Execution Time: 0.075 ms (13 rows)
Menulis CTE selalu dijalankan, dan CTE yang tidak dirujuk tidak pernah.
Ini terbukti dari rencana: not_executed tidak ada di dalamnya. Ini berlaku untuk versi sebelumnya, tetapi perlu diingat bahwa, dan (BUKAN) konstruk MATERIALISASI berlaku untuk ekspresi dieksekusi di versi 12.
EXPLAIN (COSTS OFF) WITH yy AS ( SELECT * FROM xytable WHERE y > 1), not_executed AS ( SELECT * FROM xytable), always_executed AS ( INSERT INTO xytable VALUES(2,2) RETURNING *) SELECT FROM yy WHERE x=2;
QUERY PLAN ----------------------------- CTE Scan on yy Filter: (x = 2) CTE yy -> Seq Scan on cte Filter: (y > 1) CTE always_executed -> Insert on cte cte_1 -> Result (5 rows)
Dan satu aturan lagi:
pertanyaan rekursif dengan DENGAN selalu terwujud.
Itu selalu, dan tidak secara default. Jika kami memesan pengoptimal: BUKAN MATERIALISASI, tidak akan ada kesalahan, dan materialisasi masih akan terjadi. Ini adalah keputusan yang sadar komunitas.
Kami akan mempertimbangkan pekerjaan rumah contoh ilustratif. Itu saja untuk hari ini.
Bagian dari tinjauan yang ditujukan untuk CTE baru ini menggunakan contoh dan fragmen dari laporan "Postgres 12 in Etudes", yang dibaca Oleg Bartunov di Saint Highload ++ di St. Petersburg pada 9 April tahun ini.Di seri berikutnya -
KNN .