From: Ben <midfield@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: equivalent queries lead to different query plans for self-joins with group by?
Date: Thu, 11 Nov 2010 15:56:31 -0800
Message-ID: <5E705CB3-DF3D-4804-8F87-984D2041C9AC@gmail.com> (raw)
In-Reply-To: <23147.1289515069@sss.pgh.pa.us>
References: <5D061DAC-31E2-4C47-AE3B-7799118C57D3@gmail.com>
<23147.1289515069@sss.pgh.pa.us>
appreciate the instant response.
> Well, arguably it's not doing the right thing either way --- you'd sort
> of like the inequalities to get pushed down into both of the join
> inputs, not just one of them. PG doesn't make that deduction though;
> it can make such inferences for equalities, but inequalities are not
> optimized as much.
in my work i have replaced the query with a sql function + window :
create or replace function bar(timestamp, timestamp) returns setof foo
language 'sql' as $$
select ts,
id,
val -
(avg(val) over (partition by ts)) as val
from foo
where ts > $1
and ts < $2
$$;
i was forced to use a sql function as opposed to a view because the query planner was unable to push down restrictions on ts inside the view subquery, which i've manually done in the function. indeed,
explain select ts, id, val - (avg(val) over (partition by ts)) as val from foo where ts > '2009-10-20' and ts < '2009-10-21';
and
explain select * from (select ts, id, val - (avg(val) over (partition by ts)) as val from foo) as f where ts > '2009-10-20' and ts < '2009-10-21';
give different answers, despite being equivalent, but i understand it is hard to push things into subqueries in general. in this case it is only legal because we partition by ts.
thanks again for the explanations!
best, ben
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: midfield@gmail.com, tgl@sss.pgh.pa.us
Subject: Re: equivalent queries lead to different query plans for self-joins with group by?
In-Reply-To: <5E705CB3-DF3D-4804-8F87-984D2041C9AC@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox