Query optimization for a ducklake table

[billziss-gh]

I have a large table on a ducklake with parquet files stored on object storage. I am trying to understand the time difference between the two queries below. I ran each query after restarting duckdb to avoid caching effects (and for the same reason I did not use cache_httpfs).

explain analyze from options where symbol='AAPL';               -- 75 sec
explain analyze from options where symbol='AAPL' and type='c';  -- 706 sec

In my mind the second query should not take 9.5x longer than the first one as it returns a subset of the results of the first one. I modified the second query as follows to get a time similar to the first query.

explain analyze WITH q AS MATERIALIZED (
    SELECT * FROM options WHERE symbol='AAPL'
)
SELECT * FROM q WHERE type='c';
--  76 sec

I will admit that I am no expert in database optimizers, but to my untrained eye this looks like a failure of the optimizer to rewrite the query into one that performs significantly better (almost 10x).

I include full explain analyze plans for the queries below. From these it can be seen that all 3 queries fetch about the same amount of data (approx. 258 MiB) but the second query issues substantially more GETs (19965 vs 2502).

D PRAGMA explain_output='all';
D .timer on
D explain analyze from options where symbol='AAPL';
100% ▕██████████████████████████████████████▏ (00:01:14.76 elapsed)
┌─────────────────────────────────────┐
│┌───────────────────────────────────┐│
││    Query Profiling Information    ││
│└───────────────────────────────────┘│
└─────────────────────────────────────┘
explain analyze from options where symbol='AAPL';
┌─────────────────────────────────────┐
│┌───────────────────────────────────┐│
││         HTTPFS HTTP Stats         ││
││                                   ││
││           in: 257.9 MiB           ││
││            out: 0 bytes           ││
││              #HEAD: 0             ││
││             #GET: 2502            ││
││              #PUT: 0              ││
││              #POST: 0             ││
││             #DELETE: 0            ││
│└───────────────────────────────────┘│
└─────────────────────────────────────┘
┌────────────────────────────────────────────────┐
│┌──────────────────────────────────────────────┐│
││              Total Time: 75.24s              ││
│└──────────────────────────────────────────────┘│
└────────────────────────────────────────────────┘
┌───────────────────────────┐
│           QUERY           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│      EXPLAIN_ANALYZE      │
│    ────────────────────   │
│           0 rows          │
│          (0.00s)          │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         TABLE_SCAN        │
│    ────────────────────   │
│       Table: options      │
│                           │
│        Projections:       │
│           Symbol          │
│            Date           │
│           Strike          │
│         Expiration        │
│            Type           │
│         LastPrice         │
│            Bid            │
│            Ask            │
│           BidIV           │
│           AskIV           │
│             OI            │
│           Volume          │
│           Delta           │
│           Gamma           │
│            Vega           │
│           Theta           │
│            Rho            │
│                           │
│          Filters:         │
│       Symbol='AAPL'       │
│                           │
│    Total Files Read: 66   │
│                           │
│       6,604,262 rows      │
│         (573.78s)         │
└───────────────────────────┘
Run Time (s): real 75.235 user 8.163639 sys 1.577722

D PRAGMA explain_output='all';
D .timer on
D explain analyze from options where symbol='AAPL' and type='c';
100% ▕██████████████████████████████████████▏ (00:11:45.73 elapsed)
┌─────────────────────────────────────┐
│┌───────────────────────────────────┐│
││    Query Profiling Information    ││
│└───────────────────────────────────┘│
└─────────────────────────────────────┘
explain analyze from options where symbol='AAPL' and type='c';
┌─────────────────────────────────────┐
│┌───────────────────────────────────┐│
││         HTTPFS HTTP Stats         ││
││                                   ││
││           in: 258.7 MiB           ││
││            out: 0 bytes           ││
││              #HEAD: 0             ││
││            #GET: 19965            ││
││              #PUT: 0              ││
││              #POST: 0             ││
││             #DELETE: 0            ││
│└───────────────────────────────────┘│
└─────────────────────────────────────┘
┌────────────────────────────────────────────────┐
│┌──────────────────────────────────────────────┐│
││              Total Time: 706.20s             ││
│└──────────────────────────────────────────────┘│
└────────────────────────────────────────────────┘
┌───────────────────────────┐
│           QUERY           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│      EXPLAIN_ANALYZE      │
│    ────────────────────   │
│           0 rows          │
│          (0.00s)          │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         PROJECTION        │
│    ────────────────────   │
│           Symbol          │
│            Date           │
│           Strike          │
│         Expiration        │
│            Type           │
│         LastPrice         │
│            Bid            │
│            Ask            │
│           BidIV           │
│           AskIV           │
│             OI            │
│           Volume          │
│           Delta           │
│           Gamma           │
│            Vega           │
│           Theta           │
│            Rho            │
│                           │
│       3,302,131 rows      │
│          (0.01s)          │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         TABLE_SCAN        │
│    ────────────────────   │
│       Table: options      │
│                           │
│        Projections:       │
│           Symbol          │
│            Type           │
│            Date           │
│           Strike          │
│         Expiration        │
│         LastPrice         │
│            Bid            │
│            Ask            │
│           BidIV           │
│           AskIV           │
│             OI            │
│           Volume          │
│           Delta           │
│           Gamma           │
│            Vega           │
│           Theta           │
│            Rho            │
│                           │
│          Filters:         │
│       Symbol='AAPL'       │
│          Type='c'         │
│                           │
│    Total Files Read: 66   │
│                           │
│       3,302,131 rows      │
│         (5636.90s)        │
└───────────────────────────┘
Run Time (s): real 706.141 user 19.688677 sys 5.360346

D PRAGMA explain_output='all';
.timer on
D .timer on
D explain analyze WITH q AS MATERIALIZED (
    SELECT * FROM options WHERE symbol='AAPL'
  )
  SELECT * FROM q WHERE type='c';
100% ▕██████████████████████████████████████▏ (00:01:15.51 elapsed)
┌─────────────────────────────────────┐
│┌───────────────────────────────────┐│
││    Query Profiling Information    ││
│└───────────────────────────────────┘│
└─────────────────────────────────────┘
explain analyze WITH q AS MATERIALIZED (   SELECT * FROM options WHERE symbol='AAPL' ) SELECT * FROM q WHERE type='c';
┌─────────────────────────────────────┐
│┌───────────────────────────────────┐│
││         HTTPFS HTTP Stats         ││
││                                   ││
││           in: 257.9 MiB           ││
││            out: 0 bytes           ││
││              #HEAD: 0             ││
││             #GET: 2502            ││
││              #PUT: 0              ││
││              #POST: 0             ││
││             #DELETE: 0            ││
│└───────────────────────────────────┘│
└─────────────────────────────────────┘
┌────────────────────────────────────────────────┐
│┌──────────────────────────────────────────────┐│
││              Total Time: 75.97s              ││
│└──────────────────────────────────────────────┘│
└────────────────────────────────────────────────┘
┌───────────────────────────┐
│           QUERY           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│      EXPLAIN_ANALYZE      │
│    ────────────────────   │
│           0 rows          │
│          (0.00s)          │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│            CTE            │
│    ────────────────────   │
│        CTE Name: q        │
│       Table Index: 0      ├──────────────┐
│                           │              │
│           0 rows          │              │
│          (0.49s)          │              │
└─────────────┬─────────────┘              │
┌─────────────┴─────────────┐┌─────────────┴─────────────┐
│           FILTER          ││           FILTER          │
│    ────────────────────   ││    ────────────────────   │
│        (Type = 'c')       ││        (Type = 'c')       │
│                           ││                           │
│       3,302,131 rows      ││       3,302,131 rows      │
│          (0.25s)          ││          (0.02s)          │
└─────────────┬─────────────┘└─────────────┬─────────────┘
┌─────────────┴─────────────┐┌─────────────┴─────────────┐
│         TABLE_SCAN        ││          CTE_SCAN         │
│    ────────────────────   ││    ────────────────────   │
│       Table: options      ││        CTE Index: 0       │
│                           ││                           │
│        Projections:       ││                           │
│           Symbol          ││                           │
│            Date           ││                           │
│           Strike          ││                           │
│         Expiration        ││                           │
│            Type           ││                           │
│         LastPrice         ││                           │
│            Bid            ││                           │
│            Ask            ││                           │
│           BidIV           ││                           │
│           AskIV           ││                           │
│             OI            ││                           │
│           Volume          ││                           │
│           Delta           ││                           │
│           Gamma           ││                           │
│            Vega           ││                           │
│           Theta           ││                           │
│            Rho            ││                           │
│                           ││                           │
│          Filters:         ││                           │
│       Symbol='AAPL'       ││                           │
│                           ││                           │
│    Total Files Read: 66   ││                           │
│                           ││                           │
│       6,604,262 rows      ││       3,302,131 rows      │
│         (577.33s)         ││          (0.13s)          │
└───────────────────────────┘└───────────────────────────┘
Run Time (s): real 75.981 user 9.113896 sys 2.145798