DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type:content-transfer-encoding;
	b=o1h8r7SCK7WqtLLSGXFPiOS+CAxDirTBzdsGQFyTYX3Gt/Y4rzGFQAH0QTVUDElIRm
	6vN9L70jja4BRAVL/YP9UY4BcpntpZnE6EFutRc1Ntal9ERTeUSu9A/5l0dimenFy+Cn
	mPuTJYEItlyXsG+7CZtTO2Oy52L42coBU70u4=
MIME-Version: 1.0
In-Reply-To: <o2n8bdec0841004021119u69517c9fm9d239dc36b6af397@mail.gmail.com>
References: <o2n8bdec0841004021119u69517c9fm9d239dc36b6af397@mail.gmail.com>
Date: Tue, 6 Apr 2010 14:33:51 -0400
Message-ID: <o2k603c8f071004061133qbfeccea6iec13efdbc3c080d7@mail.gmail.com>
Subject: Re: LIMIT causes planner to do Index Scan using a less optimal index
From: Robert Haas <robertmhaas@gmail.com>
To: Joel Jacobson <joel@gluefinance.com>
Cc: pgsql-performance@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

On Fri, Apr 2, 2010 at 2:19 PM, Joel Jacobson <joel@gluefinance.com> wrote:
> Is this a bug?=A0I'm using version 8.4.1.

It's not really a bug, but it's definitely not a feature either.

> =A0Limit =A0(cost=3D0.00..43.46 rows=3D1 width=3D4) (actual time=3D1023.2=
13..1023.214
> rows=3D1 loops=3D1)
> =A0=A0 -> =A0Index Scan using transactions_pkey on transactions
> =A0(cost=3D0.00..493029.74 rows=3D11345 width=3D4) (actual time=3D1023.21=
2..1023.212
> rows=3D1 loops=3D1)
> =A0=A0 =A0 =A0 =A0 Filter: ((accountid =3D 108) AND (currency =3D 'SEK'::=
bpchar))
> =A0Total runtime: 1023.244 ms
> (4 rows)

The planner's idea here is that rows matching the filter criteria will
be common enough that an index scan over transactions_pkey will find
one fairly quickly, at which point the executor can return that row
and stop.  But it turns out that those rows aren't as common as the
planner thinks, so the search takes a long time.

...Robert