Re: Indices and Foreign Tables

Kohler Manuel wrote:
> I have a question regarding the use of indices when querying foreign data tables (using postgres_fdw
> of 9.3 to another postgres DB).
> Everything works fine beside the fact that the indices which are defined in the foreign DB are not
> used at all when I do query it through the foreign data wrapper. This leads to an extremely slow query
> performance.
>
> Here is what I did:
>
> CREATE EXTENSION postgres_fdw;
>
> CREATE SERVER app_db
> FOREIGN DATA WRAPPER postgres_fdw
> OPTIONS (dbname ‘<DBNAME>', host 'localhost');
>
> CREATE USER MAPPING for openbis
> SERVER app_db
> OPTIONS (user ‘<USRE>', password ‘<PW>');
>
> CREATE FOREIGN TABLE data_sets_fdw
> (
> id bigint,
> code code,
> location file_path
> )
> SERVER app_db OPTIONS (table_name 'data_sets’);
>
> The data_sets_fdw corresponds to the table in the other DB which is defined as this:
>
> db=> \d data_sets
> Table "public.data_sets"
> Column | Type | Modifiers
> ----------+-----------+--------------------------------------------------------
> id | bigint | not null default nextval('data_sets_id_seq'::regclass)
> code | code | not null
> location | file_path | not null
> Indexes:
> "data_sets_pkey" PRIMARY KEY, btree (id)
> "data_sets_code_key" UNIQUE CONSTRAINT, btree (code)
> "data_sets_code_idx" btree (code)
>
> When I realised that the queries are so slow I ran an EXPLAIN ANALYZE which shows that the indices
> are not used. I can also post the query plan if it helps.
>
> So am I doing something wrong here, or is this not possible that the indices are used by the fdw?

The indices should be used.

Did you ANALYZE the remote table on the remote database?
What is the remote query (EXPLAIN VERBOSE)?
What do you get for EXPLAIN ANALYZE of the remote query when executed on the remote database?

Yours,
Laurenz Albe