From: Colin McGuigan <cmcguigan@earthcomber.com>
To: pgsql-performance@postgresql.org
Subject: Re: Odd problem with planner choosing seq scan
Date: Sat, 21 Apr 2007 20:48:22 -0500
Message-ID: <462ABEE6.4060803@earthcomber.com> (raw)
In-Reply-To: <25028.1177175197@sss.pgh.pa.us>
References: <462A2ED2.20501@earthcomber.com>
<25028.1177175197@sss.pgh.pa.us>
Tom Lane wrote:
> The right way to do it is to adjust the planner cost parameters.
> The standard values of those are set on the assumption of
> tables-much-bigger-than-memory, a situation in which the planner's
> preferred plan probably would be the best. What you are testing here
> is most likely a situation in which the whole of both tables fits in
> RAM. If that pretty much describes your production situation too,
> then you should decrease seq_page_cost and random_page_cost. I find
> setting them both to 0.1 produces estimates that are more nearly in
> line with true costs for all-in-RAM situations.
>
I know I can do it by adjusting cost parameters, but I was really
curious as to why adding a "LIMIT 5000" onto a SELECT from a table with
only 530 rows in it would affect matters at all. The plan the planner
uses when LIMIT 5000 is on is the one I want, without adjusting any
performance costs. It doesn't seem to matter what the limit is -- LIMIT
99999 also produces the desired plan, whereas no LIMIT produces the
undesirable plan.
--Colin McGuigan
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: cmcguigan@earthcomber.com
Subject: Re: Odd problem with planner choosing seq scan
In-Reply-To: <462ABEE6.4060803@earthcomber.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