aggregate doesn't push down

harris-chiu commented 5 years ago

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 commented 5 years ago

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

harris-chiu commented 5 years ago

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)

https://github.com/Percona-Lab/clickhousedb_fdw/commit/16d79c0e3ac2c564c08d87a8d7d7a0c871b0f75d

It looks like this commit is causing the issue

ethanryl commented 5 years ago

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

ethanryl commented 5 years ago

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 commented 5 years ago

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

On Tue, Jun 11, 2019 at 2:24 PM ethanryl notifications@github.com 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 https://github.com/Percona-Lab/clickhousedb_fdw/issues/16?email_source=notifications&email_token=AA3NF4XSW2EZQVMXKJP23DLPZ5VLLA5CNFSM4HSZO2SKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGODXMQIXI#issuecomment-500761693, or mute the thread https://github.com/notifications/unsubscribe-auth/AA3NF4QRIHX3IFVJRGZQLOLPZ5VLLANCNFSM4HSZO2SA .

-- Ibrar Ahmed

ethanryl commented 5 years ago

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 commented 5 years ago

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 notifications@github.com 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 https://github.com/Percona-Lab/clickhousedb_fdw/issues/16?email_source=notifications&email_token=AA3NF4XZGEN3F7VVOLDQIZDPZ6MBXA5CNFSM4HSZO2SKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGODXM7BNA#issuecomment-500822196, or mute the thread https://github.com/notifications/unsubscribe-auth/AA3NF4WXJQF4NDNQHHUMSWTPZ6MBXANCNFSM4HSZO2SA .

-- Ibrar Ahmed

ethanryl commented 5 years ago

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

------------------ 原始邮件 ------------------ 发件人: "Ibrar Ahmed"notifications@github.com; 发送时间: 2019年6月11日(星期二) 晚上8:51 收件人: "Percona-Lab/clickhousedb_fdw"clickhousedb_fdw@noreply.github.com; 抄送: "下一个明天"810128618@qq.com;"Comment"comment@noreply.github.com; 主题: 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 notifications@github.com 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 https://github.com/Percona-Lab/clickhousedb_fdw/issues/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 commented 5 years ago

Yes, I am looking at that.

On Tue, Jun 11, 2019 at 5:55 PM ethanryl notifications@github.com wrote:

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

------------------ 原始邮件 ------------------ 发件人: "Ibrar Ahmed"notifications@github.com; 发送时间: 2019年6月11日(星期二) 晚上8:51 收件人: "Percona-Lab/clickhousedb_fdw"clickhousedb_fdw@noreply.github.com; 抄送: "下一个明天"810128618@qq.com;"Comment"comment@noreply.github.com; 主题: 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 notifications@github.com 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 < https://github.com/Percona-Lab/clickhousedb_fdw/issues/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 https://github.com/Percona-Lab/clickhousedb_fdw/issues/16?email_source=notifications&email_token=AA3NF4VDBWI4DUDIEZDPW3LPZ6OEVA5CNFSM4HSZO2SKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGODXNAR2I#issuecomment-500828393, or mute the thread https://github.com/notifications/unsubscribe-auth/AA3NF4SKRKLR6F4SK6NPLQDPZ6OEVANCNFSM4HSZO2SA .

-- Ibrar Ahmed

ethanryl commented 5 years ago

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

------------------ 原始邮件 ------------------ 发件人: "Ibrar Ahmed"notifications@github.com; 发送时间: 2019年6月11日(星期二) 晚上8:59 收件人: "Percona-Lab/clickhousedb_fdw"clickhousedb_fdw@noreply.github.com; 抄送: "下一个明天"810128618@qq.com;"Comment"comment@noreply.github.com; 主题: 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 notifications@github.com wrote:

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

------------------ 原始邮件 ------------------ 发件人: "Ibrar Ahmed"notifications@github.com; 发送时间: 2019年6月11日(星期二) 晚上8:51 收件人: "Percona-Lab/clickhousedb_fdw"clickhousedb_fdw@noreply.github.com; 抄送: "下一个明天"810128618@qq.com;"Comment"comment@noreply.github.com; 主题: 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 notifications@github.com 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 < https://github.com/Percona-Lab/clickhousedb_fdw/issues/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 https://github.com/Percona-Lab/clickhousedb_fdw/issues/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 commented 5 years ago

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 notifications@github.com wrote:

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

------------------ 原始邮件 ------------------ 发件人: "Ibrar Ahmed"notifications@github.com; 发送时间: 2019年6月11日(星期二) 晚上8:59 收件人: "Percona-Lab/clickhousedb_fdw"clickhousedb_fdw@noreply.github.com; 抄送: "下一个明天"810128618@qq.com;"Comment"comment@noreply.github.com; 主题: 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 notifications@github.com wrote:

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

------------------ 原始邮件 ------------------ 发件人: "Ibrar Ahmed"notifications@github.com; 发送时间: 2019年6月11日(星期二) 晚上8:51 收件人: "Percona-Lab/clickhousedb_fdw"clickhousedb_fdw@noreply.github.com;

抄送: "下一个明天"810128618@qq.com;"Comment"comment@noreply.github.com; 主题: 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 notifications@github.com 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 <

https://github.com/Percona-Lab/clickhousedb_fdw/issues/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 < https://github.com/Percona-Lab/clickhousedb_fdw/issues/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 https://github.com/Percona-Lab/clickhousedb_fdw/issues/16?email_source=notifications&email_token=AA3NF4XETWC44OAEMYA6ZXLP4AW2PA5CNFSM4HSZO2SKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGODYLSXRI#issuecomment-504835013, or mute the thread https://github.com/notifications/unsubscribe-auth/AA3NF4T7O7ITLVIGZKYIWZLP4AW2PANCNFSM4HSZO2SA .

-- Ibrar Ahmed

harris-chiu commented 5 years ago

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

ibrarahmad commented 5 years ago

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 notifications@github.com wrote:

you can actually take out the commit I mentioned earlier to get the push down working 16d79c0 https://github.com/Percona-Lab/clickhousedb_fdw/commit/16d79c0e3ac2c564c08d87a8d7d7a0c871b0f75d

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/Percona-Lab/clickhousedb_fdw/issues/16?email_source=notifications&email_token=AA3NF4QE4GB73CWSYZ4JWE3P4JJHXA5CNFSM4HSZO2SKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGODYRAF3I#issuecomment-505545453, or mute the thread https://github.com/notifications/unsubscribe-auth/AA3NF4VMS4BRDOY2QIYEVQTP4JJHXANCNFSM4HSZO2SA .

-- Ibrar Ahmed

ethanryl commented 5 years ago

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 commented 5 years ago

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 notifications@github.com 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 https://github.com/Percona-Lab/clickhousedb_fdw/issues/16?email_source=notifications&email_token=AB7LLIUTOB454Q747IDGPDLP4LDY3A5CNFSM4HSZO2SKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGODYSCMVY#issuecomment-505685591, or mute the thread https://github.com/notifications/unsubscribe-auth/AB7LLIRFRTQDRALFBJRPKVDP4LDY3ANCNFSM4HSZO2SA .

ibrarahmad commented 5 years ago

Add that to postgresql.conf file

On Thu, Jun 27, 2019 at 3:05 AM harris-chiu notifications@github.com 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 notifications@github.com 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 < https://github.com/Percona-Lab/clickhousedb_fdw/issues/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 https://github.com/Percona-Lab/clickhousedb_fdw/issues/16?email_source=notifications&email_token=AA3NF4RDXM3I3HDKVPEO53LP4PRY5A5CNFSM4HSZO2SKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGODYU6L5Y#issuecomment-506062327, or mute the thread https://github.com/notifications/unsubscribe-auth/AA3NF4TWMMTDSXTAEWDBOW3P4PRY5ANCNFSM4HSZO2SA .

-- Ibrar Ahmed

Percona-Lab / clickhousedb_fdw