Indices and Foreign Tables

From: "Kohler Manuel (ID SIS)" <manuel(dot)kohler(at)id(dot)ethz(dot)ch>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Indices and Foreign Tables
Date: 2014-04-04 11:24:51
Message-ID: 28DD87F9-7BEB-438D-8D3D-5E51E7D1184B@bsse.ethz.ch
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,
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?
Any hints are appreciated.
Cheers
Manuel

--
Kohler Manuel (ID SIS)
Research Informatics, Scientific IT Services (ID ETHZ)
Quantitative Genomics Facility (QGF), D-BSSE
ETH Zurich, Mattenstr. 26 (1078 1.02), CH-4058 Basel, +41 61 387 3132

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Oleg Bartunov 2014-04-04 12:04:43 Re: hstore - jsonb
Previous Message Pavel Stehule 2014-04-04 10:23:12 Re: create temp view from function inside plpgsql function.