Same query 10000x More Time

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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"

 

 

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.

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux