2

I have a table on database temp_db named external_objects and an index on the field id, which is an MongoDB id (like 5458d717cd07870859000003). When I explain this select at this database, I got this

 explain verbose SELECT origin_id
    from external_objects 
    where id = '5458d717cd07870859000003'  


"Index Only Scan using eoid_lookup_tmp_ix1 on public.external_objects  (cost=0.56..8.57 rows=1 width=16)"
"  Output: origin_id"
"  Index Cond: (external_objects.id = '5458d717cd07870859000003'::text)"

I connected this table to another database, via postgres_fdw with 

-- Creating external_objects table
CREATE FOREIGN TABLE external_objects_mosql
(
  id text NOT NULL,
  created_at date,
  origin_id text,
  _extra_props text
) SERVER mosql_data  OPTIONS (table_name 'external_objects', schema_name 'public');

on the staging server, but when I try explain the same query on this foreign table, I see this

 explain verbose SELECT external_objects_mosql.origin_id
    from external_objects_mosql 
    where external_objects_mosql.id = '5458d717cd07870859000003'


"Foreign Scan on public.external_objects_mosql  (cost=100.00..147085.60 rows=1 width=16)"
"  Output: origin_id"
"  Remote SQL: SELECT origin_id FROM public.external_objects WHERE ((id = '5458d717cd07870859000003'::text))"

Seems like that when using postgres_fdw, I'm unable to use indexes, and this is costing me too much time. is there any workaround for this?

Improve this question
6
  • Here you see a foreign scan - I think it does not tell you if it uses an index or not. I would expect it does and you lose the time on network latency or elsewhere. Please add the output of EXPLAIN ANALYZE for both queries. Commented Jun 4, 2015 at 18:37
  • @dezso right away: pastebin.com/NbDP6yE7 Commented Jun 4, 2015 at 18:42
  • You can edit your question with those. Anyway, there is only a half millisecond difference between the two - is there a reason (say, a huge dataset in the future) why you are worried about it? Commented Jun 4, 2015 at 19:09
  • I have 3 millions records in this origin table, and when it comes to create this table on staging with the records of origin table, it takes too much time (and this table will grow soon) Commented Jun 4, 2015 at 19:11
  • I think that is a different issue. But how often do you need to create this foreign table? Isn't it a one-time operation? Commented Jun 4, 2015 at 20:22

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.