This message was sent from Geocrawler.com by "Tim Perdue" <tim@perdue.net>
Be sure to reply to that address.

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?

Tim


Geocrawler.com - The Knowledge Archive

Thu, 2 Dec 1999 18:34:19 -0800
"Tim Perdue" <archiver@db.geocrawler.com>
Brain-Dead Sort Algorithm???
hi... 

> 
> I'm trying to SELECT DISTINCT on a table that is
> 60MB:

what is your exact select statement, where are you doing this from (psql,
libpq, PHP etc), what OS, what version of pgsql are you using.... etc... 

-- 
Aaron J. Seigo
Sys Admin

Thu, 2 Dec 1999 19:43:06 -0700
"Aaron J. Seigo" <aaron@gtv.ca>
Re: [HACKERS] Brain-Dead Sort Algorithm???
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 Service and other goodies at
  http://donb.photo.net.

Thu, 02 Dec 1999 20:27:13 -0800
Don Baccus <dhogaza@pacifier.com>
Re: [HACKERS] Brain-Dead Sort Algorithm???