From: Robert Haas <robertmhaas@gmail.com>
To: Joel Jacobson <joel@gluefinance.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: LIMIT causes planner to do Index Scan using a less optimal index
Date: Tue, 6 Apr 2010 14:33:51 -0400
Message-ID: <o2k603c8f071004061133qbfeccea6iec13efdbc3c080d7@mail.gmail.com> (raw)
In-Reply-To: <o2n8bdec0841004021119u69517c9fm9d239dc36b6af397@mail.gmail.com>
References: <o2n8bdec0841004021119u69517c9fm9d239dc36b6af397@mail.gmail.com>
On Fri, Apr 2, 2010 at 2:19 PM, Joel Jacobson <joel@gluefinance.com> wrote:
> Is this a bug? I'm using version 8.4.1.
It's not really a bug, but it's definitely not a feature either.
> Limit (cost=0.00..43.46 rows=1 width=4) (actual time=1023.213..1023.214
> rows=1 loops=1)
> -> Index Scan using transactions_pkey on transactions
> (cost=0.00..493029.74 rows=11345 width=4) (actual time=1023.212..1023.212
> rows=1 loops=1)
> Filter: ((accountid = 108) AND (currency = 'SEK'::bpchar))
> Total 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
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: robertmhaas@gmail.com, joel@gluefinance.com
Subject: Re: LIMIT causes planner to do Index Scan using a less optimal index
In-Reply-To: <o2k603c8f071004061133qbfeccea6iec13efdbc3c080d7@mail.gmail.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