Re: order of nested loop

On Tue, Jun 17, 2003 at 11:53:05AM -0400, Jean-Luc Lachance wrote:
> Tom Lane wrote:
> >
> > Jean-Luc Lachance <jllachan(at)nsd(dot)ca> writes:
> > > I am currious. Why perform a sort first? Would it not be more
> > > efficient to create an in memory index on the fly?
> >
> > Why would you think that? Creating an index involves a sort ...
> > there's no free lunch there that I can see.
> >
> > regards, tom lane
>
>
> There is only a small difference, but distinct implies unique index
> which mean there is no need to sort duplicate records.

Also (and not specific to this example), an index doesn't need to sort
entire tuples. If the tuples are wide enough, it would be faster to
build an index, then use that index to access the tuples, especially if
you're going to read through the data more than once (the tuples might
have to be hopelessly large to make a single scan effective).

On a related note; since temporary tables are only visible to a single
connection, do they have full MVCC info in them, or can it be bypassed?
If it's not there you'd probably need some other means to allow for
transactions, but the performance gain might be well worth it.
--
Jim C. Nasby (aka Decibel!) jim(at)nasby(dot)net
Member: Triangle Fraternity, Sports Car Club of America
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"