thanks.. I will try creating that function. :)
- Greg
-----Original Message-----
From: Tom Lane [mailto:[EMAIL PROTECTED]]
Sent: Friday, May 12, 2000 8:24 AM
To: Gregory Krasnow
Cc: Bruce Momjian; [EMAIL PROTECTED]
Subject: Re: [GENERAL] backend running out of memory in v7.0
Gregory Krasnow <[EMAIL PROTECTED]> writes:
> typical query produced from this product (I didn't write the piece that
> creates the queries, and I know that the queries could definitely be
> optimized):
> SELECT cms_users.usr_id
> FROM cms_usr_prof, cms_usr_incenvalue, cms_opt_in, cms_users,
> cms_usr_prodvalue
> WHERE (((cms_usr_prof.cluster_id2=2) OR (cms_usr_prof.cluster_id2=7))
> OR ((cms_usr_incenvalue.incenvalue_id=283 AND cms_usr_prodvalue.num_sol >
> 0) OR (cms_usr_incenvalue.incenvalue_id=283 AND cms_usr_prodvalue.num_resp
>
> 0))
> OR (cms_usr_prof.last_visit_dt< (now() - 3)::datetime AND
cms_usr_prof.last_visit_dt> = (now() - 4)::datetime)
> OR (cms_opt_in.optin_creation_date>= (now() - 14)::datetime)
> OR (cms_usr_prof.sum_resp_time/cms_usr_prof.total_unq_resp >= 2 AND
> cms_usr_prof.sum_resp_time/cms_usr_prof.total_unq_resp < 3 AND
cms_usr_prof.total_unq_resp> 0)
> OR (cms_users.usr_age>=21 AND cms_users.usr_age<=25)
> OR ((cms_users.usr_bmonth=1) OR (cms_users.usr_bmonth=2) OR
> (cms_users.usr_bmonth=3) OR (cms_users.usr_bmonth=4) OR
> [ etc etc ]
OK, I can well believe that this would've thrown the 6.5 planner for a
loop ;-). But 7.0's planner should cope. Try running this through
EXPLAIN and see if you get a query plan back (in a reasonable amount
of time and memory usage). You would not have under 6.5, I think.
Assuming that you do, that leaves us with the expression memory leakage
theory, and I see the cause right there: the three expressions like
(now() - 3)::datetime. Since datetime is a pass-by-reference type,
you chew up sizeof(datetime) every time this is evaluated. What's
worse, it'll be evaluated again for every tuple that's processed,
because the system doesn't think it can constant-fold an expression
involving now(). Process enough tuples, and you run out of memory
before the system gets around to reclaiming the temporary space.
The expressions in
> OR (cms_usr_prof.sum_resp_time/cms_usr_prof.total_unq_resp >= 2 AND
> cms_usr_prof.sum_resp_time/cms_usr_prof.total_unq_resp < 3 AND
might also be causing a problem depending on what datatype is involved.
It'd be safe enough if these fields are all int.
If you are not in a position to modify the app generating the query at
all, then I think you are kind of stuck until 7.1. You could maybe
kluge it to work by marking now() and timestamp_mi_span() as cacheable
but that could have bad consequences for other queries.
If you can modify the queries, then you could eliminate the leakage
from re-evaluation of the datetime expressions by creating a cacheable
user-defined function that produces the right result. I posted a
detailed example a few days ago in one pgsql list or another; check
the archives. Basically you want to define "ndaysago(int) returns
timestamp" so that the optimizer can't see that there are any
uncacheable functions involved; then it'll evaluate the subexpressions
just once at query start.
regards, tom lane
