Search code examples
postgresqljoinsql-update

simple postgresql query taking forever

Most of my queries are done in max 3 secs. Even if they are much more complicated. This one takes forever (at leas I quit after 10 mins):

UPDATE public.url
    SET 
        last_seen_on = NOW()
    FROM public.temp_url AS B
    INNER JOIN public.url AS A
    ON A.url_path = B.url_path;

Is there a problem with the query? I'm not getting any error messages. EXPLAIN:

Update on url  (cost=28512.96..4035340577.80 rows=0 width=0)
  ->  Nested Loop  (cost=28512.96..4035340577.80 rows=188508798976 width=26)
        ->  Hash Join  (cost=28512.96..53744.52 rows=434176 width=12)
              Hash Cond: (b.url_path = a.url_path)
              ->  Seq Scan on temp_url b  (cost=0.00..12641.82 rows=434182 width=43)
              ->  Hash  (cost=19269.76..19269.76 rows=434176 width=42)
                    ->  Seq Scan on url a  (cost=0.00..19269.76 rows=434176 width=42)
        ->  Materialize  (cost=0.00..23136.64 rows=434176 width=6)
              ->  Seq Scan on url  (cost=0.00..19269.76 rows=434176 width=6)
JIT:
  Functions: 13
  Options: Inlining true, Optimization true, Expressions true, Deforming true
Solution

  • Most likely not the correct query since you're using url twice and also without a condition.

    My best bet is that you need this query:

    UPDATE public.url
    SET 
        last_seen_on = NOW()
    FROM public.temp_url
WHERE url.url_path = temp_url.url_path;

    An index on both columns url_path might be beneficial.