pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedselect distinct too slow
13+ messages / 8 participants
[nested] [flat]
* select distinct too slow
@ 2001-06-01 16:36 george young <gry@ll.mit.edu>
0 siblings, 1 reply; 13+ messages in thread
From: george young @ 2001-06-01 16:36 UTC (permalink / raw)
To: pgsql-sql
[postgresql 7.1.3, Sun ultrasparc, Solaris 2.5.1]
create table myjunk(run_name text,wafer_num int2,pass int2,test_type text,yloc int2,xloc int2,test_num int,test_data float8,comments text,primary key(run_name,wafer_num,yloc,xloc,test_type,test_num))
[insert 240765 rows]
vacuum analyze myjunk;
There are only 3 distinct values of run_name right now.
select distinct run_name from myjunk;
[takes ~ 14 seconds]
explain select distinct run_name from myjunk;
Unique (cost=34896.75..35498.66 rows=24076 width=12)
-> Sort (cost=34896.75..34896.75 rows=240765 width=12)
-> Seq Scan on myjunk (cost=0.00..6785.65 rows=240765 width=12)
How can I make this query be fast? I tried adding an index on run_name alone, that did not help.
I guess I can keep a separate table with unique run_name's... sounds kind of ugly.
If I did this, what sort of trigger or rule could maintain it automatically -- and how could it
be done without grossly slowing down large data loads into that table?
--
Your mouse has moved.
Windows NT must be restarted for the change to take effect.
Reboot now? [OK]
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: select distinct too slow
@ 2001-06-04 21:45 Hans-Jürgen Schönig <hs@cybertec.at>
parent: george young <gry@ll.mit.edu>
0 siblings, 0 replies; 13+ messages in thread
From: Hans-Jürgen Schönig @ 2001-06-04 21:45 UTC (permalink / raw)
To: pgsql-sql
Did you vacuum analyse your table after creating the index?
A solution for your problem would be to turn sequential scans ofd before querying the table.
This should work somehow like that:
myjava=# SET enable_seqscan TO 'off';
SET VARIABLE
myjava=# show enable_seqscan;
NOTICE: enable_seqscan is off
SHOW VARIABLE
check out the docs for further information.
Hans
^ permalink raw reply [nested|flat] 13+ messages in thread
* SELECT DISTINCT too slow
@ 2006-06-01 13:26 Miroslav Šulc <miroslav.sulc@startnet.cz>
0 siblings, 2 replies; 13+ messages in thread
From: Miroslav Šulc @ 2006-06-01 13:26 UTC (permalink / raw)
To: pgsql-sql
Hello,
I have a table with cca 400,000 rows. The table contains column "key" of
varchar(20) type containing 10 distinct values. I want to get out what
distinct values are present in the column. I use this simple query,
which is very slow:
SELECT DISTINCT Key FROM MRTPContactValue
Here is the query plan:
QUERY PLAN
Unique (cost=64882.26..66964.59 rows=9 width=9) (actual time=26139.972..29593.164 rows=10 loops=1)
-> Sort (cost=64882.26..65923.43 rows=416466 width=9) (actual time=26139.964..27975.944 rows=416466 loops=1)
Sort Key: "key"
-> Seq Scan on mrtpcontactvalue (cost=0.00..8669.66 rows=416466 width=9) (actual time=0.026..2460.535 rows=416466 loops=1)
Total runtime: 29603.159 ms
I've tried index on the "key" column but no improvement.
Is there a way to speed the SELECT up?
Thank you for any suggestions.
--
Miroslav Šulc
Attachments:
miroslav.sulc.vcf (text/x-vcard, 347B) download
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
* Re: SELECT DISTINCT too slow
@ 2006-06-01 13:53 Alvaro Herrera <alvherre@commandprompt.com>
parent: Miroslav Šulc <miroslav.sulc@startnet.cz>
1 sibling, 1 reply; 13+ messages in thread
From: Alvaro Herrera @ 2006-06-01 13:53 UTC (permalink / raw)
To: Miroslav ?ulc <miroslav.sulc@startnet.cz>; +Cc: pgsql-sql
Miroslav ?ulc wrote:
> Hello,
>
> I have a table with cca 400,000 rows. The table contains column "key" of
> varchar(20) type containing 10 distinct values. I want to get out what
> distinct values are present in the column. I use this simple query,
> which is very slow:
>
> SELECT DISTINCT Key FROM MRTPContactValue
You could get the universe of values from the table where this is a
primary key, and use an IN clause (which apparently is more efficient
than an EXISTS in some cases, but try that too) to search for values
that exist in the MRTPContactValue table.
I assume you do have the other table, don't you?
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: SELECT DISTINCT too slow
@ 2006-06-01 14:49 Miroslav Šulc <miroslav.sulc@startnet.cz>
parent: Alvaro Herrera <alvherre@commandprompt.com>
0 siblings, 1 reply; 13+ messages in thread
From: Miroslav Šulc @ 2006-06-01 14:49 UTC (permalink / raw)
To: pgsql-sql
Well, "key" is not primary key from another table. It is just a column
in pair "key" => "value".
The structure of the table is this:
Id (primary key)
MRTPContactId (id of contact from table MRTPContact)
Key (key from pair key => value)
Value (value from pair key => value)
So I want the get the list of keys used in the table.
Miroslav Šulc
Alvaro Herrera napsal(a):
> Miroslav ?ulc wrote:
>
>> Hello,
>>
>> I have a table with cca 400,000 rows. The table contains column "key" of
>> varchar(20) type containing 10 distinct values. I want to get out what
>> distinct values are present in the column. I use this simple query,
>> which is very slow:
>>
>> SELECT DISTINCT Key FROM MRTPContactValue
>>
>
> You could get the universe of values from the table where this is a
> primary key, and use an IN clause (which apparently is more efficient
> than an EXISTS in some cases, but try that too) to search for values
> that exist in the MRTPContactValue table.
>
> I assume you do have the other table, don't you?
>
>
Attachments:
miroslav.sulc.vcf (text/x-vcard, 347B) download
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
* Re: SELECT DISTINCT too slow
@ 2006-06-01 14:53 Alvaro Herrera <alvherre@commandprompt.com>
parent: Miroslav Šulc <miroslav.sulc@startnet.cz>
0 siblings, 2 replies; 13+ messages in thread
From: Alvaro Herrera @ 2006-06-01 14:53 UTC (permalink / raw)
To: Miroslav ?ulc <miroslav.sulc@startnet.cz>; +Cc: pgsql-sql
Miroslav ?ulc wrote:
> Well, "key" is not primary key from another table. It is just a column
> in pair "key" => "value".
> The structure of the table is this:
>
> Id (primary key)
> MRTPContactId (id of contact from table MRTPContact)
> Key (key from pair key => value)
> Value (value from pair key => value)
>
> So I want the get the list of keys used in the table.
The plan you get is the most efficient possible for that query. If you
had a table of possible keys (which should of course be FK of "Key"),
you could get a much faster version :-)
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: SELECT DISTINCT too slow
@ 2006-06-01 15:03 Miroslav Šulc <miroslav.sulc@startnet.cz>
parent: Alvaro Herrera <alvherre@commandprompt.com>
1 sibling, 0 replies; 13+ messages in thread
From: Miroslav Šulc @ 2006-06-01 15:03 UTC (permalink / raw)
To: pgsql-sql
It might be a good solution :-)
Thank you for your help.
Miroslav Šulc
Alvaro Herrera napsal(a):
> Miroslav ?ulc wrote:
>
>> Well, "key" is not primary key from another table. It is just a column
>> in pair "key" => "value".
>> The structure of the table is this:
>>
>> Id (primary key)
>> MRTPContactId (id of contact from table MRTPContact)
>> Key (key from pair key => value)
>> Value (value from pair key => value)
>>
>> So I want the get the list of keys used in the table.
>>
>
> The plan you get is the most efficient possible for that query. If you
> had a table of possible keys (which should of course be FK of "Key"),
> you could get a much faster version :-)
>
>
Attachments:
miroslav.sulc.vcf (text/x-vcard, 347B) download
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
* Re: SELECT DISTINCT too slow
@ 2006-06-01 15:20 Tom Lane <tgl@sss.pgh.pa.us>
parent: Miroslav Šulc <miroslav.sulc@startnet.cz>
1 sibling, 1 reply; 13+ messages in thread
From: Tom Lane @ 2006-06-01 15:20 UTC (permalink / raw)
To: Miroslav Šulc <miroslav.sulc@startnet.cz>; +Cc: pgsql-sql
=?ISO-8859-2?Q?Miroslav_=A9ulc?= <miroslav.sulc@startnet.cz> writes:
> I have a table with cca 400,000 rows. The table contains column "key" of
> varchar(20) type containing 10 distinct values. I want to get out what
> distinct values are present in the column. I use this simple query,
> which is very slow:
> SELECT DISTINCT Key FROM MRTPContactValue
Try
SELECT Key FROM MRTPContactValue GROUP BY Key
The "select distinct" code is a bit old and crufty, GROUP BY is usually
smarter.
regards, tom lane
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: SELECT DISTINCT too slow
@ 2006-06-01 15:39 Miroslav ulc <miroslav.sulc@startnet.cz>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 13+ messages in thread
From: Miroslav ulc @ 2006-06-01 15:39 UTC (permalink / raw)
To: ; +Cc: pgsql-sql
The GROUP BY is really fast :-)
Thank you.
Miroslav ulc
Tom Lane napsal(a):
> Try
> SELECT Key FROM MRTPContactValue GROUP BY Key
>
> The "select distinct" code is a bit old and crufty, GROUP BY is usually
> smarter.
>
> regards, tom lane
>
Attachments:
miroslav.sulc.vcf (text/x-vcard, 347B) download
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
* Re: SELECT DISTINCT too slow
@ 2006-06-01 15:46 Alvaro Herrera <alvherre@commandprompt.com>
parent: Miroslav ulc <miroslav.sulc@startnet.cz>
0 siblings, 1 reply; 13+ messages in thread
From: Alvaro Herrera @ 2006-06-01 15:46 UTC (permalink / raw)
To: Miroslav ?ulc <miroslav.sulc@startnet.cz>; +Cc: pgsql-sql
Miroslav ?ulc wrote:
> The GROUP BY is really fast :-)
Doh! How does it do it?
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: SELECT DISTINCT too slow
@ 2006-06-01 15:46 Greg Stark <gsstark@mit.edu>
parent: Alvaro Herrera <alvherre@commandprompt.com>
1 sibling, 1 reply; 13+ messages in thread
From: Greg Stark @ 2006-06-01 15:46 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@commandprompt.com>; +Cc: Miroslav ?ulc <miroslav.sulc@startnet.cz>; pgsql-sql
Alvaro Herrera <alvherre@commandprompt.com> writes:
> Miroslav ?ulc wrote:
> > Well, "key" is not primary key from another table. It is just a column
> > in pair "key" => "value".
> > The structure of the table is this:
> >
> > Id (primary key)
> > MRTPContactId (id of contact from table MRTPContact)
> > Key (key from pair key => value)
> > Value (value from pair key => value)
> >
> > So I want the get the list of keys used in the table.
>
> The plan you get is the most efficient possible for that query. If you
> had a table of possible keys (which should of course be FK of "Key"),
> you could get a much faster version :-)
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...
--
greg
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: SELECT DISTINCT too slow
@ 2006-06-01 16:06 Miroslav Šulc <miroslav.sulc@startnet.cz>
parent: Greg Stark <gsstark@mit.edu>
0 siblings, 0 replies; 13+ messages in thread
From: Miroslav Šulc @ 2006-06-01 16:06 UTC (permalink / raw)
To: ; +Cc: pgsql-sql
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
Attachments:
miroslav.sulc.vcf (text/x-vcard, 347B) download
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
* Re: SELECT DISTINCT too slow
@ 2006-06-01 18:59 Florian Weimer <fw@deneb.enyo.de>
parent: Alvaro Herrera <alvherre@commandprompt.com>
0 siblings, 0 replies; 13+ messages in thread
From: Florian Weimer @ 2006-06-01 18:59 UTC (permalink / raw)
To: Miroslav ?ulc <miroslav.sulc@startnet.cz>; +Cc: pgsql-sql
* Alvaro Herrera:
> Miroslav ?ulc wrote:
>> The GROUP BY is really fast :-)
>
> Doh! How does it do it?
It uses a hash table and can therefore discard duplicate rows more
quickly (essentially linear time in the number of rows if the number
of different rows is bounded).
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2006-06-01 18:59 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-06-01 16:36 select distinct too slow george young <gry@ll.mit.edu>
2001-06-04 21:45 ` Hans-Jürgen Schönig <hs@cybertec.at>
2006-06-01 13:26 SELECT DISTINCT too slow Miroslav Šulc <miroslav.sulc@startnet.cz>
2006-06-01 13:53 ` Re: SELECT DISTINCT too slow Alvaro Herrera <alvherre@commandprompt.com>
2006-06-01 14:49 ` Re: SELECT DISTINCT too slow Miroslav Šulc <miroslav.sulc@startnet.cz>
2006-06-01 14:53 ` Re: SELECT DISTINCT too slow Alvaro Herrera <alvherre@commandprompt.com>
2006-06-01 15:03 ` Re: SELECT DISTINCT too slow Miroslav Šulc <miroslav.sulc@startnet.cz>
2006-06-01 15:46 ` Re: SELECT DISTINCT too slow Greg Stark <gsstark@mit.edu>
2006-06-01 16:06 ` Re: SELECT DISTINCT too slow Miroslav Šulc <miroslav.sulc@startnet.cz>
2006-06-01 15:20 ` Re: SELECT DISTINCT too slow Tom Lane <tgl@sss.pgh.pa.us>
2006-06-01 15:39 ` Re: SELECT DISTINCT too slow Miroslav ulc <miroslav.sulc@startnet.cz>
2006-06-01 15:46 ` Re: SELECT DISTINCT too slow Alvaro Herrera <alvherre@commandprompt.com>
2006-06-01 18:59 ` Re: SELECT DISTINCT too slow Florian Weimer <fw@deneb.enyo.de>
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