On Sat, 2007-05-19 at 17:05 -0400, Tom Lane wrote:
> Robert Fitzpatrick <[EMAIL PROTECTED]> 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=6699 width=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.884 rows=663
loops=1)
Filter: (fldfullservice = true)
-> Materialize (cost=55119.09..55127.13 rows=804 width=1014)
(actual time=46827.886..70028.280 rows=9110 loops=1)
-> Merge Join (cost=53060.03..55118.29 rows=804
width=1014) (actual time=46827.877..69956.976 rows=9110 loops=1)
Merge Cond: ("outer".fldclientnumber =
"inner".fldclientnumber)
-> Nested Loop (cost=53060.03..54565.61 rows=24
width=1010) (actual time=37189.898..69232.176 rows=25048 loops=1)
-> Nested Loop (cost=53060.03..54420.94
rows=24 width=1014) (actual time=37148.445..67472.468 rows=25048 loops=1)
-> Nested Loop IN Join
(cost=53060.03..53581.73 rows=1 width=1006) (actual time=37129.788..66642.591
rows=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.166
rows=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.979
rows=1873 loops=1)
Merge Cond:
(("outer".fldclientnumber = "inner".fldclientnumber) AND
("outer".fldcontactnumber = "inner".fldcontactnumber))
-> Sort
(cost=50577.52..50585.04 rows=3008 width=189) (actual time=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.70 rows=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.36 rows=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.975
rows=2439 loops=1)
Sort Key:
tblclientactivitytag.fldclientnumber, tblclientactivitytag.fldcontactnumber
-> Seq Scan on
tblclientactivitytag (cost=0.00..2454.24 rows=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 tblcontactinfo b (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 tblclientproductrelation g
(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.83
rows=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
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
