Indices and Foreign Tables
От | Kohler Manuel (ID SIS) |
---|---|
Тема | Indices and Foreign Tables |
Дата | |
Msg-id | 28DD87F9-7BEB-438D-8D3D-5E51E7D1184B@bsse.ethz.ch обсуждение исходный текст |
Ответы |
Re: Indices and Foreign Tables
|
Список | 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 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
В списке pgsql-general по дате отправления: