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 <akretsch...@spamfence.net> wrote: > Wei Shan <weishan....@gmail.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 > > > -- > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance > -- Regards, Ang Wei Shan
