"Charles Duffy" <[EMAIL PROTECTED]> writes:
> ... For the 'long' data, the compare moves on rightward until it
> encounters 'flato', which is a TEXT column with an average length of
> 7.5k characters (with some rows up to 400k). The first 6 columns are
> mostly INTEGER, so compares on them are relatively inexpensive. All
> the expensive compares on 'flato' account for the disproportionate
> difference in sort times, relative to the number of rows in each set.
Yeah, and it's not just that it's text either. At those sizes, all
the values will be toasted, which means each compare is paying the
price of fetching multiple rows from the toast table. And decompressing
them too, no doubt. These costs are most likely swamping the actual
strcoll() (not that that's not bad enough compared to int4cmp).
We could probably tweak the sorting code to forcibly detoast sort keys
before beginning the sort, but I'm not entirely convinced that would be
a win: reading and writing enormous sort keys won't be cheap either.
Meanwhile, for a cheap solution: do you really need to sort on flato
at all? Maybe sorting on substr(flato,1,100) would be good enough?
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
