การใช้ฟังก์ชันของ postgres ใน where clause อาจทำให้ query กลายเป็น non-sargable

โครงสร้างฐานข้อมูล

tracks has_many artists

Query แบบ Non-Sargable

การใช้ฟังก์ชัน LOWER ทำให้ DBMS engine ใช้ index ไม่ได้

Track.joins(:artists).where('LOWER(tracks.display_name) LIKE ?', "eric clapton%").explain

Gather  (cost=1000.85..56714.32 width=4061)
   Workers Planned: 2
   ->  Nested Loop  (cost=0.85..55713.62 rows=3 width=4061)
         ->  Nested Loop  (cost=0.42..55708.37 rows=3 width=4069)
               ->  Parallel Seq Scan on tracks  (cost=0.00..55365.17 rows=41 width=4061)
                     Filter: (lower((display_name)::text) ~~* 'eric clapton%'::text)
               ->  Index Scan using index_artist_relations_on_artist_item_type_and_artist_item_id on artist_relations  (cost=0.42..8.36 rows=1 width=16)
                     Index Cond: (((artist_item_type)::text = 'Track'::text) AND (artist_item_id = tracks.id))
         ->  Index Only Scan using idx_35952_primary on artists  (cost=0.43..1.75 rows=1 width=8)
               Index Cond: (id = artist_relations.artist_id)

Query แบบ Sargable

เมื่อเอาฟังก์ชัน LOWER ออก DBMS engine ก็ใช้ index ได้ ทำให้รันได้เร็วขึ้น

Track.joins(:artists).where('tracks.display_name ILIKE ?', "eric clapton%").explain

Nested Loop  (cost=1497.60..2695.43 width=4061)
   ->  Nested Loop  (cost=1497.17..2684.94 rows=6 width=4069)
         ->  Bitmap Heap Scan on tracks  (cost=1496.75..1873.05 rows=97 width=4061)
               Recheck Cond: ((display_name)::text ~~* 'eric clapton%'::text)
               ->  Bitmap Index Scan on index_tracks_on_display_name  (cost=0.00..1496.73 rows=97 width=0)
                     Index Cond: ((display_name)::text ~~* 'eric clapton%'::text)
         ->  Index Scan using index_artist_relations_on_artist_item_type_and_artist_item_id on artist_relations  (cost=0.42..8.36 rows=1 width=16)
               Index Cond: (((artist_item_type)::text = 'Track'::text) AND (artist_item_id = tracks.id))
   ->  Index Only Scan using idx_35952_primary on artists  (cost=0.43..1.75 rows=1 width=8)
         Index Cond: (id = artist_relations.artist_id)

Sargable กับ Non-Sargable

query แบบ sargable คือ query ที่ใช้ index ช่วยให้ทำงานเร็วขึ้นได้ โดยเปิดทางให้ DBMS engine ทำ index seek ซึ่งเร็วกว่าการ scan ทั้งตารางมาก ส่วน query แบบ non-sargable ใช้ index ไม่ได้ จึงช้ากว่า คำว่า “sargable” มาจาก “Search ARGument ABLE” หมายความว่า DBMS engine สามารถใช้ index มา optimize query ได้ การเข้าใจความแตกต่างระหว่าง sargable กับ non-sargable คือกุญแจสำคัญในการ optimize ฐานข้อมูลของคุณ

Query แบบ Non-Sargable

query แบบ non-sargable ส่งผลกระทบต่อ performance ของ query ได้มาก โดยเฉพาะกับชุดข้อมูลขนาดใหญ่ เมื่อ query เป็น non-sargable ตัว DBMS engine ต้องทำ full table scan หรือ index scan ซึ่งทั้งสองอย่างเป็นงานหนัก ผลคือรันช้าลง ใช้ CPU สูงขึ้น และ performance โดยรวมของระบบแย่ลง การหาและ optimize query แบบ non-sargable จึงเป็นหัวใจของการทำให้ฐานข้อมูลมีประสิทธิภาพและตอบสนองได้ดีขึ้น

ฟังก์ชันใน WHERE Clause

การใช้ฟังก์ชันใน WHERE clause อาจทำให้ query กลายเป็น non-sargable ที่เป็นแบบนี้เพราะ DBMS engine ไม่สามารถใช้ index มา optimize query ได้เมื่อมีฟังก์ชันถูกใช้กับคอลัมน์ใน WHERE clause ตัวอย่างเช่น query SELECT FROM table WHERE UPPER(column) = 'VALUE' เป็น non-sargable เพราะฟังก์ชัน UPPER ถูกใช้กับคอลัมน์ index จึงถูกใช้ไม่ได้ ถ้าอยากให้ query นี้เป็น sargable ก็เอาฟังก์ชันออกแล้วเขียนใหม่เป็น SELECT FROM table WHERE column = 'VALUE' จากนั้น DBMS engine ก็จะใช้ index ได้และ query จะเร็วขึ้น

Index กับการ Optimize Query

index คือหัวใจของการ optimize query เพราะช่วยให้ DBMS engine หาข้อมูลได้อย่างรวดเร็ว query จึงเร็วขึ้นตาม แต่ index จะถูกใช้ได้ก็ต่อเมื่อ query เป็น sargable ถ้า query เป็น non-sargable ตัว index จะถูกใช้ไม่ได้และ query จะช้าลง ดังนั้นให้สร้าง index บนคอลัมน์ที่ใช้ใน WHERE clause และตรวจให้แน่ใจว่า query เป็น sargable แล้ว performance ของฐานข้อมูลคุณจะดีขึ้นแบบเห็นได้ชัด

การแปลง Predicate แบบ Non-Sargable

การแปลง predicate แบบ non-sargable ให้เป็น sargable คือหนึ่งในขั้นตอนของการ optimize query โดย predicate แบบ non-sargable แปลงเป็น sargable ได้ด้วยการเขียน query ใหม่เพื่อเอาฟังก์ชันหรือการดำเนินการที่ขวางการใช้ index ออก ตัวอย่างเช่น query SELECT FROM table WHERE YEAR(date) = 2022 เป็น non-sargable เพราะฟังก์ชัน YEAR ถูกใช้กับคอลัมน์ date ถ้าอยากให้ query นี้เป็น sargable ก็เขียนใหม่เป็น SELECT FROM table WHERE date >= '2022-01-01' AND date < '2023-01-01' ซึ่ง query ที่เขียนใหม่นี้เป็น sargable เพราะ DBMS engine ใช้ index บนคอลัมน์ date มา optimize query ได้และจะเร็วขึ้น