From: Pierre-Frédéric Caillaud <lists@boutiquenumerique.com>
To: Ole Langbehn <ole@freiheit.com>
To: pgsql-performance@postgresql.org
Subject: Re: sequential scan on select distinct
Date: Wed, 06 Oct 2004 12:19:24 +0200
Message-ID: <opsff1mmp8cq72hf@musicbox> (raw)
In-Reply-To: <200410061130.58625.ole@freiheit.com>
References: <200410061130.58625.ole@freiheit.com>
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<pos ORDER BY number DESC
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=13.500..13.713 rows=255 loops=1)
Total runtime: 13.857 ms
Hum hum ! Again, a lot better !
Index scan backwards seems a lot faster than index scan forwards. Why, I
don't know, but here you go from 15 seconds to 14 milliseconds...
I don't know WHY (oh why) postgres does not use this kind of strategy
when distinct'ing an indexed field... Anybody got an idea ?
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: lists@boutiquenumerique.com, ole@freiheit.com
Subject: Re: sequential scan on select distinct
In-Reply-To: <opsff1mmp8cq72hf@musicbox>
* 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