1

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)

3

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.

|improve this answer|||||
  • I maybe might need to say that customer_list is a VIEW which is a inner join of customer and address, does this change something? – Christian Schmitt Sep 30 '15 at 15:49
  • @ChristianSchmitt Sure, you cannot analyze a simple view :) I picked now the real tablename, I hope. – dezso Sep 30 '15 at 15:50
  • I knew that I need to make it on the table ;) Still I'm not sure why I needed to ANALYZE doesn't it run automatically if I have AUTOVACUUM? or after a VACUUM? I mean mostly I run VACUUM after I created an Index.. – Christian Schmitt Sep 30 '15 at 15:51
  • @ChristianSchmitt VACUUM doesn't involve an ANALYZE, too. After adding an index, you need the latter. And, depending on your autovacuum settings, it may take a while until the table gets autoanalyzed. – dezso Sep 30 '15 at 15:54
  • thanks :) I've just read postgresql.org/docs/9.1/static/sql-vacuum.html it seems that I need to VACUUM ANALYZE; or ANALYZE; after index creation, thank you. :) – Christian Schmitt Sep 30 '15 at 15:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.