Lists: | pgsql-general |
---|
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 |
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
From: | "Adam Rich" <adam(dot)r(at)sbcglobal(dot)net> |
---|---|
To: | <peter(dot)vanderborght(at)taatu(dot)com>, <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: The optimizer is too smart for me - How can I trick it? |
Date: | 2008-05-30 15:13:49 |
Message-ID: | 026301c8c267$c3ecb870$4bc62950$@r@sbcglobal.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
> 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.
From: | "Peter Vanderborght" <peter(dot)vanderborght(at)taatu(dot)com> |
---|---|
To: | "'Adam Rich'" <adam(dot)r(at)sbcglobal(dot)net>, <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: The optimizer is too smart for me - How can I trick it? |
Date: | 2008-05-30 15:28:41 |
Message-ID: | 007901c8c269$d7a5c8a0$88c8a8c0@TAATU.local |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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(dot)r(at)sbcglobal(dot)net]
Sent: 30 May 2008 17:14
To: peter(dot)vanderborght(at)taatu(dot)com; pgsql-general(at)postgresql(dot)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.
From: | Martijn van Oosterhout <kleptog(at)svana(dot)org> |
---|---|
To: | Peter Vanderborght <peter(dot)vanderborght(at)taatu(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: The optimizer is too smart for me - How can I trick it? |
Date: | 2008-05-30 16:30:21 |
Message-ID: | 20080530163021.GC18339@svana.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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(at)svana(dot)org> http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.