X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 903CC9FA5C8 for ; Thu, 1 Jun 2006 13:07:08 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 30040-01 for ; Thu, 1 Jun 2006 13:07:02 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from titan.startnet.cz (unknown [82.113.63.73]) by postgresql.org (Postfix) with ESMTP id 3CDB49FA040 for ; Thu, 1 Jun 2006 13:07:00 -0300 (ADT) Received: (qmail 5031 invoked from network); 1 Jun 2006 18:06:59 +0200 Received: from unknown (HELO ?192.168.1.2?) (miroslav.sulc@startnet.cz@10.15.192.25) by titan.local.startnet.cz with ESMTPA; 1 Jun 2006 18:06:59 +0200 Message-ID: <447F109D.5030400@startnet.cz> Date: Thu, 01 Jun 2006 18:06:53 +0200 From: =?windows-1252?Q?Miroslav_=8Aulc?= Organization: StartNet s.r.o. User-Agent: Thunderbird 1.5.0.2 (X11/20060501) MIME-Version: 1.0 CC: pgsql-sql@postgresql.org Subject: Re: SELECT DISTINCT too slow References: <447EEAF1.7040305@startnet.cz> <20060601135310.GB18513@surnet.cl> <447EFE92.2070509@startnet.cz> <20060601145327.GA19388@surnet.cl> <87k680hoa3.fsf@stark.xeocode.com> In-Reply-To: <87k680hoa3.fsf@stark.xeocode.com> Content-Type: multipart/mixed; boundary="------------040803080501030905030606" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200606/13 X-Sequence-Number: 25249 This is a multi-part message in MIME format. --------------040803080501030905030606 Content-Type: multipart/alternative; boundary="------------090705070806000602020902" --------------090705070806000602020902 Content-Type: text/plain; charset=windows-1252 Content-Transfer-Encoding: 8bit Greg Stark napsal(a): > Actually you could try the equivalent query: > > SELECT Key FROM MRTPContactValue GROUP BY Key > > This may or may not be faster because it can use a hash aggregate plan. I > would expect it to be faster here because there are few distinct keys and the > planner predicts that. > > Eventually these two queries should be handled the same by Postgres but Hash > Aggregates are a new addition and DISTINCT/DISTINCT ON hasn't been adapted to > make use of them. > > Also, incidentally, I don't see how a table of possible keys could help you > here. Nothing forces they table MRTPContactValue to use all possible keys... > I simpified the case because it was slow by itself. GROUP BY really makes this a lot faster. The table contains properties for each contact that I cannot control how many properties and what names of the properties there will be. In my scenario user can export the data through user interface and I need to know what keys are used there to create appropriate column names. There is even one constraint. The contacts are grouped into groups so I need to get only the keys from a selected group. The real query is this (which is not so fast as the plain SELECT ... GROUP BY ... because the other table is also large enough) but now it is faster than before: SELECT Key FROM MRTPContactValue INNER JOIN MRTPContact ON MRTPContactValue.MRTPContactId = MRTPContact.Id WHERE MRTPContact.MRTPWaveQuestionnaireId = 1 GROUP BY Key Here's the query plan: QUERY PLAN HashAggregate (cost=32639.67..32639.76 rows=9 width=9) (actual time=19407.116..19407.146 rows=10 loops=1) -> Hash Join (cost=8070.36..31598.51 rows=416466 width=9) (actual time=5917.367..17607.502 rows=416466 loops=1) Hash Cond: ("outer".mrtpcontactid = "inner".id) -> Seq Scan on mrtpcontactvalue (cost=0.00..8669.66 rows=416466 width=17) (actual time=9.094..4233.131 rows=416466 loops=1) -> Hash (cost=7119.80..7119.80 rows=137824 width=8) (actual time=5096.750..5096.750 rows=137824 loops=1) -> Seq Scan on mrtpcontact (cost=0.00..7119.80 rows=137824 width=8) (actual time=9.312..4337.647 rows=137824 loops=1) Filter: (mrtpwavequestionnaireid = 1) Total runtime: 19417.873 ms The same query using DISTINCT takes about 40 sec to complete. Thank you. -- Miroslav Šulc --------------090705070806000602020902 Content-Type: text/html; charset=windows-1252 Content-Transfer-Encoding: 8bit Greg Stark napsal(a): 
Actually you could try the equivalent query:

 SELECT Key FROM MRTPContactValue GROUP BY Key

This may or may not be faster because it can use a hash aggregate plan. I
would expect it to be faster here because there are few distinct keys and the
planner predicts that. 

Eventually these two queries should be handled the same by Postgres but Hash
Aggregates are a new addition and DISTINCT/DISTINCT ON hasn't been adapted to
make use of them.

Also, incidentally, I don't see how a table of possible keys could help you
here. Nothing forces they table MRTPContactValue to use all possible keys...

I simpified the case because it was slow by itself. GROUP BY really makes this a lot faster.


The table contains properties for each contact that I cannot control how many properties and what names of the properties there will be. In my scenario user can export the data through user interface and I need to know what keys are used there to create appropriate column names. There is even one constraint. The contacts are grouped into groups so I need to get only the keys from a selected group. The real query is this (which is not so fast as the plain SELECT ... GROUP BY ... because the other table is also large enough) but now it is faster than before:

SELECT Key FROM MRTPContactValue
INNER JOIN MRTPContact
ON MRTPContactValue.MRTPContactId = MRTPContact.Id
WHERE MRTPContact.MRTPWaveQuestionnaireId = 1
GROUP BY Key


Here's the query plan:

QUERY PLAN 
HashAggregate  (cost=32639.67..32639.76 rows=9 width=9) (actual time=19407.116..19407.146 rows=10 loops=1)
 
  ->  Hash Join  (cost=8070.36..31598.51 rows=416466 width=9) (actual time=5917.367..17607.502 rows=416466 loops=1)
 
        Hash Cond: ("outer".mrtpcontactid = "inner".id)
 
        ->  Seq Scan on mrtpcontactvalue  (cost=0.00..8669.66 rows=416466 width=17) (actual time=9.094..4233.131 rows=416466 loops=1)
 
        ->  Hash  (cost=7119.80..7119.80 rows=137824 width=8) (actual time=5096.750..5096.750 rows=137824 loops=1)
 
              ->  Seq Scan on mrtpcontact  (cost=0.00..7119.80 rows=137824 width=8) (actual time=9.312..4337.647 rows=137824 loops=1)
 
                    Filter: (mrtpwavequestionnaireid = 1)
Total runtime: 19417.873 ms


The same query using DISTINCT takes about 40 sec to complete.


Thank you.

--
Miroslav Šulc
--------------090705070806000602020902-- --------------040803080501030905030606 Content-Type: text/x-vcard; charset=utf-8; name="miroslav.sulc.vcf" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="miroslav.sulc.vcf" begin:vcard fn;quoted-printable:Miroslav =C5=A0ulc n;quoted-printable:=C5=A0ulc;Miroslav org:StartNet s.r.o. adr;quoted-printable;quoted-printable:;;Schodov=C3=A1 309/10;Praha 5;;150 00;=C4=8Cesk=C3=A1 republika email;internet:miroslav.sulc@startnet.cz tel;cell:+420 603 711 413 x-mozilla-html:TRUE url:http://www.startnet.cz version:2.1 end:vcard --------------040803080501030905030606--