Thread: 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.