Re: Performance tuning?

Robert Fitzpatrick <lists@webtent.net> writes:
> On Sat, 2007-05-19 at 17:05 -0400, Tom Lane wrote:
>> Show us the table definitions and the EXPLAIN ANALYZE output, please.

There seem to be a couple of problems visible in the EXPLAIN output:

>              ->  Nested Loop  (cost=53060.03..53565.72 rows=1 width=1040) (actual time=36584.031..37402.166 rows=1017
loops=1)
>                    Join Filter: ("outer".fldcontactnumber = ("inner".fldcontactnumber)::numeric)

>                                ->  Nested Loop  (cost=0.00..50403.74 rows=3008 width=189) (actual
time=6.180..36110.024rows=6167 loops=1) 
>                                      Join Filter: (("outer".fldclientnumber)::numeric = "inner".fldclientnumber)

You're comparing fields of distinct types, which not only incurs
run-time type conversions but can interfere with the ability to
use some plan types at all.  Looking at the table definitions,
you've got primary keys declared as SERIAL (ie, integer) and the
referencing columns declared as NUMERIC(18,0).  This is just horrid for
performance :-( --- NUMERIC arithmetic is pretty slow, and it's really
pointless when the referenced columns are only integers.  I suspect
you should have translated these column types as BIGINT (and BIGSERIAL).

>                    ->  Merge Join  (cost=53060.03..53087.19 rows=1 width=210) (actual time=36561.298..36603.979
rows=1873loops=1) 
>                          Merge Cond: (("outer".fldclientnumber = "inner".fldclientnumber) AND
("outer".fldcontactnumber= "inner".fldcontactnumber)) 

The planner is drastically underestimating the number of rows out of
this join, probably because it does not know that there is any
correlation between fldclientnumber and fldcontactnumber, where in
reality I bet there's a lot.  Is it possible that one of these fields is
actually functionally dependent on the other, such that you could use
just one of them in the join?  The one-result-row estimate is bad
because it leads to inappropriate choices of nestloop joins.

There may not be much you can do about that part, but I suspect if you
get rid of the pointless use of NUMERIC arithmetic you'll find a lot
of the performance issue goes away.

Another thing that might be worth fixing is the rather silly use of '%%%'
rather than '%' for a no-op LIKE pattern.  It looks like the planner's
LIKE-estimator gets fooled by that and doesn't realize it's a
match-everything pattern.  (Yeah, we should fix that, but it won't
help you today...)  Again, underestimating the number of rows is bad
for the quality of the plan.

            regards, tom lane