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

Re: [HACKERS] putting CHECK_FOR_INTERRUPTS in qsort_comparetup()

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Charles Duffy" <charles(dot)duffy(at)gmail(dot)com>
Cc: "Peter Eisentraut" <peter_e(at)gmx(dot)net>, pgsql-hackers(at)postgresql(dot)org, "Qingqing Zhou" <zhouqq(at)cs(dot)toronto(dot)edu>, pgsql-patches(at)postgresql(dot)org
Subject: Re: [HACKERS] putting CHECK_FOR_INTERRUPTS in qsort_comparetup()
Date: 2006-07-28 13:19:13
Message-ID: 19745.1154092753@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-patches
"Charles Duffy" <charles(dot)duffy(at)gmail(dot)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

In response to

Responses

pgsql-hackers by date

Next:From: Bruce MomjianDate: 2006-07-28 13:31:02
Subject: Re: pgstattuple extension for indexes
Previous:From: Andrew DunstanDate: 2006-07-28 11:58:52
Subject: Re: [Pgbuildfarm-members] [Fwd: RE: Build farm on Windows]

pgsql-patches by date

Next:From: Bruce MomjianDate: 2006-07-28 13:31:02
Subject: Re: pgstattuple extension for indexes
Previous:From: korryd@enterprisedb.comDate: 2006-07-28 09:44:19
Subject: PL instrumentation plugin support (i.e. PL/pgSQL debuggerinfrastructure)

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