Обсуждение: The optimizer is too smart for me - How can I trick it?

Hi,

I've implemented Depesz's running total function
(http://www.depesz.com/index.php/2007/08/17/rownum-anyone-cumulative-sum-in-
one-query/) in my DB, which works great.
Now what I want to do is get the running total for a certain statement and
then do a subselect on that result so to get a non-zero start on a function.

Example:

 select day, registrations, runningsum(cast('myregistrations' as text),
cast(registrations as int4)) as rtotal
 from
 (
    select cast(to_char(dateinscr, 'YYYY-MM-DD') as timestamp) as day,
count(*) as registrations
    from public.people
    where supplierid = 609
       and dateinscr < date_trunc('day', now())
    group by day
    order by day
 ) subQ

This gives me correct output like this:

         day         | registrations | rtotal
---------------------+---------------+--------
 2008-04-14 00:00:00 |             1 |      1
 2008-04-17 00:00:00 |            11 |     12
 2008-04-18 00:00:00 |            24 |     36
 2008-04-19 00:00:00 |            14 |     50
 2008-04-20 00:00:00 |            13 |     63
 2008-04-21 00:00:00 |             6 |     69
 2008-04-22 00:00:00 |             2 |     71
 2008-04-23 00:00:00 |            12 |     83
 2008-04-24 00:00:00 |             5 |     88
 2008-04-25 00:00:00 |            13 |    101
 2008-04-26 00:00:00 |            11 |    112

Then I want to subselect on this and do:

select *
from
(
 select day, registrations, runningsum(cast('myregistrations' as text),
cast(registrations as int4)) as rtotal
 from
 (
    select cast(to_char(dateinscr, 'YYYY-MM-DD') as timestamp) as day,
count(*) as registrations
    from public.people
    where supplierid = 609
       and dateinscr < date_trunc('day', now())
    group by day
    order by day
 ) subQ
) subq2
Where day > '2008-04-24'

And I would expect to get
         day         | registrations | rtotal
---------------------+---------------+--------
 2008-04-25 00:00:00 |            13 |    101
 2008-04-26 00:00:00 |            11 |    112

Instead, the optimizer sees what I'm trying to do, moves the where clause
inside the subquery and my output becomes

         day         | registrations | rtotal
---------------------+---------------+--------
 2008-04-25 00:00:00 |            13 |     13
 2008-04-26 00:00:00 |            11 |     24

What can I do to tell the optimizer to keep its hands off my query or at
least get it to not optimize?


Thanks,
Peter

GREAT!!!
Just "offset 0" in the subquery did the trick!

I had already been playing with a stored proc, but that's way more messy
than this.

Thanks a million!
Peter

-----Original Message-----
From: Adam Rich [mailto:adam.r@sbcglobal.net]
Sent: 30 May 2008 17:14
To: peter.vanderborght@taatu.com; pgsql-general@postgresql.org
Subject: RE: [GENERAL] The optimizer is too smart for me - How can I trick
it?


> I've implemented Depesz's running total function
> (http://www.depesz.com/index.php/2007/08/17/rownum-anyone-cumulative-
> sum-in-
> one-query/) in my DB, which works great.
> Now what I want to do is get the running total for a certain statement
> and then do a subselect on that result so to get a non-zero start on a
> function.
>
> Instead, the optimizer sees what I'm trying to do, moves the where
> clause inside the subquery and my output becomes
>
> What can I do to tell the optimizer to keep its hands off my query or
> at least get it to not optimize?
>

I think if you add a LIMIT/OFFSET clause to your subquery, the planner
will leave it alone.

> I've implemented Depesz's running total function
> (http://www.depesz.com/index.php/2007/08/17/rownum-anyone-cumulative-
> sum-in-
> one-query/) in my DB, which works great.
> Now what I want to do is get the running total for a certain statement
> and
> then do a subselect on that result so to get a non-zero start on a
> function.
>
> Instead, the optimizer sees what I'm trying to do, moves the where
> clause
> inside the subquery and my output becomes
>
> What can I do to tell the optimizer to keep its hands off my query or
> at
> least get it to not optimize?
>

I think if you add a LIMIT/OFFSET clause to your subquery, the planner
will leave it alone.

On Fri, May 30, 2008 at 10:49:21AM +0200, Peter Vanderborght wrote:
> What can I do to tell the optimizer to keep its hands off my query or at
> least get it to not optimize?

The usual trick is you put an OFFSET 0 in the subquery, which prevents
the optimiser from pulling it up.

Have a nice day.
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.