From: Tom Lane <tgl@sss.pgh.pa.us>
To: Pailloncy Jean-Gerard <jg@rilk.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: 8.1 count(*) distinct: IndexScan/SeqScan
Date: Thu, 24 Nov 2005 21:37:53 -0500
Message-ID: <17380.1132886273@sss.pgh.pa.us> (raw)
In-Reply-To: <A6F55E12-4479-49F6-B3EE-8CFCC8541179@rilk.com>
References: <334D9941-B5B3-4D18-8312-F85D0FB054ED@rilk.com>
<A6F55E12-4479-49F6-B3EE-8CFCC8541179@rilk.com>
Pailloncy Jean-Gerard <jg@rilk.com> writes:
> I redo the test, with a freshly installed data directory. Same result.
What "same result"? You only ran it up to 2K rows, not 2M. In any
case, EXPLAIN without ANALYZE is pretty poor ammunition for complaining
that the planner made the wrong choice. I ran the same test case,
and AFAICS the indexscan is the right choice at 2K rows:
regression=# explain analyze select count(*) from (select distinct on (val) * from test) as foo;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=105.24..105.25 rows=1 width=0) (actual time=41.561..41.565 rows=1 loops=1)
-> Unique (cost=0.00..79.63 rows=2048 width=8) (actual time=0.059..32.459 rows=2048 loops=1)
-> Index Scan using testval on test (cost=0.00..74.51 rows=2048 width=8) (actual time=0.049..13.197 rows=2048 loops=1)
Total runtime: 41.683 ms
(4 rows)
regression=# set enable_indexscan TO 0;
SET
regression=# explain analyze select count(*) from (select distinct on (val) * from test) as foo;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Aggregate (cost=179.96..179.97 rows=1 width=0) (actual time=59.567..59.571 rows=1 loops=1)
-> Unique (cost=144.12..154.36 rows=2048 width=8) (actual time=21.438..50.434 rows=2048 loops=1)
-> Sort (cost=144.12..149.24 rows=2048 width=8) (actual time=21.425..30.589 rows=2048 loops=1)
Sort Key: test.val
-> Seq Scan on test (cost=0.00..31.48 rows=2048 width=8) (actual time=0.014..9.902 rows=2048 loops=1)
Total runtime: 60.265 ms
(6 rows)
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, jg@rilk.com
Subject: Re: 8.1 count(*) distinct: IndexScan/SeqScan
In-Reply-To: <17380.1132886273@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox