Re: Database performance problem

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.