Skip site navigation (1) Skip section navigation (2)

Re: qsort again (was Re: [PERFORM] Strange Create

From: Martijn van Oosterhout <kleptog(at)svana(dot)org>
To: Ron <rjpeace(at)earthlink(dot)net>
Cc: Markus Schaber <schabi(at)logix-tt(dot)com>, pgsql-performance(at)postgresql(dot)org,pgsql-hackers(at)postgresql(dot)org
Subject: Re: qsort again (was Re: [PERFORM] Strange Create
Date: 2006-02-17 15:53:58
Message-ID: 20060217155358.GD9254@svana.org (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-performance
On Fri, Feb 17, 2006 at 08:23:40AM -0500, Ron wrote:
> >For this mapping, you need a full table sort.
> One physical IO pass should be all that's needed.  However, let's 
> pretend you are correct and that we do need to sort the table to get 
> the key mapping.  Even so, we would only need to do it =once= and 
> then we would be able to use and incrementally update the results 
> forever afterward.  Even under this assumption, one external sort to 
> save all subsequent such sorts seems well worth it.
> 
> IOW, even if I'm wrong about the initial cost to do this; it is still 
> worth doing ;-)

I think you're talking about something different here. You're thinking
of having the whole table sorted and when you add a new value you add
it in such a way to keep it sorted. The problem is, what do you sort it
by? If you've sorted the table by col1, then when the user does ORDER
BY col2 it's useless.

Indeed, this is what btrees do, you store the order of the table
seperate from the data. And you can store multiple orders. But even
then, when someone does ORDER BY lower(col1), it's still useless.

And you're right, we still need to do the single mass sort in the
beginning, which is precisely what we're trying to optimise here.

> ??? You do not need to resort already ordered data to insert a new 
> element into it such that the data stays ordered!  Once we have done 
> the key ordering operation once, we should not ever need to do it 
> again on the original data.  Else most sorting algorithms wouldn't work ;-)

We already do this with btree indexes. I'm not sure what you are
proposing that improves on that.

Have a nice day,
-- 
Martijn van Oosterhout   <kleptog(at)svana(dot)org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

In response to

Responses

pgsql-performance by date

Next:From: Scott LambDate: 2006-02-17 16:18:41
Subject: Re: qsort again (was Re: [PERFORM] Strange Create Index
Previous:From: martial.bizelDate: 2006-02-17 15:18:39
Subject: Re: split partitioned table across several postgres servers

pgsql-hackers by date

Next:From: Scott LambDate: 2006-02-17 16:18:41
Subject: Re: qsort again (was Re: [PERFORM] Strange Create Index
Previous:From: Bruce MomjianDate: 2006-02-17 15:40:40
Subject: Updated email signature

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group