Uwe Schroeder <uwe@xxxxxxxxx> writes: > I have a 8.3 database and decided for various reasons to upgrade to 8.4. I also tried 9.0 - same results. > On the exactly same hardware with the exactly same configuration, some queries perform a factor of 100000 slower on 8.4 and 9.0 than on 8.3 Hmm ... the problem here seems to come from the much poorer size estimate from the t/ugt antijoin: > -> Hash Left Join (cost=71.37..2445.93 rows=52 width=9) (actual time=0.791..2.017 rows=104 loops=1) > Hash Cond: ((subplan) = ugt.user_id) > Filter: (ugt.group_id IS NULL) > -> Hash Anti Join (cost=62.53..2432.89 rows=1 width=9) (actual time=0.639..2.976 rows=105 loops=1) > Hash Cond: ((SubPlan 2) = ugt.user_id) Now, 8.3 has no idea what it's doing here, and it's just luck that it produces an estimate that's only a factor of 2 off from reality. 8.4 actually understands that it's dealing with an antijoin, and with that greater knowledge it produces ... a much worse estimate :-(. And that sends it into the weeds about how to perform the higher-level join. While I don't have your specific example to try, I did some experimenting with queries of this form, and I noticed that 8.4's heuristic in eqjoinsel_semi() was going completely nuts and estimating that all rows in the lefthand side have join partners (thus, no rows out of the antijoin). This is because it has stats for one side of the comparison operator but not the other side (the one with the sub-select). But it's taking the totally-made-up ndistinct estimate for the sub-select at face value. It needs to be a bit warier I think. In general you might want to think about whether you can recast this query to avoid the sub-selects in the join conditions. The planner isn't terribly bright about sub-selects in the first place, and you're putting them in places where quality estimates are really critical to getting a good plan. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
