On Tue, May 22, 2012 at 12:29 AM, Merlin Moncure <mmonc...@gmail.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
