search

Infinidat / infi.clickhouse_fdw

A PostgreSQL foreign data wrapper for ClickHouse
BSD 3-Clause "New" or "Revised" License
54 stars 4 forks source link

where, limit, order statements are not forwarded to CH #4

Open kosjak1 opened 6 years ago

kosjak1 commented 6 years ago

Hello! I have installed your Clickhouse FDW and got problems. Example:

Query in psql: select * from clickhouse.vast_tracking where date=current_date order by dt desc limit 10;

Query in CH processlist: SELECT intDiv(2333294099, uniqCombined(vast_tracking_id)) AS vast_tracking_id, intDiv(2333294099, uniqCombined(ip)) AS ip, intDiv(2333294099, uniqCombined(date)) AS date, intDiv(2333294099, uniqCombined(key)) AS key, intDiv(2333294099, uniqCombined(action)) AS action, intDiv(2333294099, uniqCombined(url)) AS url, intDiv(2333294099, uniqCombined(referrer)) AS referrer, intDiv(2333294099, uniqCombined(country)) AS country, intDiv(2333294099, uniqCombined(banner)) AS banner, intDiv(2333294099, uniqCombined(year)) AS year, intDiv(2333294099, uniqCombined(month)) AS month, intDiv(2333294099, uniqCombined(day)) AS day, intDiv(2333294099, uniqCombined(hour)) AS hour, intDiv(2333294099, uniqCombined(uuid)) AS uuid, intDiv(2333294099, uniqCombined(sky_uuid)) AS sky_uuid, intDiv(2333294099, uniqCombined(creative_id)) AS creative_id, intDiv(2333294099, uniqCombined(revenue)) AS revenue, intDiv(2333294099, uniqCombined(in_price)) AS in_price, intDiv(2333294099, uniqCombined(wm_percent)) AS wm_percent, intDiv(2333294099, uniqCombined(profit)) AS profit, intDiv(2333294099, uniqCombined(dt)) AS dt, intDiv(2333294099, uniqCombined(vt)) AS vt, intDiv(2333294099, uniqCombined(advert_id)) AS advert_id, intDiv(2333294099, uniqCombined(site_url)) AS site_url, intDiv(2333294099, uniqCombined(campaign_name)) AS campaign_name FROM default.vast

As you can see - where, order by, limit are not passed, so CH is trying to obtain all data from table and push it to Postgres, this table have billions of records and it will take a long time to pass data.

ishirav commented 6 years ago

Hi,

I believe postgres is not pushing the condition to the FDW. The documentation states that: "WHERE clauses are not sent to the remote server unless they use only data types, operators, and functions that are built-in or belong to an extension that's listed in the foreign server's extensions option. Operators and functions in such clauses must be IMMUTABLE as well." In this case, I'm guessing current_date is mutable and therefore the condition is not pushed down to the FDW. Try changing the current_date to a specific fixed date, and see if that makes a difference.

kosjak1 commented 6 years ago

I've tried this query: select * from clickhouse.vast_tracking where date='2018-06-06' order by dt desc limit 10;

But no luck, CH receives query without where parameters...