From: Tom Lane <tgl@sss.pgh.pa.us>
To: Bruno Wolff III <bruno@wolff.to>
Cc: Craig A. James <cjames@modgraph-usa.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Optimizer: limit not taken into account
Date: Wed, 17 May 2006 14:30:22 -0400
Message-ID: <24245.1147890622@sss.pgh.pa.us> (raw)
In-Reply-To: <20060517174426.GA14567@wolff.to>
References: <44699C6C.80302@tweakers.net>
<4469D173.50406@sun.com>
<4469D986.6090300@tweakers.net>
<4469F55A.7040004@sun.com>
<446A1520.2020601@tweakers.net>
<446B0409.7050902@sun.com>
<446B0ED2.5030909@tweakers.net>
<446B474C.2050803@modgraph-usa.com>
<20060517174426.GA14567@wolff.to>
Bruno Wolff III <bruno@wolff.to> writes:
> I suspect it wasn't intended to be a full table scan. But rather a sequential
> scan until it found a matching row. If the data in the table is ordered by
> by id, this strategy may not work out well. Where as if the data is randomly
> ordered, it would be expected to find a match quickly.
Right. You can see from the differential in the estimates for the
SeqScan and the Limit nodes that the planner is not expecting the
seqscan to run to completion, but rather to find a matching row quite
quickly.
There is not anything in there that considers whether the table's
physical order is so nonrandom that the search will take much longer
than it would given uniform distribution. It might be possible to do
something with the correlation statistic in simple cases ...
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, bruno@wolff.to, cjames@modgraph-usa.com
Subject: Re: Optimizer: limit not taken into account
In-Reply-To: <24245.1147890622@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