I will get that and post it.

NEW NEWS... turning off "enable_seqscan" made the query run in about .25
seconds!!!

Now we're re-evaluating effective_cache_size


-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Tuesday, June 12, 2007 5:43 PM
To: Porell, Chris
Cc: 'pgsql-general@postgresql.org'
Subject: Re: [GENERAL] Database performance problem


"Porell, Chris" <Chris.Porell@ceridian.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
-----------------------------------------
IMPORTANT: The sender intends that this electronic message is for
exclusive use by the person to whom it is addressed. This message
may contain information that is confidential or privileged and
exempt from disclosure under applicable law. If the reader of this
message is not an intended recipient, be aware that any disclosure,
dissemination, distribution or copying of this communication, or
the use of its contents, is prohibited. If you have received this
message in error, please immediately notify the sender of your
inadvertent receipt and delete this message from all data storage
systems. Thank you.

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

"Porell, Chris" <Chris.Porell@ceridian.com> writes:
> NEW NEWS... turning off "enable_seqscan" made the query run in about .25
> seconds!!!

[ squint... ] It was not the seqscans that were killing you, and
changing just that setting wouldn't have moved the rowcount estimates
one millimeter. I suppose this made it switch to some differently
shaped plan that happened not to evaluate the functionscan so many
times, but you should realize that what you got there is luck, not a
trustworthy fix.

BTW, on reflection the functionscan in itself shouldn't have been
real expensive, because it would have materialized the function result
in a tuplestore and then just rescanned that N times. I think the
actual expense came from evaluating the (aaaa >= 25::numeric) filter
condition over and over --- not only is numeric arithmetic pretty slow,
but it might have done it vastly more than 11306 * 4816 times. We can't
tell from this output how selective the filter was, but there could have
been a lot more than 11306 rows in the raw function output. You might
want to think about adjusting the function definition so that the
min-value filtering happens inside the function instead of outside.
For that matter, do you really need the value to be numeric rather than
plain integer?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq