Hi,
I have a question regarding the use of indices when querying foreign data tables (using postgres_fdw of 9.3 to another
postgresDB).
Everything works fine beside the fact that the indices which are defined in the foreign DB are not used at all when I
doquery 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
alsopost 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
