Re: Using LIMIT changes index used by planner

From: Andrew McMillan
Subject: Re: Using LIMIT changes index used by planner
Date: ,
Msg-id: 1102931789.11712.9.camel@lamb.mcmillan.net.nz
(view: Whole thread, Raw)
In response to: Using LIMIT changes index used by planner  (Sven Willenberger)
Responses: Re: Using LIMIT changes index used by planner  (Sven Willenberger)
List: pgsql-performance

Tree view

Using LIMIT changes index used by planner  (Sven Willenberger, )
 Re: Using LIMIT changes index used by planner  (Andrew McMillan, )
  Re: Using LIMIT changes index used by planner  (Sven Willenberger, )
   Re: Using LIMIT changes index used by planner  (Tom Lane, )
    Re: Using LIMIT changes index used by planner  (Sven Willenberger, )
     Re: Using LIMIT changes index used by planner  (Tom Lane, )
    Re: Using LIMIT changes index used by planner  (Pierre-Frédéric Caillaud<>, )

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!
-------------------------------------------------------------------------


Attachment

pgsql-performance by date:

From: Andrew McMillan
Date:
Subject: Re: Using LIMIT changes index used by planner
From: sarlav kumar
Date:
Subject: INSERT question