Skip site navigation (1) Skip section navigation (2)

Re: Using LIMIT changes index used by planner

From: Andrew McMillan <andrew(at)catalyst(dot)net(dot)nz>
To: Sven Willenberger <sven(at)dmv(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Using LIMIT changes index used by planner
Date: 2004-12-13 09:56:29
Message-ID: 1102931789.11712.9.camel@lamb.mcmillan.net.nz (view raw or flat)
Thread:
Lists: pgsql-performance
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!
-------------------------------------------------------------------------

In response to

Responses

pgsql-performance by date

Next:From: sarlav kumarDate: 2004-12-13 16:28:39
Subject: INSERT question
Previous:From: Sven WillenbergerDate: 2004-12-13 06:13:43
Subject: Using LIMIT changes index used by planner

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group