Skip to content

Instantly share code, notes, and snippets.

@Hirurg103
Last active June 11, 2016 09:24
Show Gist options
  • Save Hirurg103/686b2307ca8ab5c5cc022383122e468f to your computer and use it in GitHub Desktop.
Save Hirurg103/686b2307ca8ab5c5cc022383122e468f to your computer and use it in GitHub Desktop.
How to optimize this query?
CREATE TABLE clients (
first_name text,
last_name text
);
INSERT INTO clients (first_name, last_name)
(SELECT md5(random()::text) AS first_name, md5(random()::text) AS last_name
FROM (SELECT * FROM generate_series(1,1000000) AS id) AS x);
# trigram index
CREATE INDEX clients_search_idx
ON clients
USING gin (first_name gin_trgm_ops);
# btree index
CREATE INDEX btree_index_on_client_first_name
ON clients USING BTREE (first_name);
\timing
SELECT count(*) FROM clients WHERE first_name ILIKE '%abc%';
# Time: 42,086 ms
# but
SELECT count(*) FROM clients WHERE first_name ILIKE '%ab%';
# Time: 2661,724 ms
# ############################################################################
# 3 chars
EXPLAIN SELECT count(*) FROM clients WHERE first_name ILIKE '%abc%';
QUERY PLAN
---------------------------------------------------------------------------------------------
Aggregate (cost=12561.04..12561.05 rows=1 width=0)
-> Bitmap Heap Scan on clients (cost=122.28..12535.79 rows=10101 width=0)
Recheck Cond: (first_name ~~* '%abc%'::text)
-> Bitmap Index Scan on clients_search_idx (cost=0.00..119.76 rows=10101 width=0)
Index Cond: (first_name ~~* '%abc%'::text)
# 2 chars
EXPLAIN SELECT count(*) FROM clients WHERE first_name ILIKE '%ab%';
QUERY PLAN
----------------------------------------------------------------------
Aggregate (cost=25123.78..25123.79 rows=1 width=0)
-> Seq Scan on clients (cost=0.00..24846.00 rows=111111 width=0)
Filter: (first_name ~~* '%ab%'::text)
# Why doesn't it use btree index?
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment