postgres_scanner

postgres_scanner copied to clipboard

What happens?

I'm trying to use https://github.com/ossc-db/pg_hint_plan extension that enables query hinting via embedded comments.

Unfortunately it looks like comments do not reach the server and hints don't work.

To Reproduce

Install pg_hint_plan in postgres and execute:

create table t
(
    id   serial primary key
);

explain select /*+ IndexScan(t) */ * from t where id > 0;
explain select /*+ NoIndexScan(t) */ * from t where id > 0;

You'll notice two queries use different plans.

Now try to do the same through duckdb with postgres_query() and see that it's always the same plan.

attach 'dbname=postgres host=localhost' as pg (type postgres, read_only) ;
select * from postgres_query('pg', $$
	select /*+ IndexScan(t) */ * from t where id > 0
$$);

Actually I don't know how to run EXPLAIN through postgres_query(), was merely observing performance difference on a very large table.

Another way would be log_statement = all.

OS:

MacOS

PostgreSQL Version:

16

DuckDB Version:

1.1.3

DuckDB Client:

JDBC/IntelliJ

Full Name:

Marcin Wisnicki

Affiliation:

MoneyLion

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