Re: Query not using Index

From: Andreas Kretschmer
Subject: Re: Query not using Index
Date: ,
Msg-id: 20160326141346.GA32059@tux
(view: Whole thread, Raw)
In response to: Query not using Index  (Wei Shan)
Responses: Re: Query not using Index  (Wei Shan)
List: pgsql-performance

Tree view

Query not using Index  (Wei Shan, )
 Re: Query not using Index  (Andreas Kretschmer, )
  Re: Query not using Index  (Wei Shan, )
   Re: Query not using Index  (Jeff Janes, )

Wei Shan <> 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



pgsql-performance by date:

From: Andreas Kretschmer
Date:
Subject: Re: Query not using Index
From: Wei Shan
Date:
Subject: Re: Query not using Index