ORDER BY text doesn't use my index?!

Question

currently this query doesn't use a index even after I created one:

EXPLAIN ANALYZE SELECT * FROM customer_list WHERE rating = 3 ORDER BY name ASC LIMIT 20 ;
                                                                             QUERY PLAN                                                                             
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=149.14..149.18 rows=15 width=1156) (actual time=11.307..11.311 rows=20 loops=1)
   ->  Sort  (cost=149.14..149.18 rows=15 width=1156) (actual time=11.305..11.306 rows=20 loops=1)
         Sort Key: c.name
         Sort Method: top-N heapsort  Memory: 28kB
         ->  Nested Loop  (cost=0.28..148.85 rows=15 width=1156) (actual time=0.022..6.403 rows=2977 loops=1)
               ->  Seq Scan on crm_customer c  (cost=0.00..96.24 rows=15 width=674) (actual time=0.009..1.184 rows=2977 loops=1)
                     Filter: (rating = 3)
                     Rows Removed by Filter: 2
               ->  Index Only Scan using crm_address_search_index on crm_address a  (cost=0.28..3.50 rows=1 width=498) (actual time=0.001..0.001 rows=1 loops=2977)
                     Index Cond: (id = c.address_id)
                     Heap Fetches: 0
 Planning time: 0.246 ms
 Execution time: 11.353 ms
(13 rows)

When turning seqscan off via set enable_seqscan = false; it will run correctly (ordered by name and is way faster):

EXPLAIN ANALYZE SELECT * FROM customer_list WHERE rating = 3 ORDER BY name ASC LIMIT 20 ;
                                                                         QUERY PLAN                                                                         
------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.56..413.02 rows=15 width=1156) (actual time=0.028..0.103 rows=20 loops=1)
   ->  Nested Loop  (cost=0.56..413.02 rows=15 width=1156) (actual time=0.027..0.101 rows=20 loops=1)
         ->  Index Scan using crm_customer_name on crm_customer c  (cost=0.28..360.41 rows=15 width=674) (actual time=0.018..0.040 rows=20 loops=1)
               Filter: (rating = 3)
               Rows Removed by Filter: 2
         ->  Index Only Scan using crm_address_search_index on crm_address a  (cost=0.28..3.50 rows=1 width=498) (actual time=0.002..0.002 rows=1 loops=20)
               Index Cond: (id = c.address_id)
               Heap Fetches: 0
 Planning time: 0.190 ms
 Execution time: 0.133 ms
(10 rows)

My actual index is created with: CREATE INDEX crm_customer_name_search_index ON crm_customer (name text_pattern_ops ASC NULLS FIRST );

currently I had a bigger index and thought that this way the problem, but however it's not. (indexing rating, too doesn't help)

András Váczi · Accepted Answer · 2015-09-30T15:49:56.707

You can find the answer in the EXPLAIN ANALYZE outputs you've posted.

In the first one, the estimated cost of the sequential scan is 96.24, while the index scan has a cost of 360.41 in the second one. This is why the planner decided not to use the index.

However, we see that the execution time is much better in the second case. The main reason might be that the row estimate of the sequential scan is quite bad - 15 rows are expected instead of ~3k. Do an ANALYZE crm_customer; and see if it changes - it is already possible that the index will be chosen.

ORDER BY text doesn't use my index?!

1 Answers1