The optimizer is too smart for me - How can I trick it?

From: "Peter Vanderborght" <peter(dot)vanderborght(at)taatu(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: The optimizer is too smart for me - How can I trick it?
Date: 2008-05-30 08:49:21
Message-ID: 003d01c8c232$0e3692b0$88c8a8c0@TAATU.local
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David Harel 2008-05-30 09:25:07 Collate problem when using ORDER BY
Previous Message User Map 2008-05-30 07:55:57 Fw: Distance w.r.t scalebar and postgre distance query