Date: Mon, 11 Dec 2017 20:56:04 -0700 (MST)
From: Jim Finnerty <jfinnert@amazon.com>
To: pgsql-general@postgresql.org
Message-ID: <1513050964482-0.post@n3.nabble.com>
In-Reply-To: 
 <CAMsqVxtGL4cO7iWTcfypxpeEAKQ3gzvqutCstqhJZJts4pKUmA@mail.gmail.com>
References: 
 <CAMsqVxtGL4cO7iWTcfypxpeEAKQ3gzvqutCstqhJZJts4pKUmA@mail.gmail.com>
Subject: Re: Why the planner does not use index for a large amount of data?
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Precedence: bulk

Hi Hmidi,

    Your description omits some important information, such as whether you
defined your index to include all columns needed by the request, but the
short answer is 'yes', if you retrieve enough rows, then it will be faster
to read the entire heap instead of reading the necessary parts of the index
and all the referenced pages of the heap, in index order.  The decision is
more complex than just this in PostgreSQL due to MVCC, the visibility bits
on each page, and the alternative of doing a bitmap heap scan when the
selectivity is moderate.  The planner is designed to consider all these
factors, cost them, and choose a minimum cost plan.

    If you're convinced that the planner has not picked the fastest plan,
you can either try to force the plan by setting the enable_* guc variables,
or you can use the pg_hint_plan extension (which sets the enable_* variables
on your behalf), to get the plan you want, and then measure the performance
of the query with each alternative.

    /Jim F


--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html