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 
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)
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.

Query Plan

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
  • I'm unclear on what exactly is getting too slow - the EXPLAIN ANALYZE output you've posted seems to include the table with 59mil rows (xxqzu5mbuoz3kypgtrono4n1l has 59,442,522 rows) but only took 143847 milliseconds - 143 seconds, or 2m23s - to execute, a lot less than 30 minutes. Am I correct in understanding that the PHP bit swaps out table names, and this is the ANALYZE output for one of the queries that completed? – AdamKG Nov 2 '18 at 1:04
  • adamkg yes the php script loop through all database tables the odd thing is smaller table with like 30 record take more time than that 5 min to 10 min – larini Nov 2 '18 at 15:15
  • also when the update is running i see postmaster using 100% of CPU is that normal – larini Nov 2 '18 at 15:18

An index on md5(email) can probably help:

CREATE INDEX "clean_us_usdataclean_1_md5_email" 
  ON  hotmail.clean_us_usdataclean_1 (md5(email::text));

but will probably take a long time to build.

It won't work if the cast to text is not immutable (eg if email was a timestamptz)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.