duckdb / duckdb/duckdb-postgres

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

Open
#369 2 comments 4 reactions 0 assignees View on GitHub
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.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.