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

From: Avi Weinberg <AviW(at)gilat(dot)com>
To: "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: postgres_fdw FAST "where id in (140,144,148)" SLOW: "where id in (select 140 as id union select 144 union select 148)"
Date: 2022-01-03 16:26:33
Message-ID: DB9PR07MB7180EDC1ACD3222532399E1CCB499@DB9PR07MB7180.eurprd07.prod.outlook.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Browse pgsql-general by date

  From Date Subject
Next Message Matthias Apitz 2022-01-04 08:30:01 restoring a single database from a pg_dumpall dump file
Previous Message Tom Lane 2022-01-03 16:18:09 Re: VACUUM FULL missing chunk number 0 for toast value