From: tv@fuzzy.cz
To: anthony.shipman@symstream.com
Cc: pgsql-performance@postgresql.org, "Claudio Freire" <klaussfreire@gmail.com>
Subject: Re: strange query plan with LIMIT
Date: Wed, 8 Jun 2011 11:47:43 +0200
Message-ID: <c48d12d868ecc4b9ab6063f562650722.squirrel@sq.gransy.com> (raw)
In-Reply-To: <201106081834.07263.anthony.shipman@symstream.com>
References: <201106071802.08272.anthony.shipman@symstream.com>
<BANLkTi=+6EYeiFwKv5Eq8AAMt_nCZ+8W6g@mail.gmail.com>
<BANLkTim7DL+n0yuZp+Mpeq0Vozp1qJ4M0A@mail.gmail.com>
<201106081834.07263.anthony.shipman@symstream.com>
> On Wednesday 08 June 2011 17:39, Claudio Freire wrote:
>> Of course optimally executing a plan with limit is a lot different
>> than one without.
>
> I imagined that limit just cuts out a slice of the query results.
> If it can find 80000 rows in 0.5 seconds then I would have thought that
> returning just the first 100 of them should be just as easy.
But that's exactly the problem with LIMIT clause. The planner considers
two choices - index scan with this estimate
Index Scan using tdiag_pkey on tdiag (cost=0.00..19114765.76
rows=1141019 width=114)
and bitmap index scan with this estimate
Bitmap Heap Scan on tdiag (cost=25763.48..638085.13 rows=1141019
width=114)
and says - hey, the index scan has much lower starting cost, and I'm using
limit so it's much better! Let's use index scan. But then it finds out it
needs to scan most of the table and that ruins the performance.
Have you tried to create a composite index on those two columns? Not sure
if that helps but I'd try that.
Tomas
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: tv@fuzzy.cz, anthony.shipman@symstream.com, klaussfreire@gmail.com
Subject: Re: strange query plan with LIMIT
In-Reply-To: <c48d12d868ecc4b9ab6063f562650722.squirrel@sq.gransy.com>
* 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