The remaining characters are just filter predicates that do not narrow the scanned index range. LIKE filters can only use the characters before the first wild card during tree traversal. When I initially built the query I used concat_ws (concat with separator) string function to join first_name and last_name.2 | FETCH EMPLOYEES | 1 of 1 (100.00%) | 13ģ | IXSCAN EMP_NAME | 1 of 10000 (. 018 rows = 0 loops = 1 ) Recheck Cond : (((( first_name ):: text || ' ' :: text ) || ( last_name ):: text ) ~~* '%foo%' :: text ) -> Bitmap Index Scan on index_users_full_name ( cost = 0. 020 rows = 1 loops = 1 ) -> Bitmap Heap Scan on users ( cost = 378. 01 rows = 1 width = 0 ) ( actual time = 0. To do this we need to use both first_name and last_name columns and concatenate them in the query:ĮXPLAIN ANALYSE SELECT COUNT ( * ) FROM users WHERE first_name || ' ' || last_name ILIKE '%foo%' QUERY PLAN - Aggregate ( cost = 10605. Let’s say we can search in our app for users by name - If I enter in ‘John Doe’ into my search field I would expect it to return a user named ‘John Doe’ (assuming it exists). In the table above we have users with a first_name and last_name. 024 rows = 0 loops = 1 ) Index Cond : (( username ):: text ~~* '%foo%' :: text ) Planning time : 0. 72 rows = 96 width = 0 ) ( actual time = 0. 026 rows = 0 loops = 1 ) Recheck Cond : (( username ):: text ~~* '%foo%' :: text ) -> Bitmap Index Scan on trgm_idx_users_username ( cost = 0. 88 rows = 96 width = 0 ) ( actual time = 0. 030 rows = 1 loops = 1 ) -> Bitmap Heap Scan on users ( cost = 12. 13 rows = 1 width = 0 ) ( actual time = 0. $ > EXPLAIN ANALYSE SELECT COUNT ( * ) FROM users WHERE username ILIKE '%foo%' QUERY PLAN - Aggregate ( cost = 369. To use the pg_trm module, you need to enable the extension and create the index passing in the default gin_trgm_ops: The pg_trgm module supports GIST or GIN indexes and as of Postgres version 9.1 these indexes support LIKE/ ILIKE queries. Postgres uses trigrams to break down strings into smaller chunks and index them efficiently. The pg_trgm module provides functions and operators for determining the similarity of ASCII alphanumeric text based on trigram matching, as well as index operator classes that support fast searching for similar strings. 268 rows = 0 loops = 1 ) Filter : (( username ):: text ~~* '%foo%' :: text ) Rows Removed by Filter : 1000000 Planning time : 0. 00 rows = 96 width = 0 ) ( actual time = 752. 271 rows = 1 loops = 1 ) -> Seq Scan on users ( cost = 0. 25 rows = 1 width = 0 ) ( actual time = 752. 751 ms $ > EXPLAIN ANALYSE SELECT COUNT ( * ) FROM users WHERE username ILIKE '%foo%' QUERY PLAN - Aggregate ( cost = 21927. $ > CREATE INDEX idx_users_username ON users ( username ) Time : 15987. The new index would be used if we’re doing a comparison search such as WHERE username = 'foo', but not if we’re doing a partial match using LIKE or ILIKE: It’s doing a full table scan and ignoring the index completely. As you can see below, the query is just as slow. You might think that indexing this column with a standard btree index would help this search - it doesn’t. 520 rows = 0 loops = 1 ) Filter : (( username ):: text ~~* '%foo%' :: text ) Rows Removed by Filter : 1000000 Planning time : 0. 00 rows = 96 width = 0 ) ( actual time = 737. 523 rows = 1 loops = 1 ) -> Seq Scan on users ( cost = 0. 25 rows = 1 width = 0 ) ( actual time = 737. $ > EXPLAIN ANALYSE SELECT COUNT ( * ) FROM users WHERE username ILIKE '%foo%' QUERY PLAN - Aggregate ( cost = 21927. I’ve got a very simple users database table populated with 1 million rows. I'm pretty sure Postgres has already solved most of my problems, I just haven't made it to that part of the documentation yet. Indexing columns for LIKE queries was perfect example of this. Whenever I need something from it, it’s usually already there, I just cant always find how to do it. The more I work with PostgreSQL the more it impresses me. Quite often, structuring and indexing the database correctly can keep your queries nice and fast. However, we don’t always need such substantial dependencies in an app. Tools like Elastic Search or Solr are great at providing quick intelligent searches on large datasets. Users expect search to fast and to be accurate. Search is often an integral part of any web app, but it’s also one of the parts that can cause performance problems. It took me a while to figure out the right way to index columns for LIKE lookups, especially for indexing compound columns. Recently I wanted to add basic text search to an application I as working on.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |