PostgreSQL Update Query Hangs

postgresqlpostgresql-performance

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:

enter image description here

DDL Statement:

enter image description here

enter image description here

ANALYZE explain output:

enter image description here

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:

"QUERY PLAN"
"Update on ""CaseProductUnderwriting""  (cost=0.00..2421622.16 rows=1 width=652) (actual time=14339.234..14339.235 rows=0 loops=1)"
"  Buffers: shared read=1644192"
"  ->  Seq Scan on ""CaseProductUnderwriting""  (cost=0.00..2421622.16 rows=1 width=652) (actual time=14339.228..14339.229 rows=0 loops=1)"
"        Filter: (""CaseProductUnderwritingPK"" = '8908693'::bigint)"
"        Rows Removed by Filter: 62196975"
"        Buffers: shared read=1644192"
"Planning Time: 1.421 ms"
"Execution Time: 14339.377 ms"

Please note that the same UPDATE is not moving forward.

EXPLAIN (ANALYZE, BUFFERS) Update "QUERY PLAN"
"Update on ""CaseProductUnderwriting"" (cost=0.00..2421622.16 rows=1 width=652) (actual time=14339.234..14339.235 rows=0 loops=1)"
" Buffers: shared read=1644192" " -> 
Seq Scan on ""CaseProductUnderwriting"" (cost=0.00..2421622.16 rows=1 width=652) (actual time=14339.228..14339.229 rows=0 loops=1)" 
" Filter: (""CaseProductUnderwritingPK"" = '8908693'::bigint)" 
" Rows Removed by Filter: 62196975" 
" Buffers: shared read=1644192" "Planning Time: 1.421 ms" 
"Execution Time: 14339.377 ms"

Best Answer

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 "CaseProductUnderwritingPK".

Related Question