pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feed From: Hervé Piedvache <footcow@noos.fr>
To: pgsql-performance@postgresql.org
Subject: Why distinct so slow ?
Date: Mon, 15 Nov 2004 22:22:02 +0100
Message-ID: <200411152222.02601.footcow@noos.fr> (raw )
Hi,
I have a strange (for me) result ... Why the second request is really quicker
with a Seq Scan than the first one with a DISTINCT and using an index !?
The table have really 183957 rows ... not like the Seq Scan seems to
expect ... !? I understand nothing here ...
Thanks for your explanations ...
# explain analyze SELECT distinct s.id_category FROM site s;
QUERY
PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=0.00..11903.15 rows=56 width=4) (actual time=0.147..1679.170
rows=68 loops=1)
-> Index Scan using ix_site_id_category on site s (cost=0.00..11496.38
rows=162706 width=4) (actual time=0.143..1452.611 rows=183957 loops=1)
Total runtime: 1679.496 ms
(3 rows)
Time: 1680,810 ms
# explain analyze SELECT s.id_category FROM site s group by id_category;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=7307.83..7307.83 rows=56 width=4) (actual
time=1198.968..1199.084 rows=68 loops=1)
-> Seq Scan on site s (cost=0.00..6901.06 rows=162706 width=4) (actual
time=0.097..921.676 rows=183957 loops=1)
Total runtime: 1199.260 ms
(3 rows)
--
Bill Footcow
Message-ID: <200411152222.02601.footcow@noos.fr>
Permalink: ../200411152222.02601.footcow@noos.fr/
Also on: postgresql.org/message-id/200411152222.02601.footcow@noos.fr
copy link · copy postgr.es
reply 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: footcow@noos.fr
Subject: Re: Why distinct so slow ?
In-Reply-To: <200411152222.02601.footcow@noos.fr>
* 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