pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Michael Contzen <mcontzen@dohle.com>
To: pgsql-sql@postgresql.org
Subject: Re: How slow is DISTINCT?
Date: Tue, 09 Apr 2002 12:15:47 +0200
Message-ID: <3CB2BF53.77D41E79@dohle.com> (raw)

Hello,

our problem to 'select distinct' is similar: We have a big table of
about 320.000.000 rows of an datawarehouse application. 55 GB, yes, it
works fine! 

Createing an index on it quite fast (nearly as fast than oracle on the
same data and same machine) 1GHz P4, 4GB-Ram -> 30 Minutes. 

Then we tried 'select distinct one_field' which would result to about
200.000 different values. Postgres needed 6 hours while Oracle managed
it in about 30 minutes.

Looking into the pgsql_tmp directory of this db while doing this
selection showed me a lot of tempfiles nearly as big as the table.

Does postgres sort the whole table without projection to one column an
performs a unique on this whole table?
This would explain the big amount of disk usage in pgsql_tmp and the big
amount of time.

The statement, something could be wrong with the data, is not very
useful: This is data of our electronic cash-desks. Unfortunately our
customers buy every day nearly the same articles - therefor the
repeatition of data :-)


Kind regards

M.Contzen
Developer 
Dohle Systemberatung
Germany


Some facts of our test:

         Table "warenausg_ges"
  Column   |     Type      | Modifiers 
-----------+---------------+-----------
 ean       | numeric(13,0) | 
 menge     | numeric(13,3) | 
 lvkumsatz | numeric(15,3) | 
 vkumsatz  | numeric(15,3) | 
 ekumsatz  | numeric(15,3) | 
 rabatt    | numeric(12,0) | 
 kdnr      | numeric(10,0) | 
 artnr     | numeric(10,0) | 
 lfnr      | numeric(10,0) | 
 wg        | integer       | 
 aktion    | character(1)  | 
 datum     | date          | 
 status    | integer       | 
Indexes: warenausg_ges_inx

Index "warenausg_ges_inx"
 Column | Type 
--------+------
 datum  | date
btree

explain select distinct artnr from warenausg_ges;
NOTICE:  QUERY PLAN:

Unique  (cost=224522801.22..225315849.86 rows=31721946 width=12)
  ->  Sort  (cost=224522801.22..224522801.22 rows=317219456 width=12)
        ->  Seq Scan on warenausg_ges  (cost=0.00..165793667.00
rows=317219456 width=12)



view thread (10+ messages)  latest in thread

Message-ID: <3CB2BF53.77D41E79@dohle.com>
Permalink:  ../3CB2BF53.77D41E79@dohle.com/
Also on:    postgresql.org/message-id/3CB2BF53.77D41E79@dohle.com

 · 

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-sql@postgresql.org
  Cc: mcontzen@dohle.com
  Subject: Re: How slow is DISTINCT?
  In-Reply-To: <3CB2BF53.77D41E79@dohle.com>

* 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