From: | Andreas Kretschmer <akretschmer(at)spamfence(dot)net> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Query not using Index |
Date: | 2016-03-26 14:13:46 |
Message-ID: | 20160326141346.GA32059@tux |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Wei Shan <weishan(dot)ang(at)gmail(dot)com> 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
From | Date | Subject | |
---|---|---|---|
Next Message | Wei Shan | 2016-03-27 16:12:43 | Re: Query not using Index |
Previous Message | Wei Shan | 2016-03-26 13:14:15 | Query not using Index |