การใช้ฟังก์ชันของ 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 เพราะเมื่อมีฟังก์ชันครอบคอลัมน์ใน WHERE clause อยู่ DBMS engine จะใช้ index มา optimize query ไม่ได้ ตัวอย่างเช่น 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 ถ้าเป็น 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 ได้และจะเร็วขึ้น