การใช้คุณสมบัติทั้งหมดของดัชนีใน PostgreSQL

ในโลกของ Postgres ดัชนีมีความสำคัญต่อการนำทางอย่างมีประสิทธิภาพของที่เก็บข้อมูลของฐานข้อมูล (เรียกว่า "ฮีป") Postgres ไม่รองรับการทำคลัสเตอร์ และสถาปัตยกรรม MVCC ทำให้คุณต้องลงเอยด้วยทูเพิลเดียวกันหลายเวอร์ชัน ดังนั้นจึงเป็นสิ่งสำคัญมากที่จะสามารถสร้างและรักษาดัชนีที่มีประสิทธิภาพเพื่อรองรับแอปพลิเคชัน

ต่อไปนี้เป็นเคล็ดลับบางประการสำหรับการเพิ่มประสิทธิภาพและปรับปรุงการใช้ดัชนี

หมายเหตุ: ข้อความค้นหาที่แสดงด้านล่างใช้ได้กับรายการที่ไม่ได้แก้ไข ฐานข้อมูลตัวอย่าง pagila.

การใช้ดัชนีการครอบคลุม

มาดูคำขอแยกที่อยู่อีเมลสำหรับผู้ใช้ที่ไม่ได้ใช้งาน โต๊ะ customer มีคอลัมน์ activeและแบบสอบถามนั้นง่าย:

pagila=# EXPLAIN SELECT email FROM customer WHERE active=0;
                        QUERY PLAN
-----------------------------------------------------------
 Seq Scan on customer  (cost=0.00..16.49 rows=15 width=32)
   Filter: (active = 0)
(2 rows)

แบบสอบถามเรียกใช้ลำดับการสแกนตารางแบบเต็ม customer. มาสร้างดัชนีในคอลัมน์กัน active:

pagila=# CREATE INDEX idx_cust1 ON customer(active);
CREATE INDEX
pagila=# EXPLAIN SELECT email FROM customer WHERE active=0;
                                 QUERY PLAN
-----------------------------------------------------------------------------
 Index Scan using idx_cust1 on customer  (cost=0.28..12.29 rows=15 width=32)
   Index Cond: (active = 0)
(2 rows)

ช่วยได้การสแกนที่ตามมากลายเป็น "index scan". ซึ่งหมายความว่า Postgres จะสแกนดัชนี "idx_cust1" จากนั้นค้นหาฮีปตารางต่อไปเพื่ออ่านค่าของคอลัมน์อื่น (ในกรณีนี้คือคอลัมน์ email) ที่แบบสอบถามต้องการ

มีการแนะนำดัชนีที่ครอบคลุมใน PostgreSQL 11 อนุญาตให้คุณรวมคอลัมน์เพิ่มเติมหนึ่งคอลัมน์ขึ้นไปในดัชนี - ค่าของมันจะถูกเก็บไว้ในที่เก็บข้อมูลดัชนี

หากเราใช้ประโยชน์จากคุณสมบัตินี้และเพิ่มค่าอีเมลภายในดัชนี Postgres ก็ไม่จำเป็นต้องค้นหาค่าในกองตาราง email. มาดูกันว่าจะได้ผลหรือไม่:

pagila=# CREATE INDEX idx_cust2 ON customer(active) INCLUDE (email);
CREATE INDEX
pagila=# EXPLAIN SELECT email FROM customer WHERE active=0;
                                    QUERY PLAN
----------------------------------------------------------------------------------
 Index Only Scan using idx_cust2 on customer  (cost=0.28..12.29 rows=15 width=32)
   Index Cond: (active = 0)
(2 rows)

«Index Only Scan' บอกเราว่าตอนนี้เคียวรีต้องการเพียงดัชนี ซึ่งช่วยหลีกเลี่ยงดิสก์ I/O ทั้งหมดเพื่ออ่านฮีปของตาราง

ขณะนี้ดัชนีครอบคลุมใช้ได้เฉพาะกับ B-tree เท่านั้น อย่างไรก็ตาม ในกรณีนี้ ความพยายามในการบำรุงรักษาจะสูงขึ้น

การใช้ดัชนีบางส่วน

ดัชนีบางส่วนจัดทำดัชนีเฉพาะส่วนย่อยของแถวในตาราง ซึ่งช่วยประหยัดขนาดของดัชนีและทำให้สแกนได้เร็วขึ้น

สมมติว่าเราต้องการรับรายชื่อที่อยู่อีเมลของลูกค้าในแคลิฟอร์เนีย คำขอจะเป็นดังนี้:

SELECT c.email FROM customer c
JOIN address a ON c.address_id = a.address_id
WHERE a.district = 'California';
which has a query plan that involves scanning both the tables that are joined:
pagila=# EXPLAIN SELECT c.email FROM customer c
pagila-# JOIN address a ON c.address_id = a.address_id
pagila-# WHERE a.district = 'California';
                              QUERY PLAN
----------------------------------------------------------------------
 Hash Join  (cost=15.65..32.22 rows=9 width=32)
   Hash Cond: (c.address_id = a.address_id)
   ->  Seq Scan on customer c  (cost=0.00..14.99 rows=599 width=34)
   ->  Hash  (cost=15.54..15.54 rows=9 width=4)
         ->  Seq Scan on address a  (cost=0.00..15.54 rows=9 width=4)
               Filter: (district = 'California'::text)
(6 rows)

ดัชนีทั่วไปจะให้อะไรแก่เรา:

pagila=# CREATE INDEX idx_address1 ON address(district);
CREATE INDEX
pagila=# EXPLAIN SELECT c.email FROM customer c
pagila-# JOIN address a ON c.address_id = a.address_id
pagila-# WHERE a.district = 'California';
                                      QUERY PLAN
---------------------------------------------------------------------------------------
 Hash Join  (cost=12.98..29.55 rows=9 width=32)
   Hash Cond: (c.address_id = a.address_id)
   ->  Seq Scan on customer c  (cost=0.00..14.99 rows=599 width=34)
   ->  Hash  (cost=12.87..12.87 rows=9 width=4)
         ->  Bitmap Heap Scan on address a  (cost=4.34..12.87 rows=9 width=4)
               Recheck Cond: (district = 'California'::text)
               ->  Bitmap Index Scan on idx_address1  (cost=0.00..4.34 rows=9 width=0)
                     Index Cond: (district = 'California'::text)
(8 rows)

การสแกน address ถูกแทนที่ด้วยการสแกนดัชนี idx_address1แล้วสแกนกอง address.

เนื่องจากเป็นคำถามที่พบบ่อยและจำเป็นต้องได้รับการปรับให้เหมาะสม เราจึงสามารถใช้ดัชนีบางส่วน ซึ่งจัดทำดัชนีเฉพาะแถวที่มีที่อยู่ซึ่งเขตนั้นๆ ‘California’:

pagila=# CREATE INDEX idx_address2 ON address(address_id) WHERE district='California';
CREATE INDEX
pagila=# EXPLAIN SELECT c.email FROM customer c
pagila-# JOIN address a ON c.address_id = a.address_id
pagila-# WHERE a.district = 'California';
                                           QUERY PLAN
------------------------------------------------------------------------------------------------
 Hash Join  (cost=12.38..28.96 rows=9 width=32)
   Hash Cond: (c.address_id = a.address_id)
   ->  Seq Scan on customer c  (cost=0.00..14.99 rows=599 width=34)
   ->  Hash  (cost=12.27..12.27 rows=9 width=4)
         ->  Index Only Scan using idx_address2 on address a  (cost=0.14..12.27 rows=9 width=4)
(5 rows)

ตอนนี้แบบสอบถามอ่านเท่านั้น idx_address2 และไม่สัมผัสโต๊ะ address.

การใช้ดัชนีหลายค่า

บางคอลัมน์ที่จะจัดทำดัชนีอาจไม่มีประเภทข้อมูลสเกลาร์ ประเภทคอลัมน์ เช่น jsonb, arrays и tsvector มีค่าผสมหรือหลายค่า หากคุณต้องการจัดทำดัชนีคอลัมน์ดังกล่าว โดยปกติแล้วคุณจะต้องค้นหาค่าแต่ละค่าทั้งหมดในคอลัมน์เหล่านั้น

ลองหาชื่อเรื่องของภาพยนตร์ทั้งหมดที่มีการตัดต่อจากเทคที่ไม่ประสบความสำเร็จ โต๊ะ film มีคอลัมน์ข้อความที่เรียกว่า special_features. หากภาพยนตร์มี "คุณสมบัติพิเศษ" คอลัมน์นี้จะมีองค์ประกอบเป็นอาร์เรย์ข้อความ Behind The Scenes. หากต้องการค้นหาภาพยนตร์ดังกล่าวทั้งหมด เราจำเป็นต้องเลือกแถวทั้งหมดที่มีคำว่า "Behind The Scenes" ใด ค่าอาร์เรย์ special_features:

SELECT title FROM film WHERE special_features @> '{"Behind The Scenes"}';

ตัวดำเนินการรัง @> ตรวจสอบว่าด้านขวาเป็นส่วนย่อยของด้านซ้ายหรือไม่

ขอแผน:

pagila=# EXPLAIN SELECT title FROM film
pagila-# WHERE special_features @> '{"Behind The Scenes"}';
                           QUERY PLAN
-----------------------------------------------------------------
 Seq Scan on film  (cost=0.00..67.50 rows=5 width=15)
   Filter: (special_features @> '{"Behind The Scenes"}'::text[])
(2 rows)

ซึ่งขอการสแกนฮีปแบบเต็มโดยมีค่าใช้จ่าย 67

มาดูกันว่าดัชนี B-tree ปกติจะช่วยเราได้หรือไม่:

pagila=# CREATE INDEX idx_film1 ON film(special_features);
CREATE INDEX
pagila=# EXPLAIN SELECT title FROM film
pagila-# WHERE special_features @> '{"Behind The Scenes"}';
                           QUERY PLAN
-----------------------------------------------------------------
 Seq Scan on film  (cost=0.00..67.50 rows=5 width=15)
   Filter: (special_features @> '{"Behind The Scenes"}'::text[])
(2 rows)

ดัชนีไม่ได้รับการพิจารณาด้วยซ้ำ ดัชนี B-tree ไม่ทราบถึงการมีอยู่ขององค์ประกอบแต่ละรายการในค่าที่จัดทำดัชนี

เราต้องการดัชนี GIN

pagila=# CREATE INDEX idx_film2 ON film USING GIN(special_features);
CREATE INDEX
pagila=# EXPLAIN SELECT title FROM film
pagila-# WHERE special_features @> '{"Behind The Scenes"}';
                                QUERY PLAN
---------------------------------------------------------------------------
 Bitmap Heap Scan on film  (cost=8.04..23.58 rows=5 width=15)
   Recheck Cond: (special_features @> '{"Behind The Scenes"}'::text[])
   ->  Bitmap Index Scan on idx_film2  (cost=0.00..8.04 rows=5 width=0)
         Index Cond: (special_features @> '{"Behind The Scenes"}'::text[])
(4 rows)

ดัชนี GIN รองรับการแมปค่าเดี่ยวกับค่าผสมที่ทำดัชนี ส่งผลให้ต้นทุนแผนการค้นหาลดลงกว่าครึ่งหนึ่ง

การกำจัดดัชนีที่ซ้ำกัน

ดัชนีสะสมเมื่อเวลาผ่านไป และบางครั้งดัชนีใหม่อาจมีคำจำกัดความเดียวกันกับหนึ่งในดัชนีก่อนหน้า คุณสามารถใช้มุมมองแค็ตตาล็อกเพื่อรับคำจำกัดความของดัชนี SQL ที่มนุษย์อ่านได้ pg_indexes. คุณยังสามารถค้นหาคำจำกัดความที่เหมือนกันได้อย่างง่ายดาย:

 SELECT array_agg(indexname) AS indexes, replace(indexdef, indexname, '') AS defn
    FROM pg_indexes
GROUP BY defn
  HAVING count(*) > 1;
And here’s the result when run on the stock pagila database:
pagila=#   SELECT array_agg(indexname) AS indexes, replace(indexdef, indexname, '') AS defn
pagila-#     FROM pg_indexes
pagila-# GROUP BY defn
pagila-#   HAVING count(*) > 1;
                                indexes                                 |                                defn
------------------------------------------------------------------------+------------------------------------------------------------------
 {payment_p2017_01_customer_id_idx,idx_fk_payment_p2017_01_customer_id} | CREATE INDEX  ON public.payment_p2017_01 USING btree (customer_id
 {payment_p2017_02_customer_id_idx,idx_fk_payment_p2017_02_customer_id} | CREATE INDEX  ON public.payment_p2017_02 USING btree (customer_id
 {payment_p2017_03_customer_id_idx,idx_fk_payment_p2017_03_customer_id} | CREATE INDEX  ON public.payment_p2017_03 USING btree (customer_id
 {idx_fk_payment_p2017_04_customer_id,payment_p2017_04_customer_id_idx} | CREATE INDEX  ON public.payment_p2017_04 USING btree (customer_id
 {payment_p2017_05_customer_id_idx,idx_fk_payment_p2017_05_customer_id} | CREATE INDEX  ON public.payment_p2017_05 USING btree (customer_id
 {idx_fk_payment_p2017_06_customer_id,payment_p2017_06_customer_id_idx} | CREATE INDEX  ON public.payment_p2017_06 USING btree (customer_id
(6 rows)

ดัชนี Superset

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

หากคุณต้องการกำหนดดัชนีดังกล่าวโดยอัตโนมัติ คุณสามารถเริ่มต้นด้วย pg_index จากตาราง pg_catalog.

ดัชนีที่ไม่ได้ใช้

เมื่อแอปพลิเคชันที่ใช้ฐานข้อมูลมีวิวัฒนาการ การสืบค้นที่ใช้ก็เช่นกัน ดัชนีที่เพิ่มไว้ก่อนหน้านี้อาจไม่สามารถใช้ได้อีกต่อไปในแบบสอบถามใดๆ แต่ละครั้งที่มีการสแกนดัชนี ดัชนีจะถูกทำเครื่องหมายโดยตัวจัดการสถิติ และในมุมมองแค็ตตาล็อกระบบ pg_stat_user_indexes คุณจะเห็นคุณค่า idx_scanซึ่งเป็นตัวนับสะสม การติดตามค่านี้ในช่วงเวลาหนึ่ง (เช่นหนึ่งเดือน) จะให้แนวคิดที่ดีว่าดัชนีใดไม่ได้ใช้และอาจถูกทิ้ง

นี่คือแบบสอบถามเพื่อรับจำนวนการสแกนปัจจุบันของดัชนีทั้งหมดในสคีมา ‘public’:

SELECT relname, indexrelname, idx_scan
FROM   pg_catalog.pg_stat_user_indexes
WHERE  schemaname = 'public';
with output like this:
pagila=# SELECT relname, indexrelname, idx_scan
pagila-# FROM   pg_catalog.pg_stat_user_indexes
pagila-# WHERE  schemaname = 'public'
pagila-# LIMIT  10;
    relname    |    indexrelname    | idx_scan
---------------+--------------------+----------
 customer      | customer_pkey      |    32093
 actor         | actor_pkey         |     5462
 address       | address_pkey       |      660
 category      | category_pkey      |     1000
 city          | city_pkey          |      609
 country       | country_pkey       |      604
 film_actor    | film_actor_pkey    |        0
 film_category | film_category_pkey |        0
 film          | film_pkey          |    11043
 inventory     | inventory_pkey     |    16048
(10 rows)

สร้างดัชนีใหม่ด้วยการล็อกน้อยลง

ดัชนีมักจะต้องสร้างใหม่ เช่น เมื่อดัชนีบวม และการสร้างดัชนีใหม่อาจทำให้การสแกนเร็วขึ้น ดัชนีอาจเสียหายได้เช่นกัน การเปลี่ยนพารามิเตอร์ดัชนีอาจต้องสร้างใหม่

เปิดใช้งานการสร้างดัชนีแบบขนาน

ใน PostgreSQL 11 การสร้างดัชนี B-Tree จะทำพร้อมกัน เพื่อให้กระบวนการสร้างเร็วขึ้น สามารถใช้ผู้ปฏิบัติงานแบบขนานหลายคนได้ อย่างไรก็ตาม ตรวจสอบให้แน่ใจว่าได้ตั้งค่าตัวเลือกการกำหนดค่าเหล่านี้อย่างถูกต้อง:

SET max_parallel_workers = 32;
SET max_parallel_maintenance_workers = 16;

ค่าเริ่มต้นมีขนาดเล็กเกินไป ตามหลักการแล้ว ตัวเลขเหล่านี้ควรเพิ่มขึ้นพร้อมกับจำนวนแกนประมวลผล อ่านเพิ่มเติมใน เอกสาร.

การสร้างดัชนีพื้นหลัง

คุณสามารถสร้างดัชนีในพื้นหลังโดยใช้ตัวเลือก CONCURRENTLY คำสั่ง CREATE INDEX:

pagila=# CREATE INDEX CONCURRENTLY idx_address1 ON address(district);
CREATE INDEX

ขั้นตอนการสร้างดัชนีนี้แตกต่างจากขั้นตอนปกติตรงที่ไม่ต้องมีการล็อคตาราง ดังนั้นจึงไม่บล็อกการดำเนินการเขียน ในทางกลับกัน จะใช้เวลามากขึ้นและใช้ทรัพยากรมากขึ้น

Postgres ให้ความยืดหยุ่นอย่างมากสำหรับการสร้างดัชนีและวิธีแก้ไขกรณีพิเศษใดๆ รวมถึงวิธีจัดการฐานข้อมูลในกรณีที่แอปพลิเคชันของคุณเติบโตอย่างรวดเร็ว เราหวังว่าเคล็ดลับเหล่านี้จะช่วยให้คุณได้รับคำถามอย่างรวดเร็วและฐานข้อมูลของคุณพร้อมที่จะปรับขนาด

ที่มา: will.com