[PERFORM] Query not using Index
Hi all, Please provide some advise on the following query not using the index: pgsql version: 9.2.4 OS version: RedHat 6.5 Ram: 64 GB rows in testdb: 180 million shared_buffers: 16GB effective_cache_size: 32GB work_mem='32MB' I have executed the query below after I vaccum analyze the table. I have 2 questions: 1. Why does the optimizer chose not to use the index when it will run faster? 2. How do I ensure the optimizer will use the index without setting enable_seqscan='off' *Table structure.* testdb=# \d testtable Table "public.testtable" Column | Type | Modifiers ---+-+--- pk| text| not null additionaldetails | text| authtoken | text| not null customid | text| eventstatus | text| not null eventtype | text| not null module| text| not null nodeid| text| not null rowprotection | text| rowversion| integer | not null searchdetail1 | text| searchdetail2 | text| sequencenumber| bigint | not null service | text| not null timestamp | bigint | not null Indexes: "testtable_pkey" PRIMARY KEY, btree (pk) "testtable_nodeid_eleanor1_idx" btree (nodeid) WHERE nodeid = 'eleanor1'::text, tablespace "tablespace_index" "testtable_nodeid_eleanor2_idx" btree (nodeid) WHERE nodeid = 'eleanor2'::text, tablespace "tablespace_index" "testtable_nodeid_eleanor3_idx" btree (nodeid) WHERE nodeid = 'eleanor3'::text, tablespace "tablespace_index" *Explain Plan with enable_seqscan='on'* testdb=# explain analyze select max ( auditrecor0_.sequenceNumber ) AS col_0_0_ From testdb auditrecor0_ where auditrecor0_.nodeid = 'eleanor1'; QUERY PLAN - - Aggregate (cost=18291486.05..18291486.06 rows=1 width=8) (actual time=484907.446..484907.446 rows=1 loops=1) -> Seq Scan on testdb auditrecor0_ (cost=0.00..18147465.00 rows=57608421 width=8) (actual time=0.166..473959.12 6 rows=57801797 loops=1) Filter: (nodeid = 'eleanor1'::text) Rows Removed by Filter: 126233820 Total runtime: 484913.013 ms (5 rows) *Explain Plan with enable_seqscan='off'* testdb=# explain analyze select max ( auditrecor0_.sequenceNumber ) AS col_0_0_ From testdb auditrecor0_ where auditrecor0_.nodeid = 'eleanor3'; QUERY PLAN -- Aggregate (cost=19226040.50..19226040.51 rows=1 width=8) (actual time=388293.245..388293.245 rows=1 loops=1) -> Bitmap Heap Scan on testdb auditrecor0_ (cost=2291521.32..19046381.97 rows=71863412 width=8) (actual time=15626.372..375378.362 rows=71 412687 loops=1) Recheck Cond: (nodeid = 'eleanor3'::text) Rows Removed by Index Recheck: 900820 -> Bitmap Index Scan on testdb_nodeid_eleanor3_idx (cost=0.00..2273555.47 rows=71863412 width=0) (actual time=15503.465..15503.465 r ows=71412687 loops=1) Index Cond: (nodeid = 'eleanor3'::text) Total runtime: 388294.378 ms (7 rows) Thanks! -- Regards, Ang Wei Shan
Re: [PERFORM] Query not using Index
Wei Shan wrote: > Hi all, > > Please provide some advise on the following query not using the index: > I have 2 questions: > > 1. Why does the optimizer chose not to use the index when it will run faster? because of the estimated costs.: Seq Scan on testdb auditrecor0_ (cost=0.00..18147465.00 Bitmap Heap Scan on testdb auditrecor0_ (cost=2291521.32..19046381.97 The estimated costs for the index-scan are higher. > 2. How do I ensure the optimizer will use the index without setting > enable_seqscan='off' You have a dedicated tablespace for indexes, is this a SSD? You can try to reduce the random_page_cost, from default 4 to maybe 2.(depends on hardware) This would reduce the estimated costs for the Index-scan and prefer the index-scan. Regards, Andreas Kretschmer -- Andreas Kretschmer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
