aggregate doesn't push down

[harris-chiu]

I have run the test in sql/clickhousedb_fdw.sql

It appears that the aggregate doesn't push down as expected

EXPLAIN (VERBOSE, COSTS OFF) select count(*) from ft1;
QUERY PLAN

Aggregate
Output: count(*)
-> Foreign Scan on public.ft1
Remote SQL: SELECT NULL FROM regression.t1
(4 rows)

[harris-chiu]

More info is that the foreign scan cost keeps having zero. Maybe this costs the aggregates doesn’t push down ?

[harris-chiu]

More info:
I have tested the setup in the readme page and reverted some commit.
Supposingly, this statement should trigger predicate pushdown,
postgres=# EXPLAIN VERBOSE SELECT count(bbl) FROM tax_bills_nyc;
QUERY PLAN

Foreign Scan (cost=1.00..-1.00 rows=1000 width=8)
Output: (count(bbl))
Relations: Aggregate on (tax_bills_nyc)
Remote SQL: SELECT count(bbl) FROM test_database.tax_bills_nyc
(4 rows)

but after this commit
postgres=# EXPLAIN VERBOSE SELECT count(bbl) FROM tax_bills_nyc;
QUERY PLAN

Aggregate (cost=0.00..0.01 rows=1 width=8)
Output: count(bbl)
-> Foreign Scan on public.tax_bills_nyc (cost=0.00..0.00 rows=0 width=8)
Output: bbl, owner_name, address, tax_class, tax_rate, emv, tbea, bav, tba, property_tax, condonumber, condo, insertion_date
Remote SQL: SELECT bbl FROM test_database.tax_bills_nyc
(5 rows)

16d79c0

It looks like this commit is causing the issue

[ethanryl]

I have the same problem that my query cannot be pushed down.

[ethanryl]

EXPLAIN VERBOSE SELECT count(bbl) FROM tax_bills_nyc;
QUERY PLAN

Aggregate (cost=0.00..0.01 rows=1 width=8)
Output: count(bbl)
-> Foreign Scan on public.tax_bills_nyc (cost=0.00..0.00 rows=0 width=8)
Output: bbl, owner_name, address, tax_class, tax_rate, emv, tbea, bav, tba, property_tax, condonumber, condo, insertion_date
Remote SQL: SELECT bbl FROM test_database.tax_bills_nyc
(5 rows)

[ibrarahmad]

Try "EXPLAIN VERBOSE SELECT count(*) FROM tax_bills_nyc;"

…

On Tue, Jun 11, 2019 at 2:24 PM ethanryl ***@***.***> wrote: EXPLAIN VERBOSE SELECT count(bbl) FROM tax_bills_nyc; QUERY PLAN Aggregate (cost=0.00..0.01 rows=1 width=8) Output: count(bbl) -> Foreign Scan on public.tax_bills_nyc (cost=0.00..0.00 rows=0 width=8) Output: bbl, owner_name, address, tax_class, tax_rate, emv, tbea, bav, tba, property_tax, condonumber, condo, insertion_date Remote SQL: SELECT bbl FROM test_database.tax_bills_nyc (5 rows) — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <#16?email_source=notifications&email_token=AA3NF4XSW2EZQVMXKJP23DLPZ5VLLA5CNFSM4HSZO2SKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGODXMQIXI#issuecomment-500761693>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AA3NF4QRIHX3IFVJRGZQLOLPZ5VLLANCNFSM4HSZO2SA> .

-- Ibrar Ahmed

[ethanryl]

EXPLAIN VERBOSE SELECT count(*) FROM tax_bills_nyc;
QUERY PLAN

Aggregate (cost=0.00..0.01 rows=1 width=8)
Output: count(*)
-> Foreign Scan on public.tax_bills_nyc (cost=0.00..0.00 rows=0 width=0)
Output: bbl, owner_name, address, tax_class, tax_rate, emv, tbea, bav, tba, property_tax, condonumber, condo, insertion_date
Remote SQL: SELECT NULL FROM test_database.tax_bills_nyc

[ibrarahmad]

That's is strange, I need to look at that because it works on my end.

…

On Tue, Jun 11, 2019 at 5:37 PM ethanryl ***@***.***> wrote: EXPLAIN VERBOSE SELECT count(*) FROM tax_bills_nyc; QUERY PLAN Aggregate (cost=0.00..0.01 rows=1 width=8) Output: count(*) -> Foreign Scan on public.tax_bills_nyc (cost=0.00..0.00 rows=0 width=0) Output: bbl, owner_name, address, tax_class, tax_rate, emv, tbea, bav, tba, property_tax, condonumber, condo, insertion_date Remote SQL: SELECT NULL FROM test_database.tax_bills_nyc — You are receiving this because you commented. Reply to this email directly, view it on GitHub <#16?email_source=notifications&email_token=AA3NF4XZGEN3F7VVOLDQIZDPZ6MBXA5CNFSM4HSZO2SKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGODXM7BNA#issuecomment-500822196>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AA3NF4WXJQF4NDNQHHUMSWTPZ6MBXANCNFSM4HSZO2SA> .

-- Ibrar Ahmed

[ethanryl]

I believe you can beat it,Please let me know if there is any new progress. thanks for you.

…

------------------ 原始邮件 ------------------ 发件人: "Ibrar Ahmed"<[email protected]>; 发送时间: 2019年6月11日(星期二) 晚上8:51 收件人: "Percona-Lab/clickhousedb_fdw"<[email protected]>; 抄送: "下一个明天"<[email protected]>;"Comment"<[email protected]>; 主题: Re: [Percona-Lab/clickhousedb_fdw] aggregate doesn't push down (#16) That's is strange, I need to look at that because it works on my end. On Tue, Jun 11, 2019 at 5:37 PM ethanryl <[email protected]> wrote: EXPLAIN VERBOSE SELECT count(*) FROM tax_bills_nyc; QUERY PLAN Aggregate (cost=0.00..0.01 rows=1 width=8) Output: count(*) -> Foreign Scan on public.tax_bills_nyc (cost=0.00..0.00 rows=0 width=0) Output: bbl, owner_name, address, tax_class, tax_rate, emv, tbea, bav, tba, property_tax, condonumber, condo, insertion_date Remote SQL: SELECT NULL FROM test_database.tax_bills_nyc — You are receiving this because you commented. Reply to this email directly, view it on GitHub <#16?email_source=notifications&email_token=AA3NF4XZGEN3F7VVOLDQIZDPZ6MBXA5CNFSM4HSZO2SKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGODXM7BNA#issuecomment-500822196>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AA3NF4WXJQF4NDNQHHUMSWTPZ6MBXANCNFSM4HSZO2SA> .

-- Ibrar Ahmed — You are receiving this because you commented. Reply to this email directly, view it on GitHub, or mute the thread.

[ibrarahmad]

Yes, I am looking at that.

…

On Tue, Jun 11, 2019 at 5:55 PM ethanryl ***@***.***> wrote: I believe you can beat it,Please let me know if there is any new progress. thanks for you. ------------------ 原始邮件 ------------------ 发件人: "Ibrar ***@***.***>; 发送时间: 2019年6月11日(星期二) 晚上8:51 收件人: ***@***.***>; 抄送: ***@***.******@***.***>; 主题: Re: [Percona-Lab/clickhousedb_fdw] aggregate doesn't push down (#16) That's is strange, I need to look at that because it works on my end. On Tue, Jun 11, 2019 at 5:37 PM ethanryl ***@***.***> wrote: > EXPLAIN VERBOSE SELECT count(*) FROM tax_bills_nyc; > QUERY PLAN > > Aggregate (cost=0.00..0.01 rows=1 width=8) > Output: count(*) > -> Foreign Scan on public.tax_bills_nyc (cost=0.00..0.00 rows=0 width=0) > Output: bbl, owner_name, address, tax_class, tax_rate, emv, tbea, bav, > tba, property_tax, condonumber, condo, insertion_date > Remote SQL: SELECT NULL FROM test_database.tax_bills_nyc > > — > You are receiving this because you commented. > Reply to this email directly, view it on GitHub > < #16?email_source=notifications&email_token=AA3NF4XZGEN3F7VVOLDQIZDPZ6MBXA5CNFSM4HSZO2SKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGODXM7BNA#issuecomment-500822196>, > or mute the thread > < https://github.com/notifications/unsubscribe-auth/AA3NF4WXJQF4NDNQHHUMSWTPZ6MBXANCNFSM4HSZO2SA> > . > -- Ibrar Ahmed — You are receiving this because you commented. Reply to this email directly, view it on GitHub, or mute the thread. — You are receiving this because you commented. Reply to this email directly, view it on GitHub <#16?email_source=notifications&email_token=AA3NF4VDBWI4DUDIEZDPW3LPZ6OEVA5CNFSM4HSZO2SKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGODXNAR2I#issuecomment-500828393>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AA3NF4SKRKLR6F4SK6NPLQDPZ6OEVANCNFSM4HSZO2SA> .

-- Ibrar Ahmed

[ethanryl]

Hello, about the problem that clickhousedb_fdw cannot be pushed down, how is the research going.

…

------------------ 原始邮件 ------------------ 发件人: "Ibrar Ahmed"<[email protected]>; 发送时间: 2019年6月11日(星期二) 晚上8:59 收件人: "Percona-Lab/clickhousedb_fdw"<[email protected]>; 抄送: "下一个明天"<[email protected]>;"Comment"<[email protected]>; 主题: Re: [Percona-Lab/clickhousedb_fdw] aggregate doesn't push down (#16) Yes, I am looking at that. On Tue, Jun 11, 2019 at 5:55 PM ethanryl <[email protected]> wrote: I believe you can beat it,Please let me know if there is any new progress. thanks for you. ------------------ 原始邮件 ------------------ 发件人: "Ibrar Ahmed"<[email protected]>; 发送时间: 2019年6月11日(星期二) 晚上8:51 收件人: "Percona-Lab/clickhousedb_fdw"<[email protected]>; 抄送: "下一个明天"<[email protected]>;"Comment"<[email protected]>; 主题: Re: [Percona-Lab/clickhousedb_fdw] aggregate doesn't push down (#16) That's is strange, I need to look at that because it works on my end. On Tue, Jun 11, 2019 at 5:37 PM ethanryl <[email protected]> wrote: > EXPLAIN VERBOSE SELECT count(*) FROM tax_bills_nyc; > QUERY PLAN > > Aggregate (cost=0.00..0.01 rows=1 width=8) > Output: count(*) > -> Foreign Scan on public.tax_bills_nyc (cost=0.00..0.00 rows=0 width=0) > Output: bbl, owner_name, address, tax_class, tax_rate, emv, tbea, bav, > tba, property_tax, condonumber, condo, insertion_date > Remote SQL: SELECT NULL FROM test_database.tax_bills_nyc > > — > You are receiving this because you commented. > Reply to this email directly, view it on GitHub > < #16?email_source=notifications&email_token=AA3NF4XZGEN3F7VVOLDQIZDPZ6MBXA5CNFSM4HSZO2SKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGODXM7BNA#issuecomment-500822196>, > or mute the thread > < https://github.com/notifications/unsubscribe-auth/AA3NF4WXJQF4NDNQHHUMSWTPZ6MBXANCNFSM4HSZO2SA> > . > -- Ibrar Ahmed — You are receiving this because you commented. Reply to this email directly, view it on GitHub, or mute the thread. — You are receiving this because you commented. Reply to this email directly, view it on GitHub <#16?email_source=notifications&email_token=AA3NF4VDBWI4DUDIEZDPW3LPZ6OEVA5CNFSM4HSZO2SKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGODXNAR2I#issuecomment-500828393>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AA3NF4SKRKLR6F4SK6NPLQDPZ6OEVANCNFSM4HSZO2SA> .

-- Ibrar Ahmed — You are receiving this because you commented. Reply to this email directly, view it on GitHub, or mute the thread.

[ibrarahmad]

You need to wait for a couple of more days, I am busy in some important work.

…

On Mon, Jun 24, 2019 at 7:32 AM ethanryl ***@***.***> wrote: Hello, about the problem that clickhousedb_fdw cannot be pushed down, how is the research going. ------------------ 原始邮件 ------------------ 发件人: "Ibrar ***@***.***>; 发送时间: 2019年6月11日(星期二) 晚上8:59 收件人: ***@***.***>; 抄送: ***@***.******@***.***>; 主题: Re: [Percona-Lab/clickhousedb_fdw] aggregate doesn't push down (#16) Yes, I am looking at that. On Tue, Jun 11, 2019 at 5:55 PM ethanryl ***@***.***> wrote: > I believe you can beat it,Please let me know if there is any new progress. > thanks for you. > > > > > ------------------ 原始邮件 ------------------ > 发件人: "Ibrar ***@***.***>; > 发送时间: 2019年6月11日(星期二) 晚上8:51 > 收件人: ***@***.***>; > 抄送: ***@***.******@***.***>; > 主题: Re: [Percona-Lab/clickhousedb_fdw] aggregate doesn't push down (#16) > > > > That's is strange, I need to look at that because it works on my end. > > > On Tue, Jun 11, 2019 at 5:37 PM ethanryl ***@***.***> > wrote: > > > EXPLAIN VERBOSE SELECT count(*) FROM tax_bills_nyc; > > QUERY PLAN > > > > Aggregate (cost=0.00..0.01 rows=1 width=8) > > Output: count(*) > > -> Foreign Scan on public.tax_bills_nyc (cost=0.00..0.00 rows=0 width=0) > > Output: bbl, owner_name, address, tax_class, tax_rate, emv, tbea, bav, > > tba, property_tax, condonumber, condo, insertion_date > > Remote SQL: SELECT NULL FROM test_database.tax_bills_nyc > > > > — > > You are receiving this because you commented. > > Reply to this email directly, view it on GitHub > > < > #16?email_source=notifications&email_token=AA3NF4XZGEN3F7VVOLDQIZDPZ6MBXA5CNFSM4HSZO2SKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGODXM7BNA#issuecomment-500822196>, > > > or mute the thread > > < > https://github.com/notifications/unsubscribe-auth/AA3NF4WXJQF4NDNQHHUMSWTPZ6MBXANCNFSM4HSZO2SA> > > > . > > > > > -- > Ibrar Ahmed > > — > You are receiving this because you commented. > Reply to this email directly, view it on GitHub, or mute the thread. > > — > You are receiving this because you commented. > Reply to this email directly, view it on GitHub > < #16?email_source=notifications&email_token=AA3NF4VDBWI4DUDIEZDPW3LPZ6OEVA5CNFSM4HSZO2SKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGODXNAR2I#issuecomment-500828393>, > or mute the thread > < https://github.com/notifications/unsubscribe-auth/AA3NF4SKRKLR6F4SK6NPLQDPZ6OEVANCNFSM4HSZO2SA> > . > -- Ibrar Ahmed — You are receiving this because you commented. Reply to this email directly, view it on GitHub, or mute the thread. — You are receiving this because you commented. Reply to this email directly, view it on GitHub <#16?email_source=notifications&email_token=AA3NF4XETWC44OAEMYA6ZXLP4AW2PA5CNFSM4HSZO2SKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGODYLSXRI#issuecomment-504835013>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AA3NF4T7O7ITLVIGZKYIWZLP4AW2PANCNFSM4HSZO2SA> .

-- Ibrar Ahmed

[harris-chiu]

you can actually take out the commit I mentioned earlier to get the push down working
16d79c0

[ibrarahmad]

I have added that because of multi-joint support. Clickhouse does not support multi-join that's. Have you tried clickhousedb_fdw.join_pushdown_safe = true or clickhousedb_fdw.join_pushdown_safe = false;

…

On Tue, Jun 25, 2019 at 10:33 PM harris-chiu ***@***.***> wrote: you can actually take out the commit I mentioned earlier to get the push down working 16d79c0 <16d79c0> — You are receiving this because you commented. Reply to this email directly, view it on GitHub <#16?email_source=notifications&email_token=AA3NF4QE4GB73CWSYZ4JWE3P4JJHXA5CNFSM4HSZO2SKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGODYRAF3I#issuecomment-505545453>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AA3NF4VMS4BRDOY2QIYEVQTP4JJHXANCNFSM4HSZO2SA> .

-- Ibrar Ahmed

[ethanryl]

hi，ibrarahmad、harris-chiu The problem has been solved. I set clickhousedb_fdw.join_pushdown_safe=on and then the SQL can be pushed down. Thank you very much.

[harris-chiu]

btw do you know how we can set this parameter permanently? so that join always push down?

…

On Tue, Jun 25, 2019 at 6:53 PM ethanryl ***@***.***> wrote: hi，ibrarahmad、harris-chiu The problem has been solved. I set clickhousedb_fdw.join_pushdown_safe=on and then the SQL can be pushed down. Thank you very much. — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <#16?email_source=notifications&email_token=AB7LLIUTOB454Q747IDGPDLP4LDY3A5CNFSM4HSZO2SKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGODYSCMVY#issuecomment-505685591>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AB7LLIRFRTQDRALFBJRPKVDP4LDY3ANCNFSM4HSZO2SA> .

[ibrarahmad]

Add that to postgresql.conf file On Thu, Jun 27, 2019 at 3:05 AM harris-chiu <[email protected]> wrote:

…

btw do you know how we can set this parameter permanently? so that join always push down? On Tue, Jun 25, 2019 at 6:53 PM ethanryl ***@***.***> wrote: > hi，ibrarahmad、harris-chiu The problem has been solved. I set > clickhousedb_fdw.join_pushdown_safe=on and then the SQL can be pushed down. > Thank you very much. > > — > You are receiving this because you authored the thread. > Reply to this email directly, view it on GitHub > < #16?email_source=notifications&email_token=AB7LLIUTOB454Q747IDGPDLP4LDY3A5CNFSM4HSZO2SKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGODYSCMVY#issuecomment-505685591 >, > or mute the thread > < https://github.com/notifications/unsubscribe-auth/AB7LLIRFRTQDRALFBJRPKVDP4LDY3ANCNFSM4HSZO2SA > > . > — You are receiving this because you commented. Reply to this email directly, view it on GitHub <#16?email_source=notifications&email_token=AA3NF4RDXM3I3HDKVPEO53LP4PRY5A5CNFSM4HSZO2SKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGODYU6L5Y#issuecomment-506062327>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AA3NF4TWMMTDSXTAEWDBOW3P4PRY5ANCNFSM4HSZO2SA> .

-- Ibrar Ahmed