X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id CC07432A3A5 for ; Wed, 6 Oct 2004 11:16:39 +0100 (BST) 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 41331-09 for ; Wed, 6 Oct 2004 10:16:31 +0000 (GMT) Received: from boutiquenumerique.com (gailleton-2-82-67-9-10.fbx.proxad.net [82.67.9.10]) by svr1.postgresql.org (Postfix) with ESMTP id 61CA332A2ED for ; Wed, 6 Oct 2004 11:16:21 +0100 (BST) Received: (qmail 3818 invoked from network); 6 Oct 2004 12:16:46 +0200 Received: from unknown (HELO musicbox) (192.168.0.2) by gailleton-2-82-67-9-10.fbx.proxad.net with SMTP; 6 Oct 2004 12:16:46 +0200 To: "Ole Langbehn" , pgsql-performance@postgresql.org Subject: Re: sequential scan on select distinct References: <200410061130.58625.ole@freiheit.com> Message-ID: Date: Wed, 06 Oct 2004 12:19:24 +0200 From: =?iso-8859-15?Q?Pierre-Fr=E9d=E9ric_Caillaud?= Organization: =?iso-8859-15?Q?La_Boutique_Num=E9rique?= Content-Type: text/plain; format=flowed; delsp=yes; charset=iso-8859-15 MIME-Version: 1.0 Content-Transfer-Encoding: 8bit In-Reply-To: <200410061130.58625.ole@freiheit.com> User-Agent: Opera M2/7.53 (Linux, build 737) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200410/56 X-Sequence-Number: 8532 You could try : explain analyze select "land" from "customer_dim" group by "land"; It will be a lot faster but I can't make it use the index on my machine... Example : create table dummy as (select id, id%255 as number from a large table with 1M rows); so we have a table with 256 (0-255) disctinct "number" values. -------------------------------------------------------------------------------- => explain analyze select distinct number from dummy; Unique (cost=69.83..74.83 rows=200 width=4) (actual time=13160.490..14414.004 rows=255 loops=1) -> Sort (cost=69.83..72.33 rows=1000 width=4) (actual time=13160.483..13955.792 rows=1000000 loops=1) Sort Key: number -> Seq Scan on dummy (cost=0.00..20.00 rows=1000 width=4) (actual time=0.052..1759.145 rows=1000000 loops=1) Total runtime: 14442.872 ms => Horribly slow because it has to sort 1M rows for the Unique. -------------------------------------------------------------------------------- => explain analyze select number from dummy group by number; HashAggregate (cost=22.50..22.50 rows=200 width=4) (actual time=1875.214..1875.459 rows=255 loops=1) -> Seq Scan on dummy (cost=0.00..20.00 rows=1000 width=4) (actual time=0.107..1021.014 rows=1000000 loops=1) Total runtime: 1875.646 ms => A lot faster because it HashAggregates instead of sorting (but still seq scan) -------------------------------------------------------------------------------- Now : create index dummy_idx on dummy(number); Let's try again. -------------------------------------------------------------------------------- explain analyze select distinct number from dummy; Unique (cost=0.00..35301.00 rows=200 width=4) (actual time=0.165..21781.732 rows=255 loops=1) -> Index Scan using dummy_idx on dummy (cost=0.00..32801.00 rows=1000000 width=4) (actual time=0.162..21154.752 rows=1000000 loops=1) Total runtime: 21782.270 ms => Index scan the whole table. argh. I should have ANALYZized. -------------------------------------------------------------------------------- explain analyze select number from dummy group by number; HashAggregate (cost=17402.00..17402.00 rows=200 width=4) (actual time=1788.425..1788.668 rows=255 loops=1) -> Seq Scan on dummy (cost=0.00..14902.00 rows=1000000 width=4) (actual time=0.048..960.063 rows=1000000 loops=1) Total runtime: 1788.855 ms => Still the same... -------------------------------------------------------------------------------- Let's make a function : The function starts at the lowest number and advances to the next number in the index until they are all exhausted. CREATE OR REPLACE FUNCTION sel_distinct() RETURNS SETOF INTEGER LANGUAGE plpgsql AS ' DECLARE pos INTEGER; BEGIN SELECT INTO pos number FROM dummy ORDER BY number ASC LIMIT 1; IF NOT FOUND THEN RAISE NOTICE ''no records.''; RETURN; END IF; LOOP RETURN NEXT pos; SELECT INTO pos number FROM dummy WHERE number>pos ORDER BY number ASC LIMIT 1; IF NOT FOUND THEN RETURN; END IF; END LOOP; END; '; explain analyze select * from sel_distinct(); Function Scan on sel_distinct (cost=0.00..12.50 rows=1000 width=4) (actual time=215.472..215.696 rows=255 loops=1) Total runtime: 215.839 ms That's better ! -------------------------------------------------------------------------------- Why not use DESC instead of ASC ? CREATE OR REPLACE FUNCTION sel_distinct() RETURNS SETOF INTEGER LANGUAGE plpgsql AS ' DECLARE pos INTEGER; BEGIN SELECT INTO pos number FROM dummy ORDER BY number DESC LIMIT 1; IF NOT FOUND THEN RAISE NOTICE ''no records.''; RETURN; END IF; LOOP RETURN NEXT pos; SELECT INTO pos number FROM dummy WHERE number