Thread: optimizer cost calculation problem
Kenji Sugita has identified a problem with cost_sort() in costsize.c.
In the following code fragment, sortmembytes is defined as long. So
double nruns = nbytes / (sortmembytes * 2);
may cause an integer overflow if sortmembytes exceeds 2^30, which in
turn make optimizer to produce wrong query plan(this actually happned
in a large PostgreSQL installation which has tons of memory). Here is a
proposed fix against current:
*** optimizer/path/costsize.c 16 Feb 2003 02:30:38 -0000 1.107
--- optimizer/path/costsize.c 31 Mar 2003 22:25:06 -0000
***************
*** 490,496 **** Cost startup_cost = input_cost; Cost run_cost = 0; double nbytes =
relation_byte_size(tuples,width);
! long sortmembytes = SortMem * 1024L; if (!enable_sort) startup_cost += disable_cost;
--- 490,496 ---- Cost startup_cost = input_cost; Cost run_cost = 0; double nbytes =
relation_byte_size(tuples,width);
! double sortmembytes = SortMem * 1024.0; if (!enable_sort) startup_cost += disable_cost;
Tatsuo Ishii <t-ishii@sra.co.jp> writes:
> Kenji Sugita has identified a problem with cost_sort() in costsize.c.
> In the following code fragment, sortmembytes is defined as long. So
> double nruns = nbytes / (sortmembytes * 2);
> may cause an integer overflow if sortmembytes exceeds 2^30, which in
> turn make optimizer to produce wrong query plan(this actually happned
> in a large PostgreSQL installation which has tons of memory).
I find it really really hard to believe that it's wise to run with
sort_mem exceeding 2 gig ;-). Does that installation have so much
RAM that it can afford to run multiple many-Gb sorts concurrently?
This is far from being the only place that multiplies SortMem by 1024.
My inclination is that a safer fix is to alter guc.c's entry for
SortMem to establish a maximum value of INT_MAX/1024 for the variable.
Probably some of the other GUC variables like shared_buffers ought to
have overflow-related maxima established, too.
regards, tom lane
> Tatsuo Ishii <t-ishii@sra.co.jp> writes: > > Kenji Sugita has identified a problem with cost_sort() in costsize.c. > > In the following code fragment, sortmembytes is defined as long. So > > double nruns = nbytes / (sortmembytes * 2); > > may cause an integer overflow if sortmembytes exceeds 2^30, which in > > turn make optimizer to produce wrong query plan(this actually happned > > in a large PostgreSQL installation which has tons of memory). > > I find it really really hard to believe that it's wise to run with > sort_mem exceeding 2 gig ;-). Does that installation have so much > RAM that it can afford to run multiple many-Gb sorts concurrently? The process is assigned 1 gig sort mem to speed up a batch job by uisng backend-process-only sort mem setting, and they do not modify postgresql.conf for ordinaly user. BTW it does not 2 gig, but 1 gig (remember that we do sortmembytes * 2) . > This is far from being the only place that multiplies SortMem by 1024. > My inclination is that a safer fix is to alter guc.c's entry for > SortMem to establish a maximum value of INT_MAX/1024 for the variable. > > Probably some of the other GUC variables like shared_buffers ought to > have overflow-related maxima established, too. > > regards, tom lane
Tatsuo Ishii <t-ishii@sra.co.jp> writes:
> BTW it does not 2 gig, but 1 gig (remember that we do sortmembytes *
> 2) .
Good point. Probably that particular calculation should be
"sortmembytes * 2.0" to force it to double before it can overflow.
But I still think we'd better limit SortMem so that the basic
SortMem*1024 calculation can't overflow (or even come close to overflow,
likely).
regards, tom lane
Tom wrote: > >I find it really really hard to believe that it's wise to run with >sort_mem exceeding 2 gig ;-). Does that installation have so much >RAM that it can afford to run multiple many-Gb sorts concurrently? I don't do 2 gig... but I found 0.3 gig helped on a not-too-large system. In a nightly load of a datawarehousing application I have a number of places where I do: set sort_mem=300000; create table new_whatevers as select distinct whatever from import_table; set sort_mem=10000; and set sort_mem=100000; select count(*),b from a group by b; set sort_mem=10000; when the optimizer likes hash aggregates. It significantly (8 hours -> 6 hours) reduces the nightly processing of my log-file analysis database. If my modest system benefited from 1/3 gig, it wouldn't surprise me if a large system benefits from 2 gig. If more info's useful, I'd be happy to provide some. Ron
On Mon, 31 Mar 2003, Tom Lane wrote: > Tatsuo Ishii <t-ishii@sra.co.jp> writes: > > BTW it does not 2 gig, but 1 gig (remember that we do sortmembytes * > > 2) . > > Good point. Probably that particular calculation should be > "sortmembytes * 2.0" to force it to double before it can overflow. > But I still think we'd better limit SortMem so that the basic > SortMem*1024 calculation can't overflow (or even come close to overflow, > likely). This isn't really an issue for 64 bit hardware is it?
"scott.marlowe" <scott.marlowe@ihs.com> writes:
> This isn't really an issue for 64 bit hardware is it?
Is "int" 64 bits on such a machine? The ones I've dealt with chose to
set int = 32bits, long = 64bits. If they don't do that then they have
a problem with not having any native C 32bit type (and promoting short
int up to 32 bits just moves the problem...)
At some point we should probably try to regularize the backend code so
that all memory-size-related calcs are consistently done in size_t or
ssize_t arithmetic; but we're a long way from that at present. For now
I think it's prudent to keep sort_mem small enough to avoid overflow in
int32 arithmetic.
regards, tom lane
On Tue, Apr 01, 2003 at 11:24:01AM -0500, Tom Lane wrote: > "scott.marlowe" <scott.marlowe@ihs.com> writes: > > This isn't really an issue for 64 bit hardware is it? > > Is "int" 64 bits on such a machine? The ones I've dealt with chose to It was for the regression tests we did recently on a 64-bit gcc-3.2-compiled Solaris 8 binary. Piles of regression failures because int was not 32 bits. A -- ---- Andrew Sullivan 204-4141 Yonge Street Liberty RMS Toronto, Ontario Canada <andrew@libertyrms.info> M2P 2A8 +1 416 646 3304 x110