Menggunakan fungsi postgres di dalam klausa where dapat membuat query menjadi non-sargable.

Struktur Database

tracks has_many artists

Query Non-Sargable

Penggunaan fungsi LOWER mencegah engine DBMS menggunakan 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

Menghapus fungsi LOWER memungkinkan engine DBMS menggunakan index, sehingga eksekusinya lebih cepat.

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 dan Non-Sargable

Query sargable adalah query yang dapat menggunakan index untuk mempercepat eksekusinya. Query sargable memungkinkan engine DBMS melakukan index seek, yang jauh lebih cepat daripada memindai seluruh tabel. Query non-sargable tidak dapat menggunakan index, sehingga lebih lambat. Istilah "sargable" berasal dari "Search ARGument ABLE", yang berarti engine DBMS dapat menggunakan index untuk mengoptimalkan query. Memahami perbedaan antara sargable dan non-sargable adalah kunci untuk mengoptimalkan database Anda.

Query Non-Sargable

Query non-sargable dapat berdampak besar pada performa query, terutama pada dataset yang besar. Ketika sebuah query bersifat non-sargable, engine DBMS harus melakukan full table scan atau index scan, yang keduanya merupakan operasi berat. Akibatnya, waktu eksekusi bisa lebih lambat, penggunaan CPU lebih tinggi, dan performa sistem secara keseluruhan menurun. Menemukan dan mengoptimalkan query non-sargable adalah kunci untuk meningkatkan efisiensi dan responsivitas database Anda.

Fungsi di Klausa WHERE

Menggunakan fungsi di klausa WHERE dapat membuat query menjadi non-sargable. Hal ini terjadi karena engine DBMS tidak dapat menggunakan index untuk mengoptimalkan query ketika sebuah fungsi diterapkan pada kolom di klausa WHERE. Sebagai contoh, perhatikan query SELECT FROM table WHERE UPPER(column) = 'VALUE'. Query ini non-sargable karena fungsi UPPER diterapkan pada kolom, sehingga index tidak dapat digunakan. Agar query ini menjadi sargable, Anda dapat menghapus fungsi tersebut dan menulis ulang query sebagai SELECT FROM table WHERE column = 'VALUE'. Dengan begitu, engine DBMS dapat menggunakan index dan query akan berjalan lebih cepat.

Index dan Optimasi Query

Index adalah kunci optimasi query karena memungkinkan engine DBMS menemukan data dengan cepat, sehingga mempercepat query. Namun, index hanya dapat digunakan jika query bersifat sargable. Jika query non-sargable, index tidak dapat digunakan dan query akan lebih lambat. Jadi, buatlah index pada kolom yang digunakan di klausa WHERE dan pastikan query Anda sargable. Langkah ini akan meningkatkan performa database Anda secara signifikan.

Mengubah Predikat Non-Sargable

Mengubah predikat non-sargable menjadi sargable adalah salah satu langkah optimasi query. Predikat non-sargable dapat diubah menjadi sargable dengan menulis ulang query untuk menghapus fungsi atau operasi apa pun yang mencegah penggunaan index. Sebagai contoh, query SELECT FROM table WHERE YEAR(date) = 2022 bersifat non-sargable karena fungsi YEAR diterapkan pada kolom date. Agar query ini menjadi sargable, Anda dapat menulis ulang query tersebut sebagai SELECT FROM table WHERE date >= '2022-01-01' AND date < '2023-01-01'. Query hasil penulisan ulang ini bersifat sargable karena engine DBMS dapat menggunakan index pada kolom date untuk mengoptimalkan query, sehingga eksekusinya lebih cepat.