On 11 October 2011 20:11, Tom Lane <t...@sss.pgh.pa.us> wrote: > Bruce Momjian <br...@momjian.us> writes: >> Tom Lane wrote: >>> Consider index-only scans even when there is no matching qual or ORDER BY. >>> >>> By popular demand. > >> Is this the COUNT(*) optimization? > > Yeah, among other cases.
This is unexpected: test=# explain analyse select count(*) from stuff; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=309724.57..309724.58 rows=1 width=0) (actual time=5622.932..5622.932 rows=1 loops=1) -> Seq Scan on stuff (cost=0.00..263974.46 rows=18300046 width=0) (actual time=0.052..3960.289 rows=18300000 loops=1) Total runtime: 5623.076 ms (3 rows) -- postgres restarted here test=# set random_page_cost = 1.0; SET test=# set seq_page_cost = 5.0; SET test=# explain analyse select count(*) from stuff; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=437191.32..437191.33 rows=1 width=0) (actual time=171652.106..171652.106 rows=1 loops=1) -> Index Only Scan using idx_stuff_thing on stuff (cost=0.00..393933.31 rows=17303202 width=0) (actual time=0.248..169062.893 rows=18300000 loops=1) Total runtime: 171652.179 ms (3 rows) So an index-only scan is 30 times slower in this particular test case. If you're curious, it was set up as so: test=# create table stuff (id serial, thing int); NOTICE: CREATE TABLE will create implicit sequence "stuff_id_seq" for serial column "stuff.id" CREATE TABLE test=# insert into stuff (thing) select ceil(random()*50) from generate_series(1,900000); INSERT 0 900000 test=# insert into stuff (thing) select ceil(random()*350) from generate_series(1,1200000); INSERT 0 1200000 test=# insert into stuff (thing) select ceil(random()*50) from generate_series(1,2200000); INSERT 0 2200000 test=# create index idx_stuff_thing on stuff (thing); CREATE INDEX test=# vacuum analyse; VACUUM test=# insert into stuff (thing) select ceil(random()*50) from generate_series(1,5000000); INSERT 0 5000000 test=# insert into stuff (thing) select ceil(random()*70) from generate_series(1,9000000); INSERT 0 9000000 test=# vacuum analyse; VACUUM If I drop the index used here, and recreate it, I get: test=# explain analyse select count(*) from stuff; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=443955.17..443955.18 rows=1 width=0) (actual time=4920.709..4920.709 rows=1 loops=1) -> Index Only Scan using idx_stuff_thing on stuff (cost=0.00..398205.06 rows=18300046 width=0) (actual time=0.330..3353.140 rows=18300000 loops=1) Total runtime: 4920.846 ms (3 rows) -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-committers mailing list (pgsql-committers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-committers