From: Robert Haas <robertmhaas@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Jonathan Hseu <vomjom@vomjom.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: Index scan plan estimates way off.
Date: Thu, 5 Mar 2009 21:33:06 -0500
Message-ID: <603c8f070903051833j29eba13tb3216610aa187a5@mail.gmail.com> (raw)
In-Reply-To: <8053.1236277851@sss.pgh.pa.us>
References: <78a8986f0903050756i7b1f6c27teb4c67d4578f11ae@mail.gmail.com>
<8053.1236277851@sss.pgh.pa.us>
On Thu, Mar 5, 2009 at 1:30 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Jonathan Hseu <vomjom@vomjom.net> writes:
>> Sort (cost=11684028.44..11761274.94 rows=30898601 width=40)
>> Sort Key: "time"
>> -> Bitmap Heap Scan on ticks (cost=715657.57..6995196.08 rows=30898601
>> width=40)
>> Recheck Cond: (contract_id = 1)
>> -> Bitmap Index Scan on contract_id_time_idx
>> (cost=0.00..707932.92 rows=30898601 width=0)
>> Index Cond: (contract_id = 1)
>> (6 rows)
>
>> This plan doesn't complete in a reasonable amount of time. I end up having
>> to kill the query after it's been running for over an hour.
>
> The bitmap scan should be at least as efficient as the plain indexscan,
> so I suppose the problem is that the sort is slow. What's the datatype
> of "time"? Can this machine actually support 256MB+ work_mem, or is that
> likely to be driving it into swapping?
>
> You might learn more from enabling trace_sort and watching the
> postmaster log entries it generates. On the whole I think the planner
> isn't making a stupid choice here: sorting a large number of rows
> usually *is* preferable to making an indexscan over them, unless the
> table is remarkably close to being in physical order for the index.
It seems like this is only likely to be true if most of the data needs
to be read from a magnetic disk, so that many seeks are involved.
That might not be the case here, since the machine has an awful lot of
RAM.
...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, tgl@sss.pgh.pa.us, vomjom@vomjom.net
Subject: Re: Index scan plan estimates way off.
In-Reply-To: <603c8f070903051833j29eba13tb3216610aa187a5@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