PostgreSQL Update Query Hangs I have an update query being fired from the Java Hibernate (for a single user), which works fine on a small dataset of rows in a table (100MB), but the same update query goes into a hung state if the number of records in the same table increases (12 GB).
I didn't find any blocking or deadlocks queries been executed.
I have performed autovacuum, reindex, analyze, increased shared buffers, set locktimeout, statement timeout values - but with no effect.
The update transaction doesn't move ahead only and never gets committed.
Output of the pg_locks table:

DDL Statement:


ANALYZE explain output:


I verified the database schemas on both databases and no difference was found. Doing the analyze, same plan is being used for both the databases but time for execution is slightly on the higher side for the database with large size. But the update gets committed when fired directly whereas through application it's waiting continuously.
Please find the output of EXPLAIN (ANALYZE, BUFFERS) update:
&quot;QUERY PLAN&quot;
&quot;Update on &quot;&quot;CaseProductUnderwriting&quot;&quot;  (cost=0.00..2421622.16 rows=1 width=652) (actual time=14339.234..14339.235 rows=0 loops=1)&quot;
&quot;  Buffers: shared read=1644192&quot;
&quot;  -&gt;  Seq Scan on &quot;&quot;CaseProductUnderwriting&quot;&quot;  (cost=0.00..2421622.16 rows=1 width=652) (actual time=14339.228..14339.229 rows=0 loops=1)&quot;
&quot;        Filter: (&quot;&quot;CaseProductUnderwritingPK&quot;&quot; = '8908693'::bigint)&quot;
&quot;        Rows Removed by Filter: 62196975&quot;
&quot;        Buffers: shared read=1644192&quot;
&quot;Planning Time: 1.421 ms&quot;
&quot;Execution Time: 14339.377 ms&quot;

Please note that the same UPDATE is not moving forward.
EXPLAIN (ANALYZE, BUFFERS) Update &quot;QUERY PLAN&quot;
&quot;Update on &quot;&quot;CaseProductUnderwriting&quot;&quot; (cost=0.00..2421622.16 rows=1 width=652) (actual time=14339.234..14339.235 rows=0 loops=1)&quot;
&quot; Buffers: shared read=1644192&quot; &quot; -&gt; 
Seq Scan on &quot;&quot;CaseProductUnderwriting&quot;&quot; (cost=0.00..2421622.16 rows=1 width=652) (actual time=14339.228..14339.229 rows=0 loops=1)&quot; 
&quot; Filter: (&quot;&quot;CaseProductUnderwritingPK&quot;&quot; = '8908693'::bigint)&quot; 
&quot; Rows Removed by Filter: 62196975&quot; 
&quot; Buffers: shared read=1644192&quot; &quot;Planning Time: 1.421 ms&quot; 
&quot;Execution Time: 14339.377 ms&quot;
If the execution plan that you posted is for the query that is too slow for you, the answer would be to create an index on &quot;CaseProductUnderwritingPK&quot;.