postgres_fdw FAST "where id in (140,144,148)" SLOW: "where id in (select 140 as id union select 144 union select 148)"

[Avi Weinberg <AviW@xxxxxxxxx>]

Hi

 

I have postgres_fdw table called tbl_link.  The source table is 2.5 GB in size with 122 lines (some lines has 70MB bytea column, but not the ones I select in the example)

I noticed that when I put the specific ids in the list "where id in (140,144,148)" it works fast (few ms), but when I put the same list as select "where id in (select 140 as id union select 144  union select 148)" it takes 50 seconds.  This select union is just for the example, I obviously have a different select (which by itself takes few ms but cause the whole insert query to take 10000x more time)

 

Why is that?  How can I still use regular select and still get reasonable response time?

 

Thanks

 

 

FAST:

select lnk.*

into local_1

from tbl_link lnk

where id in (140,144,148)

 

"Foreign Scan on tbl_link lnk  (cost=100.00..111.61 rows=3 width=700) (actual time=4.161..4.167 rows=3 loops=1)"

"Planning Time: 0.213 ms"

"Execution Time: 16.251 ms"

 

 

 

SLOW:

select lnk.*

into local_1

from tbl_link lnk

where id in (select 140 as id union select 144  union select 148)

 

 

"Hash Join  (cost=100.18..113.88 rows=3 width=700) (actual time=45398.721..46812.100 rows=3 loops=1)"

"  Hash Cond: (lnk.id = (140))"

"  ->  Foreign Scan on tbl_link lnk  (cost=100.00..113.39 rows=113 width=700) (actual time=45398.680..46812.026 rows=112 loops=1)"

"  ->  Hash  (cost=0.14..0.14 rows=3 width=4) (actual time=0.023..0.026 rows=3 loops=1)"

"        Buckets: 1024  Batches: 1  Memory Usage: 9kB"

"        ->  HashAggregate  (cost=0.08..0.11 rows=3 width=4) (actual time=0.017..0.021 rows=3 loops=1)"

"              Group Key: (140)"

"              Batches: 1  Memory Usage: 24kB"

"              ->  Append  (cost=0.00..0.07 rows=3 width=4) (actual time=0.005..0.009 rows=3 loops=1)"

"                    ->  Result  (cost=0.00..0.01 rows=1 width=4) (actual time=0.003..0.004 rows=1 loops=1)"

"                    ->  Result  (cost=0.00..0.01 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)"

"                    ->  Result  (cost=0.00..0.01 rows=1 width=4) (actual time=0.000..0.001 rows=1 loops=1)"

"Planning Time: 0.541 ms"

"Execution Time: 46827.945 ms"

 

 

 

FAST

 

 

IMPORTANT - This email and any attachments is intended for the above named addressee(s), and may contain information which is confidential or privileged. If you are not the intended recipient, please inform the sender immediately and delete this email: you should not copy or use this e-mail for any purpose nor disclose its contents to any person.