X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 0BC1EDBCC6 for ; Thu, 24 Nov 2005 22:37:52 -0400 (AST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 79409-02 for ; Fri, 25 Nov 2005 02:37:55 +0000 (GMT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 67F61DB839 for ; Thu, 24 Nov 2005 22:37:49 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id jAP2brcw017381; Thu, 24 Nov 2005 21:37:53 -0500 (EST) To: Pailloncy Jean-Gerard cc: pgsql-performance@postgresql.org Subject: Re: 8.1 count(*) distinct: IndexScan/SeqScan In-reply-to: References: <334D9941-B5B3-4D18-8312-F85D0FB054ED@rilk.com> Comments: In-reply-to Pailloncy Jean-Gerard message dated "Fri, 25 Nov 2005 00:34:16 +0100" Date: Thu, 24 Nov 2005 21:37:53 -0500 Message-ID: <17380.1132886273@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.004 required=5 tests=[AWL=0.004] X-Spam-Score: 0.004 X-Spam-Level: X-Archive-Number: 200511/506 X-Sequence-Number: 15763 Pailloncy Jean-Gerard 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