Performance Insigths -Query Review- :: Week 35 // Query 1 - create partial index
SELECT namespaces.*
FROM namespaces
WHERE namespaces.type IN ( 'Group' )
AND namespaces.visibility_level = 20
ORDER BY namespaces.name ASC,
namespaces.id ASC
LIMIT 101 offset 143800;
Current plan:
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=173041.31..173041.57 rows=101 width=314) (actual time=1984.124..1984.170 rows=101 loops=1)
-> Sort (cost=172681.81..174798.77 rows=846784 width=314) (actual time=1926.213..1978.310 rows=143901 loops=1)
Sort Key: name, id
Sort Method: quicksort Memory: 48242kB
-> Index Scan using index_namespaces_on_type on namespaces (cost=0.43..95900.87 rows=846784 width=314) (actual time=0.107..1387.788 rows=157932 loops=1)
Index Cond: ((type)::text = 'Group'::text)
Filter: (visibility_level = 20)
Rows Removed by Filter: 854911
Planning time: 0.233 ms
Execution time: 1988.589 ms
(10 rows)
This query can be improved by creating a new partial index:
- The
namespaces.type
column have ~81% of NULL values (look at null_frac column):
gitlabhq_production=# select * from pg_stats where tablename = 'namespaces' and attname='type';
-[ RECORD 1 ]----------+-----------
schemaname | public
tablename | namespaces
attname | type
inherited | f
null_frac | 0.81121
avg_width | 6
n_distinct | 1
most_common_vals | {Group}
most_common_freqs | {0.18879}
histogram_bounds |
correlation | 1
most_common_elems |
most_common_elem_freqs |
elem_count_histogram |
Here, a partial index over the type
column without NULL values should be preferred. Something like
CREATE INDEX CONCURRENTLY index_namespaces_partial_on_type ON namespaces(type) WHERE type is not NULL
Edited by Craig Gomes