Date: Sat, 26 Mar 2016 15:13:46 +0100
From: Andreas Kretschmer <akretschmer@spamfence.net>
To: pgsql-performance@postgresql.org
Subject: Re: Query not using Index
Message-ID: <20160326141346.GA32059@tux>
References: 
 <CAFe9ZToU+EUF3mBqFRstV62T4giivPXKTCZp5dgOj2R1gObJjQ@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: 
 <CAFe9ZToU+EUF3mBqFRstV62T4giivPXKTCZp5dgOj2R1gObJjQ@mail.gmail.com>
User-Agent: Mutt/1.5.18 (2008-05-17)
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

Wei Shan <weishan.ang@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