I posted an issue I was having with a migration from MS SQL server to
pgsql over the weekend. Tom's suggestion for the query I posted was
right on, I made the appropriate updates to column types by dumping,
changing and restoring the database. I then analyze'd the db and my
query performance was equal to that of MS SQL. This is my first
migration and also my first time trying to spot performance issues,
mainly because all the previous db's I've worked with were built from
scratch, never an issue with performance, but never worked with so much
data either (not sure if that has anything to do with my issues).
I have developed a view in pgsql that takes over 160K ms to execute, but
when copied into MS SQL against the old database (with syntax mods of
course), runs in a few seconds. Seems the issues are with tblcontactinfo
and tblclientactivitytag. Only if I remove all references to *both*
tables do I get good performance from the query. Thanks for any help!
SELECT tblclientmaster.fldclientnumber, tblclientmaster.fldclientname,
tblclientmaster.fldclienttype, tblclientmaster.fldbuyingstatus,
tblclientmaster.fldsellingstatus, tblclientproductpreference.fldfullservice,
tblclientproductpreference.fldlimitedservice,
tblclientproductpreference.fldallsuite, tblclientproductpreference.fldbudget,
tblclientproductpreference.fldconference, tblclientproductpreference.fldresort,
tblclientproductpreference.flddailyfee,
tblclientproductpreference.fldsemiprivate,
tblclientproductpreference.fldprivate, tblclientproductpreference.fldmunicipal,
tblclientroomsize.fldsize149, tblclientroomsize.fldsize299,
tblclientroomsize.fldsize449, tblclientroomsize.fldsize599,
tblclientroomsize.fldsize600, tblgeopreference.fldsw, tblgeopreference.fldnw,
tblgeopreference.fldmw, tblgeopreference.fldw, tblgeopreference.fldma,
tblgeopreference.fldse, tblgeopreference.flds, tblgeopreference.fldne,
tblproductmaster.fldproductname, tblproductmaster.fldproductcode,
tblcontactinfo.fldcontactnumber, tblcontactinfo.fldcontactfirstname,
tblcontactinfo.fldcontactlastname, (tblcontactinfo.fldcontactaddress1::text ||
' '::text) || tblcontactinfo.fldcontactaddress2::text AS fldcontactaddress,
tblcontactinfo.fldcontactcity, tblcontactinfo.fldcontactstate,
tblcontactinfo.fldcontactzipcode, tblcontactinfo.fldcontacttitle,
tblcontactinfo.fldcontactphone2_type, tblcontactinfo.fldcontactphone2_num,
tblcontactinfo.fldcontactphone3_num, tblcontactinfo.fldcontactphone4_num,
tblcontactinfo.fldcontactphone5_num, tblcontactinfo.fldcontactemail,
tblcontactinfo.fldenable, tblcontactinfo.fldcontactphone1_num,
tblcontactinfo.fldperscomments, tblclientactivitytag.fldcontactactivitytag
FROM tblclientmaster
LEFT JOIN tblclientproductpreference ON tblclientmaster.fldclientnumber =
tblclientproductpreference.fldclientnumber
LEFT JOIN tblclientroomsize ON tblclientmaster.fldclientnumber =
tblclientroomsize.fldclientnumber
LEFT JOIN tblgeopreference ON tblclientmaster.fldclientnumber =
tblgeopreference.fldclientnumber
LEFT JOIN tblclientproductrelation ON tblclientmaster.fldclientnumber =
tblclientproductrelation.fldclientnumber
JOIN tblproductmaster ON tblclientproductrelation.fldproductnumber =
tblproductmaster.fldproductnumber
LEFT JOIN tblcontactinfo ON tblclientmaster.fldclientnumber =
tblcontactinfo.fldclientnumber
LEFT JOIN tblclientactivitytag ON tblclientmaster.fldclientnumber =
tblclientactivitytag.fldclientnumber
ORDER BY tblclientmaster.fldclientnumber, tblclientmaster.fldclientname,
tblclientmaster.fldclienttype, tblclientmaster.fldbuyingstatus,
tblclientmaster.fldsellingstatus, tblclientproductpreference.fldfullservice,
tblclientproductpreference.fldlimitedservice,
tblclientproductpreference.fldallsuite, tblclientproductpreference.fldbudget,
tblclientproductpreference.fldconference, tblclientproductpreference.fldresort,
tblclientproductpreference.flddailyfee,
tblclientproductpreference.fldsemiprivate,
tblclientproductpreference.fldprivate, tblclientproductpreference.fldmunicipal,
tblclientroomsize.fldsize149, tblclientroomsize.fldsize299,
tblclientroomsize.fldsize449, tblclientroomsize.fldsize599,
tblclientroomsize.fldsize600, tblgeopreference.fldsw, tblgeopreference.fldnw,
tblgeopreference.fldmw, tblgeopreference.fldw, tblgeopreference.fldma,
tblgeopreference.fldse, tblgeopreference.flds, tblgeopreference.fldne,
tblproductmaster.fldproductname, tblproductmaster.fldproductcode,
tblcontactinfo.fldcontactnumber, tblcontactinfo.fldcontactfirstname,
tblcontactinfo.fldcontactlastname, (tblcontactinfo.fldcontactaddress1::text ||
' '::text) || tblcontactinfo.fldcontactaddress2::text,
tblcontactinfo.fldcontactcity, tblcontactinfo.fldcontactstate,
tblcontactinfo.fldcontactzipcode, tblcontactinfo.fldcontacttitle,
tblcontactinfo.fldcontactphone2_type, tblcontactinfo.fldcontactphone2_num,
tblcontactinfo.fldcontactphone3_num, tblcontactinfo.fldcontactphone4_num,
tblcontactinfo.fldcontactphone5_num, tblcontactinfo.fldcontactemail,
tblcontactinfo.fldenable, tblcontactinfo.fldcontactphone1_num,
tblcontactinfo.fldperscomments, tblclientactivitytag.fldcontactactivitytag;
Merge Left Join (cost=35366.42..57565.28 rows=565261 width=297) (actual
time=1000.457..148111.905 rows=41866801 loops=1)
Merge Cond: (tblclientmaster.fldclientnumber =
tblclientactivitytag.fldclientnumber)
-> Merge Join (cost=18768.38..29470.77 rows=76505 width=286) (actual
time=437.703..1575.449 rows=360822 loops=1)
Merge Cond: (tblclientmaster.fldclientnumber =
tblclientproductrelation.fldclientnumber)
-> Merge Left Join (cost=0.00..9495.89 rows=23878 width=257) (actual
time=0.224..302.321 rows=24908 loops=1)
Merge Cond: (tblclientmaster.fldclientnumber =
tblcontactinfo.fldclientnumber)
-> Merge Left Join (cost=0.00..4044.08 rows=16485 width=90)
(actual time=0.172..182.162 rows=16547 loops=1)
Merge Cond: (tblclientmaster.fldclientnumber =
tblclientproductpreference.fldclientnumber)
-> Merge Left Join (cost=0.00..3212.26 rows=16485
width=80) (actual time=0.134..130.524 rows=16514 loops=1)
Merge Cond: (tblclientmaster.fldclientnumber =
tblclientroomsize.fldclientnumber)
-> Merge Left Join (cost=0.00..2396.89 rows=16485
width=75) (actual time=0.097..81.108 rows=16497 loops=1)
Merge Cond: (tblclientmaster.fldclientnumber =
tblgeopreference.fldclientnumber)
-> Index Scan using pk_tblclientmaster on
tblclientmaster (cost=0.00..1582.44 rows=16485 width=67) (actual
time=0.053..22.425 rows=16484 loops=1)
-> Index Scan using
ix_tblgeopreference_fldclientnumber on tblgeopreference (cost=0.00..606.38
rows=13398 width=16) (actual time=0.033..14.121 rows=13397 loops=1)
-> Index Scan using
ix_tblclientroomsize_fldclientnumber on tblclientroomsize (cost=0.00..606.72
rows=13395 width=13) (actual time=0.032..14.240 rows=13414 loops=1)
-> Index Scan using ix_tblclientproductpreference on
tblclientproductpreference (cost=0.00..623.95 rows=13391 width=18) (actual
time=0.031..15.194 rows=13439 loops=1)
-> Index Scan using ix_tblcontactinfo_fldclientnumber on
tblcontactinfo (cost=0.00..5113.21 rows=23878 width=175) (actual
time=0.046..50.194 rows=24512 loops=1)
-> Sort (cost=18768.38..18900.69 rows=52924 width=37) (actual
time=437.401..724.717 rows=361884 loops=1)
Sort Key: tblclientproductrelation.fldclientnumber
-> Merge Join (cost=0.00..12985.06 rows=52924 width=37) (actual
time=0.827..299.983 rows=52904 loops=1)
Merge Cond: (tblclientproductrelation.fldproductnumber =
tblproductmaster.fldproductnumber)
-> Index Scan using
ix_tblclientproductrelation_fldproductnumber on tblclientproductrelation
(cost=0.00..2210.71 rows=52950 width=12) (actual time=0.443..53.273 rows=52950
loops=1)
-> Index Scan using pk_tblproperty on tblproductmaster
(cost=0.00..9987.31 rows=52924 width=37) (actual time=0.377..102.800 rows=52927
loops=1)
-> Sort (cost=16598.04..16902.54 rows=121800 width=19) (actual
time=562.569..29677.017 rows=41662721 loops=1)
Sort Key: tblclientactivitytag.fldclientnumber
-> Seq Scan on tblclientactivitytag (cost=0.00..3394.00 rows=121800
width=19) (actual time=12.772..115.473 rows=121800 loops=1)
Total runtime: 163914.674 ms
Indexes were added in an effort to help the issue, but no help. These
indexes do not exists in MS SQL server:
CREATE TABLE "public"."tblclientactivitytag" (
"fldclientnumber" BIGINT,
"fldcontactactivitytag" VARCHAR(100),
"fldclientname_old" VARCHAR(100),
"fldcontactfirstname" VARCHAR(50),
"fldcontactlastname" VARCHAR(50),
"fldcontactnumber" BIGINT,
"fldclientactivitytagid" BIGSERIAL,
CONSTRAINT "tblclientactivitytag_pkey" PRIMARY KEY("fldclientactivitytagid"),
CONSTRAINT "test2" FOREIGN KEY ("fldclientnumber")
REFERENCES "public"."tblclientmaster"("fldclientnumber")
ON DELETE CASCADE
ON UPDATE CASCADE
NOT DEFERRABLE
) WITHOUT OIDS;
CREATE INDEX "ix_tblclientactivitytag_fldclientactivitytagid" ON
"public"."tblclientactivitytag"
USING btree ("fldclientactivitytagid");
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");
CREATE TABLE "public"."tblcontactinfo" (
"fldclientnumber" BIGINT,
"fldcontactnumber" BIGSERIAL,
"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" BIGINT,
"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"),
CONSTRAINT "test" FOREIGN KEY ("fldclientnumber")
REFERENCES "public"."tblclientmaster"("fldclientnumber")
ON DELETE CASCADE
ON UPDATE CASCADE
NOT DEFERRABLE
) 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"."tblclientmaster" (
"fldclientnumber" BIGSERIAL,
"fldclientname" VARCHAR(100),
"fldclienttype" VARCHAR(50),
"fldclientparentcompanyname_remove" VARCHAR(100),
"fldclientparentcompanynumber" BIGINT,
"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;
--
Robert
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
