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