From: Tom Lane <tgl@sss.pgh.pa.us>
To: Alistair Bayley <alistair@abayley.org>
Cc: Jeff Janes <jeff.janes@gmail.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Why is the optimiser choosing the slower query, or, understanding explain analyze output
Date: Mon, 17 Feb 2014 21:30:14 -0500
Message-ID: <12233.1392690614@sss.pgh.pa.us> (raw)
In-Reply-To: <CAKYODbj0Yq5kWxnSbBWkDiH4w2vmUL-10qmV4-O3w0PPrgMxmw@mail.gmail.com>
References: <CAKYODbj6xQDShQd2g3+9oue0+cz3NXjrnRvmbrfRDRoReWxFKQ@mail.gmail.com>
<CAMkU=1x16-S5V+BKD0Pr2K3NyCQvv7hsCP1Fk4UcwHecssV_KQ@mail.gmail.com>
<11385.1392687610@sss.pgh.pa.us>
<CAKYODbj0Yq5kWxnSbBWkDiH4w2vmUL-10qmV4-O3w0PPrgMxmw@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
Alistair Bayley <alistair@abayley.org> writes:
> On 18 February 2014 14:40, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> I notice though that the cost estimate for the seqscan plan isn't all that
>> much lower than that for the indexscan plan. Probably lowering
>> random_page_cost a bit would change the planner's mind. We have no
>> information about total size of database vs available RAM, but if it's
>> a mostly memory-resident database then such a change would be a good idea.
> [ database size is 3GB, RAM 2GB ]
The usual advice for database-in-RAM scenarios is to set random_page_cost
= 1, or even to lower both random_page_cost and seq_page_cost below 1.
In this case, since it's not going to be entirely RAM-resident, a
compromise setting around 2 might be a good idea.
> I'm particularly interested in the massive different between cost and
> actual for the index plan. The seq scan plan has 451984/248694 (ratio
> 1.82) for cost/actual, while the index plan has 502051/11597 (ratio
> 43.29). At least the seq scan plan is only out by a factor of ~2.
Most likely this means that the index plan is taking a lot more advantage
of locality-of-reference than the planner is giving it credit for.
I wouldn't put too much faith in those numbers by themselves though,
because that's what nearly always happens if you run the same case
through EXPLAIN more than once: all the data it needs is already in
cache. It's a good idea to pay attention to what happens when the plan
does have to read in some new data.
regards, tom lane
--
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: tgl@sss.pgh.pa.us, alistair@abayley.org, jeff.janes@gmail.com
Subject: Re: Why is the optimiser choosing the slower query, or, understanding explain analyze output
In-Reply-To: <12233.1392690614@sss.pgh.pa.us>
* 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