Pallav Kalva <pkalva@xxxxxxxxxxxxxxxxx> writes: > We have recently upgraded our production database from 8.0.12 to > 8.2.4, We have seen lot of improvements on 8.2.4 side but we are also > seeing some queries which are slow. > Particularly this below query is really bad in 8.2.4 , I can get > only the explain on this as explain analyze never finishes even after 20 > min. What it's doing is scanning backward on activity1_.activityid and hoping to find a row that matches all the other constraints soon enough to make that faster than any other way of doing the query. 8.0 would have done the same thing, I believe, if the statistics looked favorable for it. So I wonder if you've forgotten to re-ANALYZE your data since migrating (a pg_dump script won't do this for you). > -> Index Scan using idx_accountactivity_fkactivityid on > accountactivity accountact0_ (cost=0.00..3.94 rows=1 width=16) > Index Cond: (accountact0_.fkactivityid = > activity1_.activityid) > Filter: (fkaccountid = 1455437) > -> Index Scan using > idx_accountactivity_fkaccountid on accountactivity accountact0_ > (cost=0.00..1641.42 rows=1343 width=16) (actual time=115.348..864.416 > rows=10302 loops=1) > Index Cond: (fkaccountid = 1455437) The discrepancy in rowcount estimates here is pretty damning. Even the 8.0 estimate wasn't really very good --- you might want to consider increasing default_statistics_target. regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
