Hi,
I'm seeing some interesting behaviour in postgres when I inserted some 50000 records into a table in 2 minutes.

After the insertion a certain query on the table which also makes joins to several other tables is hanging on for ever and postgres consumes 98% of the cpu; however, postgres is answering queries on other tables and even simple queries on the table into which the bulk insertion happened.

We observed that the query is not even put in the execution que, and is getting stuck some where in the parser stage,I guess.

However, when we trried running the same query after keeping the system idle for two days, to our surprise it worked pretty quickly( 2 seconds).


After some googling, we figured out that running 'analyze' or 'vacuum' immediately after bulk insertion solves the hang issue.


My questions: Is this a known limitation of Postgres? Should we run this vacuum or analyze on tables everytime when we execute bulk insertion?
Or can we enable Autvauum? If we have to enable autovacuum, what is the impact on the application performance and/or transaction handling?

Can some one help me in dealing with the situation mentioned above?

Thanks and Regards,
Samba

Normally a vacuum is only needed when you are doing bulk deletes from the table (it essentially reclaims dead space) not after running inserts.

The analyze part of the vacuum could be the reason for this. If the table was empty before then PG might choose the wrong execution plan (but then 50000 rows is a very small table anyway).

It would have been interesting to see the execution plan (by running EXPLAIN) right after the insert

"Limit (cost=1463.41..1463.43 rows=8 width=37427)"
" -> Sort (cost=1463.41..1463.43 rows=8 width=37427)"
" Sort Key: lower((this_.displayname)::text)"
" -> Hash Left Join (cost=13.15..1463.29 rows=8 width=37427)"
" Hash Cond: (xyz6_.aserwerweid = abcstat16_.id)"
" -> Nested Loop Left Join (cost=0.00..1450.01 rows=8 width=36903)"
" -> Nested Loop Left Join (cost=0.00..1447.54 rows=8 width=35347)"
" -> Nested Loop Left Join (cost=0.00..1419.05 rows=1 width=35331)"
" -> Nested Loop Left Join (cost=0.00..1410.77 rows=1 width=34807)"
" Join Filter: (abc10_.id = abclogi11_.abcid)"
" -> Nested Loop Left Join (cost=0.00..1399.19 rows=1 width=33719)"
" -> Nested Loop Left Join (cost=0.00..1390.91 rows=1 width=33195)"
" Join Filter: (xyz6_.abcid = abc10_.id)"
" -> Nested Loop Left Join (cost=0.00..1382.63 rows=1 width=23107)"
" -> Nested Loop (cost=0.00..1374.35 rows=1 width=22583)"
" -> Nested Loop Left Join (cost=0.00..1366.07 rows=1 width=22059)"
" -> Nested Loop Left Join (cost=0.00..1365.42 rows=1 width=20491)"
" Join Filter: (jkl3_.id = ghjkghjkjhkhj4_.fdgid)"
" -> Nested Loop Left Join (cost=0.00..1352.50 rows=1 width=19939)"
" -> Nested Loop Left Join (cost=0.00..1351.26 rows=1 width=19415)"
" Join Filter: (xyz6_.id = etefhfgjdghjghjadd7_.dfghfghsfgid)"
" -> Nested Loop Left Join (cost=0.00..1340.13 rows=1 width=17823)"
" Join Filter: (jkl3_.dfghsfgdsafwafid = xyz6_.id)"
" -> Nested Loop Left Join (cost=0.00..19.78 rows=1 width=10644)"
" Join Filter: (this_.id = jkl3_.vbncvbcvbmbvnmcvbid)"
" -> Index Scan using abc_pkey on abc this_ (cost=0.00..8.27 rows=1 width=10088)"
" Filter: (NOT isdeleted)"
" -> Seq Scan on fdg jkl3_ (cost=0.00..11.50 rows=1 width=556)"
" Filter: ((discriminator)::text = 'UE'::text)"
" -> Append (cost=0.00..1320.21 rows=11 width=7179)"
" -> Subquery Scan "*SELECT* 1" (cost=0.00..10.20 rows=10 width=7179)"
" -> Seq Scan on dfghfghsfg (cost=0.00..10.10 rows=10 width=7319)"
" -> Subquery Scan "*SELECT* 2" (cost=0.00..1310.01 rows=1 width=7179)"
" -> Seq Scan on abc (cost=0.00..1310.00 rows=1 width=10088)"
" -> Seq Scan on ghjghjghj etefhfgjdghjghjadd7_ (cost=0.00..10.50 rows=50 width=1592)"
" -> Index Scan using ert_pkey on ert dfghfghsfgc8_ (cost=0.00..1.23 rows=1 width=524)"
" Index Cond: (etefhfgjdghjghjadd7_.ghjghjghjcategoryid = dfghfghsfgc8_.id)"
" -> Seq Scan on yuiyuiouykyuk ghjkghjkjhkhj4_ (cost=0.00..11.30 rows=130 width=552)"
" -> Index Scan using jhkljkljhkl_pkey on jhkljkljhkl ghjkghjkjhkhj5_ (cost=0.00..0.64 rows=1 width=1568)"
" Index Cond: (ghjkghjkjhkhj4_.jhkljkljhklid = ghjkghjkjhkhj5_.id)"
" -> Index Scan using fdghfdghfgh_pkey on fdghfdghfgh rtyiyuiuyi2_ (cost=0.00..8.27 rows=1 width=524)"
" Index Cond: (this_.werwereid = rtyiyuiuyi2_.id)"
" -> Index Scan using fdghfdghfgh_pkey on fdghfdghfgh rtyiyuiuyi15_ (cost=0.00..8.27 rows=1 width=524)"
" Index Cond: (xyz6_.werwereid = rtyiyuiuyi15_.id)"
" -> Index Scan using abc_pkey on abc abc10_ (cost=0.00..8.27 rows=1 width=10088)"
" -> Index Scan using abcghjghjdghjdgh_pkey on abcghjghjdghjdgh abcstat12_ (cost=0.00..8.27 rows=1 width=524)"
" Index Cond: (abc10_.aserwerweid = abcstat12_.id)"
" -> Seq Scan on abcloginsession abclogi11_ (cost=0.00..10.70 rows=70 width=1088)"
" -> Index Scan using rtuyrttyurty_pkey on rtuyrttyurty dfghfghsfgt9_ (cost=0.00..8.27 rows=1 width=524)"
" Index Cond: (etefhfgjdghjghjadd7_.ghjghjghjtypeid = dfghfghsfgt9_.id)"
" -> Index Scan using abcusertypeassign_pkey on abcusertypeassign abctype13_ (cost=0.00..28.39 rows=8 width=16)"
" Index Cond: (abc10_.id = abctype13_.abcid)"
" -> Index Scan using abctypeenum_pkey on abctypeenum abctype14_ (cost=0.00..0.30 rows=1 width=1564)"
" Index Cond: (abctype13_.abctypeid = abctype14_.id)"
" -> Hash (cost=11.40..11.40 rows=140 width=524)"
" -> Seq Scan on abcghjghjdghjdgh abcstat16_ (cost=0.00..11.40 rows=140 width=524)"

What about the row estimates? Are they correct? If not, then analyze is indeed required.