Re: [HACKERS] putting CHECK_FOR_INTERRUPTS in qsort_comparetup() - Mailing list pgsql-patches

From Tom Lane
Subject Re: [HACKERS] putting CHECK_FOR_INTERRUPTS in qsort_comparetup()
Date
Msg-id 19745.1154092753@sss.pgh.pa.us
Whole thread Raw
In response to Re: [HACKERS] putting CHECK_FOR_INTERRUPTS in qsort_comparetup()  ("Charles Duffy" <charles.duffy@gmail.com>)
Responses Re: [HACKERS] putting CHECK_FOR_INTERRUPTS in  (Bruce Momjian <bruce@momjian.us>)
List pgsql-patches
"Charles Duffy" <charles.duffy@gmail.com> 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

pgsql-patches by date:

Previous
From: Chris Mair
Date:
Subject: selecting large result sets in psql using cursors
Next
From: Bruce Momjian
Date:
Subject: Re: pgstattuple extension for indexes