Predicate pushdown and projection pushdown

[delacro14]

Hello!

I am having a hard time understanding the use of predicate pushdown happening in the Init global function within the st_read.cpp.

To replicate the issue, these are the test queries I used:

CREATE TABLE zones AS (
    SELECT 
        ST_X(ST_Centroid(geom)) AS x,
        ST_Y(ST_Centroid(geom)) AS y
    FROM 
      ST_Read('./test/data/nyc_taxi/taxi_zones/taxi_zones.shx')
    WHERE 
        x BETWEEN 950000 AND 980000);

CREATE TABLE areas_above_threshold AS (
    SELECT
        geom,
        ST_Area(geom) AS area
    FROM
       ST_Read('./test/data/nyc_taxi/taxi_zones/taxi_zones.shx')
    WHERE
        ST_Area(geom) > 100000  
);

What I did was, run these queries in debug mode and put a breakpoint here. I noticed that while running these queries it skips this breakpoint and it seems that this predicate pushdown is not executed. It does execute filter_pushdown.cpp which is a part of the DuckDB query optimizer.

1. Does somebody know why this could be happening if WHERE clause is used?
2. Is DuckDB projection pushdown only being executed? (I am asking this because there is a comment here that it's not yet implemented.
3. Is there an example query where this part of the code is utilized?

Thank you in advance

[Maxxen]

It's been a while since I looked into this, but AFAIK DuckDB doesn't do predicate pushdown into table functions if the predicate is "advanced", e.g. it can only consist of simple inequalities and comparisons, it can't contain function calls like ST_Area(), or references to them like ST_X(ST_Centroid(geom)).

Here's an example that does work, as its just a reference to one of the columns of the underlying data:

SELECT objectid, st_centroid(geom) 
FROM st_read('./test/data/nyc_taxi/taxi_zones/taxi_zones.shp') 
WHERE objectid < 10;

I think it is possible to enable "advanced" predicate pushdown too, but then you need to traverse, inspect and reject all the expression trees, which is a lot of work as not all functions (or expressions) can actually be pushed down since GDAL has a pretty limited sql dialect.