So I’ve tables in two completely different databases that I would like to investigate collectively. I’m utilizing fdw to take action. Nevertheless I’m working in to bizarre habits with my joins.
Have in mind the next snippets are simply accessing the overseas database.
After I do:
SELECT * FROM tds tr INNER JOIN dq_infos dq ON tr.id = dq.tid WHERE tr.created_time >= '2020-04-22'
it runs very quick, nevertheless the results of this be a part of can have duplicated columns so I can not actually do something with it.
When as a substitute do:
SELECT tr.tid FROM tds tr INNER JOIN dq_infos dq ON tr.id = dq.tid WHERE tr.created_time >= '2020-04-22'
it takes roughly 10 occasions as lengthy.
I assumed this cannot be proper so I appeared on the question plan and it makes even much less sense.
The question plan for the primary question is:
International Scan (value=100.00..205.11 rows=78 width=788) Relations: (public.tds tr) INNER JOIN (public.vq_infos vq)
And the question plan for the second question is:
Merge Be part of (value=580.61..799.37 rows=14259 width=4) Merge Cond: (tr.id = vq.tid) -> Type (value=214.47..216.91 rows=975 width=4) Type Key: tr.id -> International Scan on tds tr (value=100.00..166.06 rows=975 width=4) -> Type (value=366.15..373.46 rows=2925 width=4) Type Key: vq.tid -> International Scan on vq_infos vq (value=100.00..197.75 rows=2925 width=4)
That is clearly the explanation for it being slower although I can not work out why it will do that.