The optimizer is too smart for me - How can I trick it?
От | Peter Vanderborght |
---|---|
Тема | The optimizer is too smart for me - How can I trick it? |
Дата | |
Msg-id | 003d01c8c232$0e3692b0$88c8a8c0@TAATU.local обсуждение исходный текст |
Ответы |
Re: The optimizer is too smart for me - How can I trick it?
("Adam Rich" <adam.r@sbcglobal.net>)
Re: The optimizer is too smart for me - How can I trick it? (Martijn van Oosterhout <kleptog@svana.org>) |
Список | 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
В списке pgsql-general по дате отправления: