At 06:34 PM 12/2/99 -0800, Tim Perdue wrote:
>In the various versions of Postgres that I've
>used, I've just been amazed at how stupid the
>sorting process is.
>I'm trying to SELECT DISTINCT on a table that is
>60MB:
>
>-rw------- 1 postgres postgres 89799976 Dec 2
>20:27 pg_sorttemp32736.0
>-rw------- 1 postgres postgres 87307680 Dec 2
>20:27 pg_sorttemp32736.1
>-rw------- 1 postgres postgres 84376872 Dec 2
>20:27 pg_sorttemp32736.2
>-rw------- 1 postgres postgres 78645944 Dec 2
>20:27 pg_sorttemp32736.3
>-rw------- 1 postgres postgres 66749412 Dec 2
>20:27 pg_sorttemp32736.4
>-rw------- 1 postgres postgres 71360512 Dec 2
>20:29 pg_sorttemp32736.5
>-rw------- 1 postgres postgres 260677944 Dec 2
>20:28 pg_sorttemp32736.6
>
>
>It uses over 1GB of disk space to do that sort,
>and it would have used a lot more if I hadn't run
>out.
>
>Then it won't fail gracefully, instead of just
>hangs and leaves temp files completely filling up
>the hard drive.
>
>How can it use 1GB of disk to sort a 60MB file?
Because maybe you're doing a really dumb join before you
sort? SQL is full of such "gotchas".
Post your query here, maybe we can make you feel better by
pointing you to Oracle, where you can rant and rave and pay
several thousand dollars to be told the same thing.
Select distinct first executes the query you give it (i.e.
all the joins), sorts the result, then deletes dupes. The
primary reason why select distinct takes a long time is because
the joins are written ... well, stupidly. What can I say that's
more honest than that? I'd be kinder if you weren't so harsh
in your assessment of Postgres.
(In my experience, PostgreSQL usually does what it's told, which is
why I'm being so harsh).
And, of course, you've posed your question stupidly - "my query's
slow, why is Postgres so horrible?" and you haven't bothered posting
your query.
That's sort of like saying "my car won't start" without telling us
if there's actually gas in the tank...
- Don Baccus, Portland OR <dhogaza@pacifier.com> Nature photos, on-line guides, Pacific Northwest Rare Bird Alert
Serviceand other goodies at http://donb.photo.net.