X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 810E83A3B8C for ; Mon, 13 Dec 2004 09:56:47 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 42888-08 for ; Mon, 13 Dec 2004 09:56:35 +0000 (GMT) Received: from mail1.catalyst.net.nz (godel.catalyst.net.nz [202.49.159.12]) by svr1.postgresql.org (Postfix) with ESMTP id 8B0213A5FB4 for ; Mon, 13 Dec 2004 09:56:35 +0000 (GMT) Received: from 222-152-92-145.jetstream.xtra.co.nz ([222.152.92.145] helo=lamb.mcmillan.net.nz) by mail1.catalyst.net.nz with asmtp (TLS-1.0:DHE_RSA_3DES_EDE_CBC_SHA:24) (Exim 4.34) id 1CdmwR-0006Eh-16; Mon, 13 Dec 2004 22:56:31 +1300 Received: from lamb.mcmillan.net.nz (lamb.mcmillan.net.nz [127.0.0.1]) by lamb.mcmillan.net.nz (Postfix) with ESMTP id AE76FAD98588; Mon, 13 Dec 2004 22:56:30 +1300 (NZDT) Subject: Re: Using LIMIT changes index used by planner From: Andrew McMillan To: Sven Willenberger Cc: pgsql-performance@postgresql.org In-Reply-To: <41BD3317.9090507@dmv.com> References: <41BD3317.9090507@dmv.com> Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="=-hh0EoM0MFMK+Mnz9idhX" Date: Mon, 13 Dec 2004 22:56:29 +1300 Message-Id: <1102931789.11712.9.camel@lamb.mcmillan.net.nz> Mime-Version: 1.0 X-Mailer: Evolution 2.0.2 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.05 tagged_above=0 required=5 tests=FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200412/156 X-Sequence-Number: 9531 --=-hh0EoM0MFMK+Mnz9idhX Content-Type: text/plain Content-Transfer-Encoding: quoted-printable 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=20 > LIMIT clause. I am using version 7.4.6 on FreeBSD 4.10-STABLE with 2GB=20 > of memory. The table in question contains some 25 million rows with a=20 > bigserial primary key, orderdate index and a referrer index. The 2=20 > 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! ------------------------------------------------------------------------- --=-hh0EoM0MFMK+Mnz9idhX Content-Type: application/pgp-signature; name=signature.asc Content-Description: This is a digitally signed message part -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.5 (GNU/Linux) iD8DBQBBvWdMjJA0f48GgBIRAgF8AJ4hR07mtCDXnpCy0Bh05ZdzfEtxxwCfTW05 KRnt/3WuxuwnSl4V270Ajg0= =flF1 -----END PGP SIGNATURE----- --=-hh0EoM0MFMK+Mnz9idhX--