Skip to content

Add partial index on identities table to speed up LDAP lookups

Stan Hu requested to merge sh-add-index-extern-uid into master

For customers with thousands of entries in the table, lookups for an LDAP identity dominated the PostgreSQL statistics because of sequential scans. We can speed this up by adding a partial index with the extern_uid and provider.

Before:

gitlabhq_test=# explain analyze select identities from identities where lower(extern_uid) = lower('test') and provider = 'ldapmain' limit 1;
                                                        QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..6.78 rows=1 width=122) (actual time=1085.405..1085.405 rows=0 loops=1)
   ->  Seq Scan on identities  (cost=0.00..101681.00 rows=15000 width=122) (actual time=1085.405..1085.405 rows=0 loops=1)
         Filter: (((provider)::text = 'ldapmain'::text) AND (lower((extern_uid)::text) = 'test'::text))
         Rows Removed by Filter: 3000000
 Planning time: 1.473 ms
 Execution time: 1085.429 ms
(6 rows)

After:

                                                                               QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.56..4.70 rows=1 width=148) (actual time=0.093..0.093 rows=0 loops=1)
   ->  Index Scan using index_on_identities_lower_extern_uid_and_provider on identities  (cost=0.56..269.85 rows=65 width=148) (actual time=0.087..0.087 rows=0 loops=1)
         Index Cond: ((lower((extern_uid)::text) = 'test'::text) AND ((provider)::text = 'ldapmain'::text))
 Planning time: 10.715 ms
 Execution time: 0.875 ms
(5 rows)

EE port: https://gitlab.com/gitlab-org/gitlab-ee/merge_requests/14807

Closes https://gitlab.com/gitlab-org/gitlab-ce/issues/59630

Edited by Stan Hu

Merge request reports