Thread: Performance tuning?

We have a MS SQL server db that we successfully migrated to pgsql 8.2
and I am now working on some views and notice select queries being very
slow. I have vacuumed last night and running the exact same query (with
minor changes to syntax of course), it runs in just a few seconds
compared to several minutes in pgsql. Since I've never dealt with MS
SQL, I wanted to ask here if this is expected performance for the type
of query *or* do I need to just learn how to properly tune my
performance on the pgsql server? Perhaps some commands or tests may help
me determine where issues may lie?

I am running the following query on a linux server with comparable
processor and memory as the windows server. The query was just taken
from the SQL server as is and adjusted teh syntax...the query only
returns 3 records, but several tables have tens of thousands of records,
the tblactivitytag table has over 100K...

SELECT distinct A.fldClientNumber as cNumber, A.fldClientName as cName,
 B.fldContactNumber as contactNumber, B.fldContactCity as cCity,
 B.fldContactState as cState, B.fldContactFirstName as contactFName,
 B.fldContactLastName as contactLName, B.fldContactEmail as ContactEmail,
 B.fldContactTitle as cTitle, B.fldContactPhone1_Num as B1Phonenumber,
 B.fldContactPhone4_Type as Num4Type, B.fldContactPhone4_Num as CellNum
FROM tblClientMaster A, tblContactInfo B,tblClientProductPreference C,
 tblClientRoomSize D,tblProductMaster F, tblClientProductRelation G,
 tblclientcomments H, tblgeopreference E ,tblClientActivityTag
WHERE
 A.fldClientNumber = B.fldClientNumber AND
 A.fldClientNumber = C.fldClientNumber AND
 A.fldClientNumber = D.fldClientNumber AND
 A.fldClientName ilike '%ADVISOR%' AND
 B.fldContactFirstName ilike '%%%' AND
 A.fldClientNumber = G.fldClientNumber AND
 G.fldProductNumber = F.fldProductNumber AND
 F.fldProductName ilike '%%%' AND
 A.fldClientNumber = H.fldClientNumber AND
 H.fldenable = 't' AND
 H.fldcontactnumber = b.fldcontactnumber AND
 H.fldClientcomments ilike '%%%' AND
 (A.fldBuyingStatus = 'Now' ) AND
 (A.fldSellingStatus = 'Now' ) AND
 (C.fldFullService = 't' ) AND
 (D.fldSize149 = 't' ) AND
 (E.fldW = 't' ) AND
 A.fldClientNumber = E.fldClientNumber AND
 A.fldclientnumber = tblClientActivityTag.fldclientnumber AND
 tblClientActivityTag.fldcontactnumber = b.fldcontactnumber AND
 tblClientActivityTag.fldcontactactivitytag like 'A%' AND
 b.fldcontactnumber in (select fldcontactnumber from tblclientcomments where tblclientcomments$
 A.fldEnable = 't' AND B.fldEnable = 't'
ORDER BY A.fldClientName, B.fldContactLastName;

--
Robert

Robert Fitzpatrick <lists@webtent.net> writes:
> I am running the following query on a linux server with comparable
> processor and memory as the windows server.

Show us the table definitions and the EXPLAIN ANALYZE output, please.

            regards, tom lane

On Sat, 2007-05-19 at 17:05 -0400, Tom Lane wrote:
> Robert Fitzpatrick <lists@webtent.net> writes:
> > I am running the following query on a linux server with comparable
> > processor and memory as the windows server.
>
> Show us the table definitions and the EXPLAIN ANALYZE output, please.
>

Thanks Tom...


---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Unique  (cost=2326081.07..2354383.40 rows=12445 width=998) (actual time=71931.967..71989.731 rows=3 loops=1)
   ->  Sort  (cost=2326081.07..2328258.17 rows=870841 width=998) (actual time=71931.959..71943.845 rows=9110 loops=1)
         Sort Key: a.fldclientname, b.fldcontactlastname, a.fldclientnumber, b.fldcontactnumber, b.fldcontactcity,
b.fldcontactstate,b.fldcontactfirstname, b.fldcontactemail, b.fldcontacttitle, b.fldcontactphone1_num,
b.fldcontactphone4_type,b.fldcontactphone4_num 
         ->  Merge Join  (cost=55798.98..60543.68 rows=870841 width=998) (actual time=46902.686..70218.041 rows=9110
loops=1)
               Merge Cond: ("outer".fldclientnumber = "inner".fldclientnumber)
               ->  Merge Join  (cost=679.89..4617.75 rows=224283 width=8) (actual time=17.104..74.653 rows=125 loops=1)
                     Merge Cond: ("outer".fldclientnumber = "inner".fldclientnumber)
                     ->  Index Scan using ix_tblgeopreference_fldclientnumber on tblgeopreference e  (cost=0.00..556.87
rows=6699width=4) (actual time=0.205..56.266 rows=143 loops=1) 
                           Filter: (fldw = true)
                     ->  Sort  (cost=679.89..696.63 rows=6696 width=4) (actual time=16.844..17.005 rows=247 loops=1)
                           Sort Key: c.fldclientnumber
                           ->  Seq Scan on tblclientproductpreference c  (cost=0.00..254.39 rows=6696 width=4) (actual
time=0.084..15.884rows=663 loops=1) 
                                 Filter: (fldfullservice = true)
               ->  Materialize  (cost=55119.09..55127.13 rows=804 width=1014) (actual time=46827.886..70028.280
rows=9110loops=1) 
                     ->  Merge Join  (cost=53060.03..55118.29 rows=804 width=1014) (actual time=46827.877..69956.976
rows=9110loops=1) 
                           Merge Cond: ("outer".fldclientnumber = "inner".fldclientnumber)
                           ->  Nested Loop  (cost=53060.03..54565.61 rows=24 width=1010) (actual
time=37189.898..69232.176rows=25048 loops=1) 
                                 ->  Nested Loop  (cost=53060.03..54420.94 rows=24 width=1014) (actual
time=37148.445..67472.468rows=25048 loops=1) 
                                       ->  Nested Loop IN Join  (cost=53060.03..53581.73 rows=1 width=1006) (actual
time=37129.788..66642.591rows=1017 loops=1) 
                                             Join Filter: ("inner".fldcontactnumber = "outer".fldcontactnumber)
                                             ->  Nested Loop  (cost=53060.03..53565.72 rows=1 width=1040) (actual
time=36584.031..37402.166rows=1017 loops=1) 
                                                   Join Filter: ("outer".fldcontactnumber =
("inner".fldcontactnumber)::numeric)
                                                   ->  Merge Join  (cost=53060.03..53087.19 rows=1 width=210) (actual
time=36561.298..36603.979rows=1873 loops=1) 
                                                         Merge Cond: (("outer".fldclientnumber =
"inner".fldclientnumber)AND ("outer".fldcontactnumber = "inner".fldcontactnumber)) 
                                                         ->  Sort  (cost=50577.52..50585.04 rows=3008 width=189)
(actualtime=36156.473..36159.932 rows=6167 loops=1) 
                                                               Sort Key: a.fldclientnumber, h.fldcontactnumber
                                                               ->  Nested Loop  (cost=0.00..50403.74 rows=3008
width=189)(actual time=6.180..36110.024 rows=6167 loops=1) 
                                                                     Join Filter: (("outer".fldclientnumber)::numeric =
"inner".fldclientnumber)
                                                                     ->  Seq Scan on tblclientmaster a
(cost=0.00..728.70rows=1 width=172) (actual time=0.680..197.224 rows=4 loops=1) 
                                                                           Filter: (((fldclientname)::text ~~*
'%ADVISOR%'::text)AND ((fldbuyingstatus)::text = 'Now'::text) AND ((fldsellingstatus)::text = 'Now'::text) AND
(fldenable= true)) 
                                                                     ->  Seq Scan on tblclientcomments h
(cost=0.00..40651.36rows=601579 width=34) (actual time=0.019..7026.388 rows=1202169 loops=4) 
                                                                           Filter: ((fldenable = true) AND
((fldclientcomments)::text~~* '%%%'::text)) 
                                                         ->  Sort  (cost=2482.51..2484.04 rows=611 width=21) (actual
time=404.670..407.975rows=2439 loops=1) 
                                                               Sort Key: tblclientactivitytag.fldclientnumber,
tblclientactivitytag.fldcontactnumber
                                                               ->  Seq Scan on tblclientactivitytag
(cost=0.00..2454.24rows=611 width=21) (actual time=22.805..400.266 rows=1389 loops=1) 
                                                                     Filter: ((fldcontactactivitytag)::text ~~
'A%'::text)
                                                   ->  Index Scan using ix_tblcontactinfo_fldclientnumber on
tblcontactinfob  (cost=0.00..477.43 rows=63 width=830) (actual time=0.031..0.371 rows=12 loops=1873) 
                                                         Index Cond: ("outer".fldclientnumber = b.fldclientnumber)
                                                         Filter: (((fldcontactfirstname)::text ~~* '%%%'::text) AND
(fldenable= true)) 
                                             ->  Seq Scan on tblclientcomments  (cost=0.00..40651.36 rows=601579
width=17)(actual time=0.014..17.342 rows=6912 loops=1017) 
                                                   Filter: (((fldproductcode)::text ~~* '%%%'::text) AND (fldenable =
true))
                                       ->  Index Scan using ix_tblclientproductrelation_fldclientnumber on
tblclientproductrelationg  (cost=0.00..835.90 rows=265 width=8) (actual time=0.053..0.461 rows=25 loops=1017) 
                                             Index Cond: (g.fldclientnumber = "outer".fldclientnumber)
                                 ->  Index Scan using pk_tblproperty on tblproductmaster f  (cost=0.00..6.02 rows=1
width=4)(actual time=0.049..0.053 rows=1 loops=25048) 
                                       Index Cond: ("outer".fldproductnumber = f.fldproductnumber)
                                       Filter: ((fldproductname)::text ~~* '%%%'::text)
                           ->  Index Scan using ix_tblclientroomsize_fldclientnumber on tblclientroomsize d
(cost=0.00..527.83rows=6698 width=4) (actual time=11.842..509.065 rows=9300 loops=1) 
                                 Filter: (fldsize149 = true)
 Total runtime: 71996.138 ms
(49 rows)

CREATE TABLE "public"."tblclientmaster" (
  "fldclientnumber" SERIAL,
  "fldclientname" VARCHAR(100),
  "fldclienttype" VARCHAR(50),
  "fldclientparentcompanyname_remove" VARCHAR(100),
  "fldclientparentcompanynumber" NUMERIC(18,0),
  "fldchildren" SMALLINT,
  "fldclientbuyerseller" VARCHAR(10),
  "fldterms" VARCHAR(50),
  "fldmaxdollars" VARCHAR(20),
  "fldmaxdownpayment" VARCHAR(20),
  "fldenable" BOOLEAN NOT NULL,
  "fldcreateddate" TIMESTAMP WITHOUT TIME ZONE,
  "fldcreatedby" VARCHAR(10),
  "fldlastupdateddate" TIMESTAMP WITHOUT TIME ZONE,
  "fldlastupdatedby" VARCHAR(10),
  "fldclientwebsite" VARCHAR(100),
  "fldbuyingstatus" VARCHAR(50),
  "fldsellingstatus" VARCHAR(50),
  "fldequitystatus" VARCHAR(50),
  "fldstatusdate" TIMESTAMP WITHOUT TIME ZONE,
  CONSTRAINT "pk_tblclientmaster" PRIMARY KEY("fldclientnumber")
) WITHOUT OIDS;

CREATE TABLE "public"."tblcontactinfo" (
  "fldclientnumber" INTEGER,
  "fldcontactnumber" SERIAL,
  "fldcontactfirstname" VARCHAR(50),
  "fldcontactlastname" VARCHAR(50),
  "fldcontactaddress1" VARCHAR(60),
  "fldcontactaddress2" VARCHAR(50),
  "fldcontactcity" VARCHAR(50),
  "fldcontactstate" VARCHAR(50),
  "fldcontactzipcode" VARCHAR(10),
  "fldclientname_remove" VARCHAR(100),
  "fldcontacttype" VARCHAR(150),
  "fldcontactsalutation" VARCHAR(50),
  "fldcontactdear" VARCHAR(50),
  "fldcontacttitle" VARCHAR(100),
  "fldcontactphone1_type" VARCHAR(50),
  "fldcontactphone1_num" VARCHAR(20),
  "fldcontactphone1_num_ext" VARCHAR(20),
  "fldcontactphone2_type" VARCHAR(50),
  "fldcontactphone2_num" VARCHAR(20),
  "fldcontactphone3_type" VARCHAR(50),
  "fldcontactphone3_num" VARCHAR(20),
  "fldcontactphone4_type" VARCHAR(50),
  "fldcontactphone4_num" VARCHAR(20),
  "fldcontactphone5_type" VARCHAR(50),
  "fldcontactphone5_num" VARCHAR(20),
  "fldcontactemail" VARCHAR(50) NOT NULL,
  "fldcontactwebsite" VARCHAR(75),
  "fldperscomments" VARCHAR(900),
  "fldassistant" VARCHAR(100),
  "fldhfcode" VARCHAR(50),
  "fldenable" BOOLEAN NOT NULL,
  "fldpreviousclientnumber" NUMERIC(18,0),
  "fldcreateddate" TIMESTAMP WITHOUT TIME ZONE,
  "fldcreatedby" VARCHAR(50),
  "fldlastupdateddate" TIMESTAMP WITHOUT TIME ZONE,
  "fldlastupdatedby" VARCHAR(50),
  "fldcontactbyemail" BOOLEAN NOT NULL,
  "fldcontactbyfax" BOOLEAN NOT NULL,
  "fldcontactbymail" BOOLEAN NOT NULL,
  "fldcontactbyphone" BOOLEAN NOT NULL,
  "fldcontactbycell" BOOLEAN NOT NULL,
  "fldcontactbypager" BOOLEAN NOT NULL,
  "fldcontactpassword" VARCHAR(8),
  CONSTRAINT "pk_tblcontactinfo" PRIMARY KEY("fldcontactnumber")
) WITHOUT OIDS;

CREATE INDEX "ix_tblcontactinfo_fldclientnumber" ON "public"."tblcontactinfo"
USING btree ("fldclientnumber");

CREATE INDEX "ix_tblcontactinfo_fldcontactcity" ON "public"."tblcontactinfo"
USING btree ("fldcontactcity");

CREATE INDEX "ix_tblcontactinfo_fldcontactstate" ON "public"."tblcontactinfo"
USING btree ("fldcontactstate");

CREATE INDEX "ix_tblcontactinfo_fldperscomments" ON "public"."tblcontactinfo"
USING btree ("fldperscomments");

CREATE TABLE "public"."tblclientproductpreference" (
  "fldclientnumber" INTEGER,
  "fldclientname_remove" VARCHAR(100),
  "fldfullservice" BOOLEAN NOT NULL,
  "fldlimitedservice" BOOLEAN NOT NULL,
  "fldallsuite" BOOLEAN NOT NULL,
  "fldbudget" BOOLEAN NOT NULL,
  "fldconference" BOOLEAN NOT NULL,
  "fldresort" BOOLEAN NOT NULL,
  "flddailyfee" BOOLEAN NOT NULL,
  "fldsemiprivate" BOOLEAN NOT NULL,
  "fldprivate" BOOLEAN NOT NULL,
  "fldmunicipal" BOOLEAN NOT NULL
) WITHOUT OIDS;

CREATE INDEX "ix_tblclientproductpreference" ON "public"."tblclientproductpreference"
USING btree ("fldclientnumber");

CREATE INDEX "ix_tblclientproductpreference_1" ON "public"."tblclientproductpreference"
USING btree ("fldclientnumber");

CREATE TABLE "public"."tblclientroomsize" (
  "fldclientname_remove" VARCHAR(100),
  "fldclientnumber" INTEGER,
  "fldsize149" BOOLEAN NOT NULL,
  "fldsize299" BOOLEAN NOT NULL,
  "fldsize449" BOOLEAN NOT NULL,
  "fldsize599" BOOLEAN NOT NULL,
  "fldsize600" BOOLEAN NOT NULL
) WITHOUT OIDS;

CREATE TABLE "public"."tblproductmaster" (
  "fldproductnumber" SERIAL,
  "fldproductname" VARCHAR(100),
  "fldclientname_rename" VARCHAR(100),
  "fldproductaddress1" VARCHAR(50),
  "fldproductcity" VARCHAR(50),
  "fldproductstate" VARCHAR(10),
  "fldproductzip" VARCHAR(10),
  "fldproducttype" VARCHAR(100),
  "fldproductcontact" VARCHAR(100),
  "fldproductcontacttitle" VARCHAR(50),
  "fldphone" VARCHAR(20),
  "fldunittype" VARCHAR(100),
  "fldamenities" VARCHAR(1000),
  "fldmeetingrooms" NUMERIC(18,0),
  "fldmgmtcompany" VARCHAR(100),
  "fldmeetingspacesf" VARCHAR(20),
  "fldproductcode" VARCHAR(50),
  "fldmgmtexpires" VARCHAR(50),
  "fldlenderfirstname" VARCHAR(50),
  "fldlenderlastname" VARCHAR(50),
  "fldlender" VARCHAR(50),
  "fldloanamount" VARCHAR(50),
  "fldloanmaturity" VARCHAR(50),
  "fldfilecode" VARCHAR(50),
  "fldforsale" VARCHAR(50) NOT NULL,
  "fldlastadr" VARCHAR(50),
  "fldlastoccupancy" VARCHAR(50),
  "fldadrperiod" VARCHAR(50),
  "fldnoofunits" NUMERIC(18,0),
  "fldgolflength" VARCHAR(50),
  "fldgolfyardage" VARCHAR(50),
  "fldfee" VARCHAR(50),
  "fldbuiltin" VARCHAR(50),
  "fldlocation" VARCHAR(50),
  "fldcounty" VARCHAR(50),
  "fldoriginaldate" TIMESTAMP WITHOUT TIME ZONE,
  "fldoriginaluser" VARCHAR(50),
  "fldlastupdatedate" TIMESTAMP WITHOUT TIME ZONE,
  "fldlastupdateuser" VARCHAR(50),
  "fldproductname_temp" VARCHAR(100),
  "fldsecondaryownernumber" NUMERIC(18,0),
  "fldmgmtcompanynumber" NUMERIC(18,0),
  "fldlendernumber" NUMERIC(18,0),
  "fldenable" BOOLEAN NOT NULL,
  "fldproductwebsite" VARCHAR(100),
  "str_market" VARCHAR(50),
  "str_tract" VARCHAR(50),
  "brand" VARCHAR(50),
  "parent" VARCHAR(50),
  "ext_stay" VARCHAR(50),
  "restaurant" VARCHAR(50),
  "yr_affl" VARCHAR(50),
  "ops_type" VARCHAR(50),
  "str_region" VARCHAR(50),
  "county" VARCHAR(50),
  "msa" VARCHAR(50),
  "tract_price_tier" VARCHAR(50),
  "impact_desg" VARCHAR(50),
  "str_location" VARCHAR(50),
  "market_price_level" VARCHAR(50),
  "chain_scale" VARCHAR(50),
  "chi_prop" VARCHAR(50),
  "str_code" VARCHAR(50),
  "str_reporting" VARCHAR(50),
  CONSTRAINT "pk_tblproperty" PRIMARY KEY("fldproductnumber")
) WITHOUT OIDS;

CREATE INDEX "ix_tblproductmaster_fldcity" ON "public"."tblproductmaster"
USING btree ("fldproductcity");

CREATE INDEX "ix_tblproductmaster_fldlendername" ON "public"."tblproductmaster"
USING btree ("fldlendernumber");

CREATE INDEX "ix_tblproductmaster_fldmeetingrooms" ON "public"."tblproductmaster"
USING btree ("fldmeetingrooms");

CREATE INDEX "ix_tblproductmaster_fldmgmtexpires" ON "public"."tblproductmaster"
USING btree ("fldmgmtexpires");

CREATE INDEX "ix_tblproductmaster_fldnoofunits" ON "public"."tblproductmaster"
USING btree ("fldnoofunits");

CREATE INDEX "ix_tblproductmaster_fldproductaddress1" ON "public"."tblproductmaster"
USING btree ("fldproductaddress1");

CREATE INDEX "ix_tblproductmaster_fldproductcode" ON "public"."tblproductmaster"
USING btree ("fldproductcode");

CREATE INDEX "ix_tblproductmaster_fldstate" ON "public"."tblproductmaster"
USING btree ("fldproductstate");

CREATE INDEX "ix_tblproductmaster_fldtype" ON "public"."tblproductmaster"
USING btree ("fldproducttype");

CREATE TABLE "public"."tblclientproductrelation" (
  "fldclientnumber" INTEGER,
  "fldclientname_remove" VARCHAR(100),
  "fldproductnumber" INTEGER,
  "fldproductname_remove" VARCHAR(100),
  "fldcontactlastname_remove" VARCHAR(50),
  "fldstatus" VARCHAR(50),
  "fldentrydate" TIMESTAMP WITHOUT TIME ZONE,
  "flduser" VARCHAR(50),
  "fldcontactnumber" NUMERIC(18,0),
  "fldpreviousclientnumber" NUMERIC(18,0)
) WITHOUT OIDS;

CREATE INDEX "ix_tblclientproductrelation_fldclientnumber" ON "public"."tblclientproductrelation"
USING btree ("fldclientnumber");

CREATE INDEX "ix_tblclientproductrelation_fldproductnumber" ON "public"."tblclientproductrelation"
USING btree ("fldproductnumber");

CREATE TABLE "public"."tblclientcomments" (
  "fldclientnumber" NUMERIC(18,0),
  "fldclientname_remove" VARCHAR(100),
  "fldclientcontactlastname_remove" VARCHAR(50),
  "flddate" TIMESTAMP WITH TIME ZONE,
  "flduser" VARCHAR(10),
  "fldclientcomments" VARCHAR(7800),
  "fldproductcode" VARCHAR(10),
  "fldstatuscode" VARCHAR(10),
  "fldactioncompletedby" VARCHAR(10),
  "fldcommentnumber" SERIAL,
  "fldenable" BOOLEAN NOT NULL,
  "fldcontactnumber" NUMERIC(18,0),
  "fldcommentflag" BOOLEAN NOT NULL,
  "fldclosepropensity" VARCHAR(2) NOT NULL,
  CONSTRAINT "pk_tblclientcomments" PRIMARY KEY("fldcommentnumber")
) WITHOUT OIDS;

CREATE INDEX "ix_tblclientcomments_fldstatuscode" ON "public"."tblclientcomments"
USING btree ("fldstatuscode");

CREATE TABLE "public"."tblgeopreference" (
  "fldclientnumber" INTEGER,
  "fldclientname_rename" VARCHAR(100),
  "fldsw" BOOLEAN NOT NULL,
  "fldnw" BOOLEAN NOT NULL,
  "fldmw" BOOLEAN NOT NULL,
  "fldw" BOOLEAN NOT NULL,
  "fldma" BOOLEAN NOT NULL,
  "fldse" BOOLEAN NOT NULL,
  "flds" BOOLEAN NOT NULL,
  "fldne" BOOLEAN NOT NULL
) WITHOUT OIDS;

CREATE INDEX "ix_tblgeopreference_fldclientnumber" ON "public"."tblgeopreference"
USING btree ("fldclientnumber");

CREATE TABLE "public"."tblclientactivitytag" (
  "fldclientnumber" INTEGER,
  "fldcontactactivitytag" VARCHAR(100),
  "fldclientname_old" VARCHAR(100),
  "fldcontactfirstname" VARCHAR(50),
  "fldcontactlastname" VARCHAR(50),
  "fldcontactnumber" NUMERIC(18,0)
) WITHOUT OIDS;

CREATE INDEX "ix_tblclientactivitytag_fldclientnumber" ON "public"."tblclientactivitytag"
USING btree ("fldclientnumber");

CREATE INDEX "ix_tblclientactivitytag_fldcontactnumber" ON "public"."tblclientactivitytag"
USING btree ("fldcontactnumber");

CREATE INDEX "ix_tblclientactivitytag_tag" ON "public"."tblclientactivitytag"
USING btree ("fldcontactactivitytag");

--
Robert

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

I wrote:
> 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.

Uh, scratch that advice, I fat-fingered my test.  It does seem to
estimate that '%%%' matches every row.

But that leads into another question, because some of the scan estimates
are further off than one would like:

>>              ->  Seq Scan on tblclientcomments  (cost=0.00..40651.36 rows=601579 width=17) (actual
time=0.014..17.342rows=6912 loops=1017) 
>>                    Filter: (((fldproductcode)::text ~~* '%%%'::text) AND (fldenable = true))

I had thought that this was explained by a bad LIKE estimate but that
seems not the case, which means that your statistics for fldenable must
be way off.   Have you ANALYZEd these tables since loading the data?

            regards, tom lane

On Sat, 2007-05-19 at 19:19 -0400, Tom Lane wrote:
> 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).

Thanks again, I'll be sure to get this straightened out and tested again
tomorrow. I thought my nightly backup was analyze'ing the database
afterward, I'll be sure to check that as well.

I really appreciate your analysis! It is my first migration from another
SQL database.

--
Robert