From: Tom Lane <tgl@sss.pgh.pa.us>
To: stange@rentec.com
Cc: pgsql-performance@postgresql.org
Subject: Re: difference in plan between 8.0 and 8.1?
Date: Fri, 26 Aug 2005 11:16:55 -0400
Message-ID: <9248.1125069415@sss.pgh.pa.us> (raw)
In-Reply-To: <430F2AF3.1080704@rentec.com>
References: <430F2AF3.1080704@rentec.com>
Alan Stange <stange@rentec.com> writes:
> Unique (cost=2717137.08..2771407.21 rows=10854026 width=8)
> -> Sort (cost=2717137.08..2744272.14 rows=10854026 width=8)
> Sort Key: timeseriesid
> -> Bitmap Heap Scan on tbltimeseries
> (cost=48714.09..1331000.42 rows=10854026 width=8)
> Recheck Cond: (timeseriesid > 0)
> -> Bitmap Index Scan on idx_timeseris
> (cost=0.00..48714.09 rows=10854026 width=0)
> Index Cond: (timeseriesid > 0)
> (7 rows)
> I'm hoping someone can explain the new query plan (as I'm not sure I
> understand what it is doing).
The index scan is reading the index to find out which heap tuple IDs
(TIDs) the index says meet the condition. It returns a bitmap of the
tuple locations (actually, an array of per-page bitmaps). The heap
scan goes and fetches the tuples from the table, working in TID order
to avoid re-reading the same page many times, as can happen for ordinary
index scans. Since the result isn't sorted, we have to do a sort to get
it into the correct order for the Unique step.
Because it avoids random access to the heap, this plan can be a lot
faster than a regular index scan. I'm not sure at all that 8.1 is
doing good relative cost estimation yet, though. It would be
interesting to see EXPLAIN ANALYZE results for both ways. (You can
use enable_bitmapscan and enable_indexscan to force the planner to pick
the plan it thinks is slower.)
regards, tom lane
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: tgl@sss.pgh.pa.us, stange@rentec.com
Subject: Re: difference in plan between 8.0 and 8.1?
In-Reply-To: <9248.1125069415@sss.pgh.pa.us>
* 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