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
