Query not using Index - Mailing list pgsql-performance
| From | Wei Shan |
|---|---|
| Subject | Query not using Index |
| Date | |
| Msg-id | CAFe9ZToU+EUF3mBqFRstV62T4giivPXKTCZp5dgOj2R1gObJjQ@mail.gmail.com Whole thread Raw |
| Responses |
Re: Query not using Index
|
| List | pgsql-performance |
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:
- Why does the optimizer chose not to use the index when it will run faster?
- 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
Ang Wei Shan
pgsql-performance by date: