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
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. |