Re: pgsql: Consider index-only scans even when there is no matching qual or

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