Re: Using LIMIT changes index used by planner

От: Andrew McMillan
Тема: Re: Using LIMIT changes index used by planner
Дата: ,
Msg-id: 1102931789.11712.9.camel@lamb.mcmillan.net.nz
(см: обсуждение, исходный текст)
Ответ на: Using LIMIT changes index used by planner  (Sven Willenberger)
Ответы: Re: Using LIMIT changes index used by planner  (Sven Willenberger)
Список: pgsql-performance

Скрыть дерево обсуждения

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


Вложения

В списке pgsql-performance по дате сообщения:

От: Andrew McMillan
Дата:
Сообщение: Re: Using LIMIT changes index used by planner
От: sarlav kumar
Дата:
Сообщение: INSERT question