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@xxxxxxxxxxxxx] Sent: Tuesday, June 12, 2007 5:43 PM To: Porell, Chris Cc: 'pgsql-general@xxxxxxxxxxxxxx' Subject: Re: [GENERAL] Database performance problem "Porell, Chris" <Chris.Porell@xxxxxxxxxxxx> 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.
