From: Andrew McMillan <andrew@catalyst.net.nz>
To: Sven Willenberger <sven@dmv.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Using LIMIT changes index used by planner
Date: Mon, 13 Dec 2004 22:56:29 +1300
Message-ID: <1102931789.11712.9.camel@lamb.mcmillan.net.nz> (raw)
In-Reply-To: <41BD3317.9090507@dmv.com>
References: <41BD3317.9090507@dmv.com>
On Mon, 2004-12-13 at 01:13 -0500, Sven Willenberger wrote:
> I have a question regarding a serious performance hit taken when using a
> LIMIT clause. I am using version 7.4.6 on FreeBSD 4.10-STABLE with 2GB
> of memory. The table in question contains some 25 million rows with a
> bigserial primary key, orderdate index and a referrer index. The 2
> select statements are as follow:
It's an interesting question, but to be able to get answers from this
list you will need to provide "EXPLAIN ANALYZE ..." rather than just
"EXPLAIN ...".
AFAICS the bad plan on LIMIT is because it optimistically thinks the
odds are around the 0.00 end, rather than the 64297840.86 end, and
indeed that is what the "Limit ..." estimate is showing. A bad plan (in
your case) is encouraged here by the combination of "LIMIT" and "ORDER
BY".
For real background on this, and calculated recommendations, we'd need
that more detailed output though.
As a quick hack, it's possible that you could improve things by
increasing the samples on relevant columns with some judicious "ALTER
TABLE ... ALTER COLUMN ... SET STATISTICS ..." commands.
Cheers,
Andrew McMillan.
-------------------------------------------------------------------------
Andrew @ Catalyst .Net .NZ Ltd, PO Box 11-053, Manners St, Wellington
WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St
DDI: +64(4)803-2201 MOB: +64(272)DEBIAN OFFICE: +64(4)499-2267
Planning an election? Call us!
-------------------------------------------------------------------------
Attachments:
signature.asc (application/pgp-signature, 188B) download
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: andrew@catalyst.net.nz, sven@dmv.com
Subject: Re: Using LIMIT changes index used by planner
In-Reply-To: <1102931789.11712.9.camel@lamb.mcmillan.net.nz>
* 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