Don't know about Oracle, but select-distinct in MSSQL2K will indeed throw
away duplicates, which chops the CPU time. Very easy to see in the graphic
query plan, both in terms of CPU and the number of rows retrieved from a
single-node or nested-loop subtree. Definitely a worthwhile optimization.
"Tom Lane" <tgl@sss.pgh.pa.us> wrote in message
news:22514.1087578111@sss.pgh.pa.us...
> Stephen Frost <sfrost@snowman.net> writes:
> > * Tom Lane (tgl@sss.pgh.pa.us) wrote:
> >> [... thinks for awhile ...] It seems possible that they may use sort
> >> code that knows it is performing a DISTINCT operation and discards
> >> duplicates on sight. Given that there are only 534 distinct values,
> >> the sort would easily stay in memory if that were happening.
>
> > Could this optimization be added to PostgreSQL? It sounds like a very
> > reasonable thing to do.
>
> That's what I was wondering about too. But first I'd like to get
> some kind of reading on how effective it would be. If someone can
> demonstrate that Oracle can do sort-and-drop-dups a lot faster than
> it can do a straight sort of the same amount of input data, that
> would be a strong indication that it's worth doing. At this point
> we don't know if that's the source of their win or not.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>