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

 · 

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