bug: filter_push_down still ends up scanning postgres table without hitting any indexes for filters

大規模なインデックス付き PostgreSQL テーブルに対してクエリを再現し、pg_use_ctid_scan=true および false と postgres_query の結果を比較します。まず PostgreSQL 拡張の ctid-scan と filter-pushdown のエントリポイントを追跡します。フィルタリングされたスキャンがテーブル全体のパスを回避し、他のスキャンを壊すことなくインデックス付きクエリの動作に近づけば完了です。

What happens?

filter_projection_pushdown doesn't actually push down the filter to postgres. It still ends up scanning the table taking 10s+ when filter should hit the index and return in milliseconds.

To Reproduce

Create a table with roughly 10M rows in postgres.

ATTACH 'postgres://u:pass@....' AS pg_reader (TYPE POSTGRES, READ_ONLY)

explain analyze select count(*) from pg_reader.main.order_lines where updated_at = '2025-06-09 14:35:00.742342+00';

┌────────────────────────────────────────────────┐
│┌──────────────────────────────────────────────┐│
││               Total Time: 9.27s              ││
│└──────────────────────────────────────────────┘│
└────────────────────────────────────────────────┘
┌───────────────────────────┐
│           QUERY           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│      EXPLAIN_ANALYZE      │
│    ────────────────────   │
│           0 Rows          │
│          (0.00s)          │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│    UNGROUPED_AGGREGATE    │
│    ────────────────────   │
│        Aggregates:        │
│        count_star()       │
│                           │
│           1 Rows          │
│          (0.00s)          │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         TABLE_SCAN        │
│    ────────────────────   │
│     Table: order_lines    │
│                           │
│        Projections:       │
│         updated_at        │
│                           │
│          Filters:         │
│ updated_at='2025-06-09 14 │
│     :35:00.742342+00':    │
│ :TIMESTAMP WITH TIME ZONE │
│                           │
│           2 Rows          │
│          (53.29s)         │
└───────────────────────────┘

Even though query plan shows a filter, the filter doesn't seem to get applied at scan time. Postgres still ends up scanning all rows of table. updated_at column has an index. It ends up taking ~10s.

For our larger tables, with 10M+ rows, duckdb extension places huge load on the database.

Same query via postgres_query runs in milliseconds

explain analyze select count(*) from postgres_query('pg_reader', 'select updated_at from main.order_lines where updated_at = ''2025-06-09 14:35:00.742342+00''');

┌────────────────────────────────────────────────┐
│┌──────────────────────────────────────────────┐│
││              Total Time: 0.525s              ││
│└──────────────────────────────────────────────┘│
└────────────────────────────────────────────────┘
┌───────────────────────────┐
│           QUERY           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│      EXPLAIN_ANALYZE      │
│    ────────────────────   │
│           0 Rows          │
│          (0.00s)          │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│    UNGROUPED_AGGREGATE    │
│    ────────────────────   │
│        Aggregates:        │
│        count_star()       │
│                           │
│           1 Rows          │
│          (0.00s)          │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         TABLE_SCAN        │
│    ────────────────────   │
│         Function:         │
│       POSTGRES_QUERY      │
│                           │
│        Projections:       │
│         updated_at        │
│                           │
│           2 Rows          │
│          (0.16s)          │
└───────────────────────────┘

use_ctid_scan should be disabled if any filters are specified. So the query is equivalent to postgres_query, but much faster.

pg_use_ctid_scan

SET pg_use_ctid_scan = true; -- default

D .timer on
D select count(*) from pg_reader.main.order_lines where updated_at = '2025-06-09 14:35:00.742342+00'::timestamptz;
┌──────────────┐
│ count_star() │
│    int64     │
├──────────────┤
│      2       │
└──────────────┘
Run Time (s): real 10.491 user 0.108536 sys 0.083701


SET pg_use_ctid_scan = false; -- disable ctid_scan

D select count(*) from pg_reader.main.order_lines where updated_at = '2025-06-09 14:35:00.742342+00'::timestamptz;
┌──────────────┐
│ count_star() │
│    int64     │
├──────────────┤
│      2       │
└──────────────┘
Run Time (s): real 0.246 user 0.001300 sys 0.001340

50x faster when SET pg_use_ctid_scan = false.

OS:

MacOS 14.6.1

PostgreSQL Version:

15

DuckDB Version:

1.3.2

DuckDB Client:

cli

Full Name:

Noj V

Affiliation:

Recurrency

Have you tried this on the latest main branch?

• I agree

Have you tried the steps to reproduce? Do they include all relevant data and configuration? Does the issue you report still appear there?

• I agree