From: Jim Finnerty <jfinnert@amazon.com>
To: pgsql-general@postgresql.org
Subject: Re: Why the planner does not use index for a large amount of data?
Date: Mon, 11 Dec 2017 20:56:04 -0700 (MST)
Message-ID: <1513050964482-0.post@n3.nabble.com> (raw)
In-Reply-To: <CAMsqVxtGL4cO7iWTcfypxpeEAKQ3gzvqutCstqhJZJts4pKUmA@mail.gmail.com>
References: <CAMsqVxtGL4cO7iWTcfypxpeEAKQ3gzvqutCstqhJZJts4pKUmA@mail.gmail.com>
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
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-docs@postgresql.org
Cc: jfinnert@amazon.com, pgsql-general@postgresql.org
Subject: Re: Why the planner does not use index for a large amount of data?
In-Reply-To: <1513050964482-0.post@n3.nabble.com>
* 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