Re: Database performance problem

"Porell, Chris" <Chris(dot)Porell(at)ceridian(dot)com> writes:
> Lastly, the EXPLAIN ANALYZE output.

Do you have the equivalent for the old installation?

> -> Nested Loop (cost=4387.04..9817.54 rows=1 width=4) (actual time=1134.020..160195.837 rows=1842 loops=1)
> Join Filter: (("inner".recordnumber = "outer".recordnumber) AND ("outer".aaaa < ("inner".aaaa - 1::numeric)))
> -> Hash Join (cost=4387.04..9796.71 rows=1 width=56) (actual time=684.721..1057.800 rows=4816 loops=1)
> ...
> -> Function Scan on aaaaresults (cost=0.00..15.00 rows=333 width=36) (actual time=0.087..18.696 rows=11306 loops=4816)
> Filter: (aaaa >= 25::numeric)
> Total runtime: 160202.265 ms

This join is what's killing you, and even more specifically the factor
of 4800 misestimate of the size of the hashjoin result. It wouldn't
have tried a nestloop if the rowcount estimate had been even a little
bit closer to reality. The misestimate seems to be mostly due to this
lower join:

> -> Hash Join (cost=3642.33..3659.85 rows=2 width=48) (actual time=559.069..581.084 rows=4816 loops=1)
> Hash Cond: ("outer".recordnumber = "inner".recordnumber)
> -> Function Scan on aaaaresults (cost=0.00..12.50 rows=1000 width=36) (actual time=271.933..277.842 rows=4817 loops=1)
> -> Hash (cost=3642.05..3642.05 rows=114 width=12) (actual time=287.113..287.113 rows=4918 loops=1)

I suppose this is not actually the same function that you are obscuring
in the other case? Anyway this seems a bit strange, because with no
stats on the functionscan result, I'd have expected a more conservative
(larger) estimate for the size of the join result. Can you show us the
pg_stats row for the column you've labeled inner.recordnumber here?

regards, tom lane