Re: 8.1 count(*) distinct: IndexScan/SeqScan - Mailing list pgsql-performance

I redo the test, with a freshly installed data directory. Same result.

Note: This is the full log. I just suppress the mistake I do like
"sl" for "ls".

Jean-Gérard Pailloncy


Last login: Thu Nov 24 12:52:32 2005 from 192.168.0.1
OpenBSD 3.8 (WDT) #2: Tue Nov  8 00:52:38 CET 2005

Welcome to OpenBSD: The proactively secure Unix-like operating system.

Please use the sendbug(1) utility to report bugs in the system.
Before reporting a bug, please try to reproduce it with the latest
version of the code.  With bug reports, please try to ensure that
enough information to reproduce the problem is enclosed, and if a
known fix for it exists, include that as well.

Terminal type? [xterm-color]
# cd /mnt2/pg/install/bin/
# mkdir /mnt2/pg/data
# chown -R _pgsql:_pgsql /mnt2/pg/data
# su _pgsql
$ ls
clusterdb        droplang         pg_config        pg_resetxlog
reindexdb
createdb         dropuser         pg_controldata   pg_restore
vacuumdb
createlang       ecpg             pg_ctl           postgres
createuser       initdb           pg_dump          postmaster
dropdb           ipcclean         pg_dumpall       psql
$ ./initdb -D /mnt2/pg/data
The files belonging to this database system will be owned by user
"_pgsql".
This user must also own the server process.

The database cluster will be initialized with locale C.

fixing permissions on existing directory /mnt2/pg/data ... ok
creating directory /mnt2/pg/data/global ... ok
creating directory /mnt2/pg/data/pg_xlog ... ok
creating directory /mnt2/pg/data/pg_xlog/archive_status ... ok
creating directory /mnt2/pg/data/pg_clog ... ok
creating directory /mnt2/pg/data/pg_subtrans ... ok
creating directory /mnt2/pg/data/pg_twophase ... ok
creating directory /mnt2/pg/data/pg_multixact/members ... ok
creating directory /mnt2/pg/data/pg_multixact/offsets ... ok
creating directory /mnt2/pg/data/base ... ok
creating directory /mnt2/pg/data/base/1 ... ok
creating directory /mnt2/pg/data/pg_tblspc ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 1000
creating configuration files ... ok
creating template1 database in /mnt2/pg/data/base/1 ... ok
initializing pg_authid ... ok
enabling unlimited row size for system tables ... ok
initializing dependencies ... ok
creating system views ... ok
loading pg_description ... ok
creating conversions ... ok
setting privileges on built-in objects ... ok
creating information schema ... ok
vacuuming database template1 ... ok
copying template1 to template0 ... ok
copying template1 to postgres ... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the -A option the
next time you run initdb.

Success. You can now start the database server using:

     ./postmaster -D /mnt2/pg/data
or
     ./pg_ctl -D /mnt2/pg/data -l logfile start

$ ./pg_ctl -D /mnt2/pg/data -l /mnt2/pg/data/logfile start
postmaster starting
$ ./psql postgres
Welcome to psql 8.1.0, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
        \h for help with SQL commands
        \? for help with psql commands
        \g or terminate with semicolon to execute query
        \q to quit

postgres=#  create table test (id serial, val integer);
NOTICE:  CREATE TABLE will create implicit sequence "test_id_seq" for
serial column "test.id"
CREATE TABLE
postgres=# create unique index testid on test (id);
CREATE INDEX
postgres=# create index testval on test (val);
CREATE INDEX
postgres=# insert into test (val) values (round(random()
*1024*1024*1024));
INSERT 0 1
postgres=# vacuum full analyze;
VACUUM
postgres=# select count(1) from test;
count
-------
      1
(1 row)

postgres=# explain select count(*) from (select distinct on (val) *
from test) as foo;
                               QUERY PLAN
----------------------------------------------------------------------
Aggregate  (cost=1.04..1.05 rows=1 width=0)
    ->  Unique  (cost=1.02..1.03 rows=1 width=8)
          ->  Sort  (cost=1.02..1.02 rows=1 width=8)
                Sort Key: test.val
                ->  Seq Scan on test  (cost=0.00..1.01 rows=1 width=8)
(5 rows)

postgres=# insert into test (val) select round(random()
*1024*1024*1024) from test;
INSERT 0 1
postgres=# insert into test (val) select round(random()
*1024*1024*1024) from test;
INSERT 0 2
postgres=# insert into test (val) select round(random()
*1024*1024*1024) from test;
INSERT 0 4
postgres=# insert into test (val) select round(random()
*1024*1024*1024) from test;
INSERT 0 8
postgres=# insert into test (val) select round(random()
*1024*1024*1024) from test;
INSERT 0 16
postgres=# insert into test (val) select round(random()
*1024*1024*1024) from test;
INSERT 0 32
postgres=# vacuum full analyze;
VACUUM
postgres=# explain select count(*) from (select distinct on (val) *
from test) as foo;
                               QUERY PLAN
-----------------------------------------------------------------------
Aggregate  (cost=4.68..4.69 rows=1 width=0)
    ->  Unique  (cost=3.56..3.88 rows=64 width=8)
          ->  Sort  (cost=3.56..3.72 rows=64 width=8)
                Sort Key: test.val
                ->  Seq Scan on test  (cost=0.00..1.64 rows=64 width=8)
(5 rows)

postgres=# insert into test (val) select round(random()
*1024*1024*1024) from test;
INSERT 0 64
postgres=# insert into test (val) select round(random()
*1024*1024*1024) from test;
INSERT 0 128
postgres=# insert into test (val) select round(random()
*1024*1024*1024) from test;
INSERT 0 256
postgres=# insert into test (val) select round(random()
*1024*1024*1024) from test;
INSERT 0 512
postgres=# vacuum full analyze;
VACUUM
postgres=# explain select count(*) from (select distinct on (val) *
from test) as foo;
                                      QUERY PLAN
------------------------------------------------------------------------
------------
Aggregate  (cost=55.63..55.64 rows=1 width=0)
    ->  Unique  (cost=0.00..42.82 rows=1024 width=8)
          ->  Index Scan using testval on test  (cost=0.00..40.26
rows=1024 width=8)
(3 rows)

postgres=# select count(1) from test;
count
-------
   1024
(1 row)

postgres=# set enable_indexscan=off;
SET
postgres=# explain select count(*) from (select distinct on (val) *
from test) as foo;
                                 QUERY PLAN
------------------------------------------------------------------------
--
Aggregate  (cost=85.36..85.37 rows=1 width=0)
    ->  Unique  (cost=67.44..72.56 rows=1024 width=8)
          ->  Sort  (cost=67.44..70.00 rows=1024 width=8)
                Sort Key: test.val
                ->  Seq Scan on test  (cost=0.00..16.24 rows=1024
width=8)
(5 rows)

postgres=# set enable_indexscan=on;
SET
postgres=# insert into test (val) select round(random()
*1024*1024*1024) from test;
INSERT 0 1024
postgres=# vacuum full analyze;
VACUUM
postgres=# explain select count(*) from (select distinct on (val) *
from test) as foo;
                                      QUERY PLAN
------------------------------------------------------------------------
------------
Aggregate  (cost=105.25..105.26 rows=1 width=0)
    ->  Unique  (cost=0.00..79.65 rows=2048 width=8)
          ->  Index Scan using testval on test  (cost=0.00..74.53
rows=2048 width=8)
(3 rows)

postgres=#