From: george young <gry@ll.mit.edu>
To: pgsql-sql@postgresql.org
Subject: select distinct too slow
Date: Fri, 1 Jun 2001 12:36:41 -0400
Message-ID: <20010601123641.4c0d96c0.gry@ll.mit.edu> (raw)
[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]
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: gry@ll.mit.edu
Subject: Re: select distinct too slow
In-Reply-To: <20010601123641.4c0d96c0.gry@ll.mit.edu>
* 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