Message-ID: <c48d12d868ecc4b9ab6063f562650722.squirrel@sq.gransy.com>
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>
Date: Wed, 8 Jun 2011 11:47:43 +0200
Subject: Re: strange query plan with LIMIT
From: tv@fuzzy.cz
To: anthony.shipman@symstream.com
Cc: pgsql-performance@postgresql.org,
 "Claudio Freire" <klaussfreire@gmail.com>
User-Agent: SquirrelMail/1.4.21
MIME-Version: 1.0
Content-Type: text/plain;charset=utf-8
Content-Transfer-Encoding: 8bit
Importance: Normal

> 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