Optimize autocomplete on edit committee page

[anandology]

The autocomplete could be very slow when there are many rows in the member table. We need to add the required indexes on that table.

[anandology]

Look at the query used, use explain to see if any index is used and create an index if required. Please report the query plan before and after the index here for later reference.

[devilankur18]

@anandology I have made a small change to the query, which seems to have helped. Do we have some bigger data to test this ?

  // Old
  SELECT member.* FROM member
        JOIN place_parents ON place_parents.child_id = member.place_id
        JOIN place ON place.id = place_parents.parent_id
        WHERE member.place_id = place.id
            AND
                (lower(member.name) LIKE 'ank' || '%'
                OR lower(member.email) LIKE 'ank' || '%'
                OR lower(member.phone) LIKE 'ank' || '%')
        LIMIT 10

// New
SELECT member.* FROM member
  JOIN place_parents ON place_parents.child_id = member.place_id
  JOIN place ON place.id = place_parents.parent_id
  WHERE member.place_id = place.id
      AND
          (lower(member.name) LIKE '%ank%'
          OR lower(member.email) LIKE '%ank%'
          OR lower(member.phone) LIKE '%ank%')
  LIMIT 10 

[devilankur18]

@anandology For indexing, we can take advantage of gin index in postgresql for which we need to enable extension.

References

• http://stackoverflow.com/questions/1566717/postgresql-like-query-performance-variations
• http://hashrocket.com/blog/posts/exploring-postgres-gin-index

Any suggestions / roadblocks ?

[devilankur18]

@anandology please review and merge.

For deployment, all need to done is run the migration.

[devilankur18]

Query Plan:

#Old

Limit  (cost=0.28..60.47 rows=1 width=176)
  ->  Nested Loop  (cost=0.28..60.47 rows=1 width=176)
        ->  Nested Loop  (cost=0.00..56.91 rows=1 width=184)
              Join Filter: (member.place_id = place_parents.child_id)
              ->  Seq Scan on member  (cost=0.00..1.02 rows=1 width=176)
                    Filter: ((lower(name) ~~ '%ank%'::text) OR (lower(email) ~~ '%ank%'::text) OR (lower(phone) ~~ '%ank%'::text))
              ->  Seq Scan on place_parents  (cost=0.00..55.69 rows=16 width=8)
                    Filter: (child_id = parent_id)
        ->  Index Only Scan using place_pkey on place  (cost=0.28..3.54 rows=1 width=4)
              Index Cond: (id = place_parents.child_id)

# New
Limit  (cost=0.28..60.47 rows=1 width=176)
  ->  Nested Loop  (cost=0.28..60.47 rows=1 width=176)
        ->  Nested Loop  (cost=0.00..56.91 rows=1 width=184)
              Join Filter: (member.place_id = place_parents.child_id)
              ->  Seq Scan on member  (cost=0.00..1.02 rows=1 width=176)
                    Filter: ((lower(name) ~~ '%ank%'::text) OR (lower(email) ~~ '%ank%'::text) OR (lower(phone) ~~ '%ank%'::text))
              ->  Seq Scan on place_parents  (cost=0.00..55.69 rows=16 width=8)
                    Filter: (child_id = parent_id)
        ->  Index Only Scan using place_pkey on place  (cost=0.28..3.54 rows=1 width=4)
              Index Cond: (id = place_parents.child_id) 

[anandology]

Is this after creating the index or before creating the index?

The query plan doesn't seem to be using the index. It is still doing seq scan.

[anandology]

Try with the following simple query:

cleansweep=# explain select * from member where name like 'anan%';
                         QUERY PLAN
------------------------------------------------------------
 Seq Scan on member  (cost=0.00..6663.07 rows=36 width=118)
   Filter: (name ~~ 'anan%'::text)
(2 rows)

With GIN index, it seems to be using the index.

cleansweep=# explain select * from member where name like 'anan%';
                                  QUERY PLAN
-------------------------------------------------------------------------------
 Bitmap Heap Scan on member  (cost=68.28..198.49 rows=36 width=118)
   Recheck Cond: (name ~~ 'anan%'::text)
   ->  Bitmap Index Scan on ix_member_name  (cost=0.00..68.27 rows=36 width=0)
         Index Cond: (name ~~ 'anan%'::text)
(4 rows)

However, if we use lower(name) then the index is not used.

cleansweep=# explain select * from member where lower(name) like 'anan%';
                          QUERY PLAN
--------------------------------------------------------------
 Seq Scan on member  (cost=0.00..7581.69 rows=1837 width=118)
   Filter: (lower(name) ~~ 'anan%'::text)
(2 rows)

The number of rows also matters in the query plan. Unless you have good enough rows, it thinks that seq scan is not that expensive. I've added lot of rows using the following:

cleansweep=# copy member (name) from '/usr/share/dict/words';
COPY 235886

That added a quarter million rows to the member table.

[devilankur18]

@anandology I tried all the suggestion, and tried to add the lower function in the index, but the index created with it is never been hit upon search. Seems some thing is not right, might be related to the text / string difference.

I do have updated the code, to easy to switch to lower function by just uncommenting. I am using this codebase as reference

https://github.com/indico/indico/blob/master/migrations/versions/201506081244_a2dfbb4b85c_add_extra_indexes_for_user_search.py

Let me know if you have any suggestions.