pg_hint_plan does not work with postgres_query

[mwisnicki]

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?

• 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

[Mytherin]

Thanks for the report!

Comments are not stripped, but the query is not executed "as-is" but passed into a binary copy. You can use pg_debug_show_queries to print the queries that get sent to the server:

D SET pg_debug_show_queries=true;
D SELECT * FROM postgres_query(s, $$SELECT /* Comment */ 42$$);
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ READ ONLY

COMMIT

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ READ ONLY


	COPY (SELECT "?column?" FROM (SELECT /* Comment */ 42) AS __unnamed_subquery ) TO STDOUT (FORMAT "binary");
	

COMMIT

┌──────────┐
│ ?column? │
│  int32   │
├──────────┤
│       42 │
└──────────┘

As you can see, the comment is in the original query - however the query is wrapped inside a subquery.

Perhaps the problem here is that pg_hint_plan does not work inside COPY statements?