Tom Lane wrote:
> I bet the problem is that you're not getting a hashed subplan in 8.1.
> What do you have work_mem set to on the two systems? The fact that the
> rowcount estimate for the subplan is much larger may be causing the
> change, too. Why is that --- perhaps you haven't ANALYZEd stud_vera
> recently on one system or the other? How many rows will really come out
> of the sub-select (ie, what's "select count(*) from stud_vera
> where veraid = 2")?
HEUREKA...
Increasing the work_mem had solved the problem. Result: 3170 rows in
1,487.927ms.
Working_mem was set to the default of 1MB. Increasing it to 4 solved it.
Are there any suggestions for the size. Maybe depending on database size?
Many thanks!
But I still wonder why it didn't work with the lower size of working_mem
and the temporary file on the disk? I had a similar problem in the past
where I forget to ANALYZE and before ANALYZEing also a temporary file
was used. The time difference had been from ten seconds to five minutes
and not from one second to over 40 hours. The temporary file is created
and has a size of around 2MB and doesn't change size while working. I
fear to run into the same problem, if I use more complex queries.
And, the problem with the estimated rows is still very interesting. The
Table is FULLy ANALYZEd and the actual count of stud_vera with verid=2
is 49176, so the 53000 are real close. Don't now, why it is so low on 8.0.8?
One other interesting thing is that the query plan has changed after
increasing the working mem:
Hash Join (cost=12991.28..21472.83 rows=7512 width=4)
Hash Cond: ("outer".sid = "inner".sid)
-> Bitmap Heap Scan on stud_vera v (cost=1841.02..5834.80 rows=15023
width=4)
Recheck Cond: (veraid = 34)
-> Bitmap Index Scan on stud_vera_sid_veraid_idx
(cost=0.00..1841.02 rows=15023 width=0)
Index Cond: (veraid = 34)
-> Hash (cost=11008.74..11008.74 rows=56607 width=4)
-> Seq Scan on stud s (cost=7617.57..11008.74 rows=56607 width=4)
Filter: (NOT (hashed subplan))
SubPlan
-> Seq Scan on stud_vera (cost=0.00..7488.20
rows=51747 width=4)
Filter: (veraid = 2)
*greets*
Kai
