From: Tom Lane <tgl@sss.pgh.pa.us>
To: Colin McGuigan <cmcguigan@earthcomber.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Odd problem with planner choosing seq scan
Date: Sat, 21 Apr 2007 22:45:11 -0400
Message-ID: <16596.1177209911@sss.pgh.pa.us> (raw)
In-Reply-To: <462ABEE6.4060803@earthcomber.com>
References: <462A2ED2.20501@earthcomber.com>
<25028.1177175197@sss.pgh.pa.us>
<462ABEE6.4060803@earthcomber.com>
Colin McGuigan <cmcguigan@earthcomber.com> writes:
> 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 LIMIT prevents the sub-select from being flattened into the main
query. In the current code this has a side-effect of preventing any
statistical information from being used to estimate the selectivity
of the filter conditions --- so you get a default rowcount estimate
that's way too small, and that changes the shape of the join plan.
It's giving you the "right" answer for entirely the wrong reason.
regards, tom lane
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, cmcguigan@earthcomber.com
Subject: Re: Odd problem with planner choosing seq scan
In-Reply-To: <16596.1177209911@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