I need to optimize this query, or change some configuration, because execution takes too much time.
with rows as ( update hotmail.clean_us_usdataclean_1 set offers_excluded=case when offers_excluded!='' and offers_excluded is not null and offers_excluded not like '%,80' and offers_excluded not like '80,%' and offers_excluded!='80' then offers_excluded || ',' || '80' else '80' END where md5(email::text) in (select email from supp.xxc4u0p5ce74l8co5) RETURNING 1 ) SELECT count(*) FROM rows;
I have a php script that runs through all the tables in the schema, and does the same thing. When dealing with 2m-8m it takes around 30 min to complete, but when I try it with 59m it is so slow, and seems to get stuck on some tables.
Aggregate (cost=1151032.31..1151032.32 rows=1 width=8) (actual time=143740.697..143740.698 rows=1 loops=1) CTE rows -> Update on clean_us_usdataclean_1 (cost=1124740.33..1145407.31 rows=250000 width=507) (actual time=140845.110..143656.250 rows=51095 loops=1) -> Hash Join (cost=1124740.33..1145407.31 rows=250000 width=507) (actual time=140844.921..143462.910 rows=51095 loops=1) Hash Cond: (md5(clean_us_usdataclean_1.email) = (xxqzu5mbuoz3kypgtrono4n1l.email)::text) -> Seq Scan on clean_us_usdataclean_1 (cost=0.00..9416.99 rows=499999 width=472) (actual time=0.021..361.794 rows=499999 loops=1) -> Hash (cost=1124368.51..1124368.51 rows=29746 width=22) (actual time=140843.991..140843.991 rows=21646140 loops=1) Buckets: 8388608 (originally 32768) Batches: 2 (originally 1) Memory Usage: 815899kB -> HashAggregate (cost=1124071.05..1124368.51 rows=29746 width=22) (actual time=101204.068..124867.571 rows=21646140 loops=1) Group Key: (xxqzu5mbuoz3kypgtrono4n1l.email)::text -> Seq Scan on xxqzu5mbuoz3kypgtrono4n1l (cost=0.00..975465.04 rows=59442404 width=22) (actual time=0.157..33816.356 rows=59442522 loops=1) -> CTE Scan on rows (cost=0.00..5000.00 rows=250000 width=0) (actual time=140845.117..143716.370 rows=51095 loops=1) Planning time: 1.111 ms Execution time: 143847.156 ms