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 <akretschmer@xxxxxxxxxxxxx> wrote:
Wei Shan <weishan.ang@xxxxxxxxx> 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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Regards,
Ang Wei Shan
Ang Wei Shan
