On 11 October 2011 20:11, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Bruce Momjian <bruce@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