From: Tom Lane <tgl@sss.pgh.pa.us>
To: Claudio Freire <klaussfreire@gmail.com>
Cc: Dieter Rehbein <dieter.rehbein@skiline.cc>
Cc: pgsql-performance@postgresql.org
Subject: Re: performance problem with LIMIT (order BY in DESC order). Wrong index used?
Date: Tue, 12 Apr 2011 10:38:27 -0400
Message-ID: <9713.1302619107@sss.pgh.pa.us> (raw)
In-Reply-To: <BANLkTik6U2MU=s9mxr3=pK3Fp+S4bAJK3Q@mail.gmail.com>
References: <152FFD9B-A288-4329-9C96-0FF0C6A2D418@skiline.cc>
<BANLkTik6U2MU=s9mxr3=pK3Fp+S4bAJK3Q@mail.gmail.com>
Claudio Freire <klaussfreire@gmail.com> writes:
> Did you try increasing the statistic targets?
> AFAIK, it looks a lot like the planner is missing stats, since it
> estimates the index query on idx_nfi_newsfeed will fetch 10k rows -
> instead of 25.
BTW, this is the right suggestion, but for the wrong reason. You seem
to be looking at
Limit (cost=0.00..980.09 rows=25 width=963) (actual time=48.592..4060.779 rows=25 loops=1)
-> Index Scan Backward using "IDX_NFI_DATETIME" on newsfeed_item (cost=0.00..409365.16 rows=10442 width=963) (actual time=48.581..4060.542 rows=25 loops=1)
Here, the actual row count is constrained to 25 because the LIMIT node
stops calling the indexscan node once it's got 25. So this case proves
little about whether the planner's estimates are any good. You need to
check the estimates in the unconstrained plan:
-> Bitmap Heap Scan on newsfeed_item (cost=421.41..34450.72 rows=10442 width=963) (actual time=0.644..12.601 rows=477 loops=1)
Here we can see that there really are only 477 rows in the table that
satisfy the WHERE clause, versus an estimate of 10K. So sure enough,
the statistics are bad, and an increase in stats target might help.
But you can't conclude that from an explain that involves LIMIT.
regards, tom lane
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: tgl@sss.pgh.pa.us, klaussfreire@gmail.com, dieter.rehbein@skiline.cc
Subject: Re: performance problem with LIMIT (order BY in DESC order). Wrong index used?
In-Reply-To: <9713.1302619107@sss.pgh.pa.us>
* 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