From: | Alfred Perlstein <bright(at)wintelcom(dot)net> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Unable to get acceptable performance from EXCEPT |
Date: | 2000-05-10 22:35:12 |
Message-ID: | 20000510153511.N28180@fw.wintelcom.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
=# select count(*) from ref_old;
count
-------
10595
(1 row)
=# select count(*) from ref_new;
count
-------
22997
(1 row)
=# select ref_id from ref_old except select ref_id from ref_new;
Takes over 10 minutes, probably closer to half an hour.
I've also tried using 'NOT IN ( select ref_id from ref_new )'
ref_id is an int4, this is on Postgresql 7.0.
This confuses me because the way I'd plan to execute this query would
be something like this: (pseudo code)
result retval;
sort(ref_old);
sort(ref_new);
i = k = 0;
while (i < count(ref_old)) {
while(ref_old[i] > ref_new[k])
k++;
while(ref_old[i] == ref_new[k])
i++;
while(ref_old[i] < ref_new[k])
store(&retval, ref_old[i++]);
}
return (retval);
I can't imagine this algorithm would take over 10 minutes on my
hardware. Can anyone shed some light on what's going on here?
Is there a way to formulate my SQL to get Postgresql to follow
this algorithm?
thanks,
--
-Alfred Perlstein - [bright(at)wintelcom(dot)net|alfred(at)freebsd(dot)org]
"I have the heart of a child; I keep it in a jar on my desk."
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2000-05-10 22:38:49 | Re: Unable to get acceptable performance from EXCEPT |
Previous Message | Bruce Momjian | 2000-05-10 22:30:17 | Re: setproctitle() no longer used? |