Re: Performance tuning?

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