From: Andreas Kretschmer <akretschmer@spamfence.net>
To: pgsql-performance@postgresql.org
Subject: Re: Query not using Index
Date: Sat, 26 Mar 2016 15:13:46 +0100
Message-ID: <20160326141346.GA32059@tux> (raw)
In-Reply-To: <CAFe9ZToU+EUF3mBqFRstV62T4giivPXKTCZp5dgOj2R1gObJjQ@mail.gmail.com>
References: <CAFe9ZToU+EUF3mBqFRstV62T4giivPXKTCZp5dgOj2R1gObJjQ@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: akretschmer@spamfence.net
Subject: Re: Query not using Index
In-Reply-To: <20160326141346.GA32059@tux>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox