On this thread http://www.postgresql.org/message-id/52c6f712.6040...@student.kit.edu there was some discussion around allowing push downs of quals that happen to be in every window clause of the sub query. I've quickly put together a patch which does this (see attached)
I'm posting this just mainly to let Thomas know that I'm working on it, per
his request on the other thread.
The patch seems to work with all my test cases, and I've not quite gotten
around to thinking of any more good cases to throw at it.
Oh and I know that my function var_exists_in_all_query_partition_by_clauses
has no business in allpaths.c, I'll move it out as soon as I find a better
home for it.
Here's my test case:
drop table if exists winagg;
create table winagg (
id serial not null primary key,
partid int not null
);
insert into winagg (partid) select x.x % 100000 from
generate_series(1,2000000) x(x);
create index winagg_partid_idx on winagg(partid);
-- Should push: this should push WHERE partid=1 to the inner query as
partid is in the only parition by clause in the query.
explain analyze select partid,n from (select partid,count(*) over
(partition by partid) n from winagg) winagg where partid=1;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
WindowAgg (cost=4.58..82.23 rows=20 width=4) (actual time=0.196..0.207
rows=20 loops=1)
-> Bitmap Heap Scan on winagg (cost=4.58..81.98 rows=20 width=4)
(actual time=0.102..0.170 rows=20 loops=1)
Recheck Cond: (partid = 1)
Heap Blocks: exact=20
-> Bitmap Index Scan on winagg_partid_idx (cost=0.00..4.58
rows=20 width=0) (actual time=0.084..0.084 rows=20 loops=1)
Index Cond: (partid = 1)
Planning time: 0.208 ms
Total runtime: 0.276 ms
(8 rows)
-- Should not push: Added a +0 to partition by clause.
explain analyze select partid,n from (select partid,count(*) over
(partition by partid + 0) n from winagg) winagg where partid=1;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
Subquery Scan on winagg (cost=265511.19..330511.19 rows=20 width=12)
(actual time=2146.642..4257.267 rows=20 loops=1)
Filter: (winagg.partid = 1)
Rows Removed by Filter: 1999980
-> WindowAgg (cost=265511.19..305511.19 rows=2000000 width=4) (actual
time=2146.614..4099.169 rows=2000000 loops=1)
-> Sort (cost=265511.19..270511.19 rows=2000000 width=4) (actual
time=2146.587..2994.993 rows=2000000 loops=1)
Sort Key: ((winagg_1.partid + 0))
Sort Method: external merge Disk: 35136kB
-> Seq Scan on winagg winagg_1 (cost=0.00..28850.00
rows=2000000 width=4) (actual time=0.025..418.306 rows=2000000 loops=1)
Planning time: 0.249 ms
Total runtime: 4263.933 ms
(10 rows)
-- Should not push: Add a window clause (which is not used) that has a
partition by clause that does not have partid
explain analyze select partid,n from (select partid,count(*) over
(partition by partid) n from winagg window stopPushDown as (partition by
id)) winagg where partid=1;
-- Should not push: 1 window clause does not have partid
explain analyze select partid,n from (select partid,count(*) over
(partition by partid) n from winagg window stopPushDown as (order id))
winagg where partid=1;
-- Should not push: 1 window clause does not have partid
explain analyze select partid,n from (select partid,count(*) over
(partition by partid) n from winagg window stopPushDown as ()) winagg where
partid=1;
As of now the patch is a couple of hours old, I've not even bothered to run
the regression tests yet, let alone add any new ones.
Comments are welcome...
Regards
David Rowley
wfunc_pushdown_paritionby_v0.1.patch
Description: Binary data
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
