public inbox for [email protected]
help / color / mirror / Atom feedQuery not using Index
4+ messages / 3 participants
[nested] [flat]
* Query not using Index
@ 2016-03-26 13:14 Wei Shan <[email protected]>
2016-03-26 14:13 ` Re: Query not using Index Andreas Kretschmer <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: Wei Shan @ 2016-03-26 13:14 UTC (permalink / raw)
To: 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:
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Query not using Index
2016-03-26 13:14 Query not using Index Wei Shan <[email protected]>
@ 2016-03-26 14:13 ` Andreas Kretschmer <[email protected]>
2016-03-27 16:12 ` Re: Query not using Index Wei Shan <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: Andreas Kretschmer @ 2016-03-26 14:13 UTC (permalink / raw)
To: pgsql-performance
Wei Shan <[email protected]> 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 ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Query not using Index
2016-03-26 13:14 Query not using Index Wei Shan <[email protected]>
2016-03-26 14:13 ` Re: Query not using Index Andreas Kretschmer <[email protected]>
@ 2016-03-27 16:12 ` Wei Shan <[email protected]>
2016-03-27 19:20 ` Re: Query not using Index Jeff Janes <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: Wei Shan @ 2016-03-27 16:12 UTC (permalink / raw)
To: Andreas Kretschmer <[email protected]>; +Cc: pgsql-performance
Hi Andreas,
The tablespace is not on SSD although I intend to do it within the next
week. I actually tried reducing the random_page_cost to 0.2 but it doesn't
help.
On 26 March 2016 at 22:13, Andreas Kretschmer <[email protected]>
wrote:
> Wei Shan <[email protected]> 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 ([email protected])
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
--
Regards,
Ang Wei Shan
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Query not using Index
2016-03-26 13:14 Query not using Index Wei Shan <[email protected]>
2016-03-26 14:13 ` Re: Query not using Index Andreas Kretschmer <[email protected]>
2016-03-27 16:12 ` Re: Query not using Index Wei Shan <[email protected]>
@ 2016-03-27 19:20 ` Jeff Janes <[email protected]>
0 siblings, 0 replies; 4+ messages in thread
From: Jeff Janes @ 2016-03-27 19:20 UTC (permalink / raw)
To: Wei Shan <[email protected]>; +Cc: Andreas Kretschmer <[email protected]>; pgsql-performance
On Sun, Mar 27, 2016 at 9:12 AM, Wei Shan <[email protected]> wrote:
> Hi Andreas,
>
> The tablespace is not on SSD although I intend to do it within the next
> week. I actually tried reducing the random_page_cost to 0.2 but it doesn't
> help.
Setting random_page_cost to less than seq_page_cost is nonsensical.
You could try to increase cpu_tuple_cost to 0.015 or 0.02
Cheers,
Jeff
--
Sent via pgsql-performance mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2016-03-27 19:20 UTC | newest]
Thread overview: 4+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2016-03-26 13:14 Query not using Index Wei Shan <[email protected]>
2016-03-26 14:13 ` Andreas Kretschmer <[email protected]>
2016-03-27 16:12 ` Wei Shan <[email protected]>
2016-03-27 19:20 ` Jeff Janes <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox