duckdb / duckdb/duckdb-postgres
bug: filter_push_down still ends up scanning postgres table without hitting any indexes for filters
- Dominant language
- C++
- Stars
- 372
- Forks
- 105
- Avg merge
- 10h 19m
- Merged PRs (30d)
- 18
Description
### 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.
```sql
ATTACH 'postgres://u:pass@....' AS pg_reader (TYPE POSTGRES, READ_ONLY)
```
```sql
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
```sql
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
```sql
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?
- [x] 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?
- [x] I agree
Contributor guide
No contributing guide indexed for this repository
Assessment
This issue has not been assessed yet.