Re: Why is indexonlyscan so darned slow?

On Tue, May 22, 2012 at 12:29 AM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
> Generally though the real world wins (although the gains will be
> generally less spectacular) are heavily i/o bound queries where the
> indexed subset of data you want is nicely packed and the (non
> clustered) heap records are all over the place.  By skipping the semi
> random heap lookups you can see enormous speedups.  I figure 50-90%
> improvement would be the norm there, but this is against queries that
> are taking forever, being i/o bound.

The heap fetch/visibility check overhead is also a problem for CPU
bound workloads. Example:

CREATE TABLE test AS SELECT x, (RANDOM()*1000000000) AS value FROM
generate_series(1,10000000) AS x;
CREATE INDEX ON test(value, x);
VACUUM ANALYZE test;

Then running the following pgbench script with 4G buffers:

\setrandom rangemin 1 1000000000
\set rangemax :rangemin + 1000000
SELECT MIN(x) FROM test WHERE value BETWEEN :rangemin AND :rangemax;

I get the following results:

bitmap scan: 106 tps
index scan: 146 tps
index only scan: 653 tps

Ants Aasma
--
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt
Web: http://www.postgresql-support.de