Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 7E7319FB38C; Fri, 23 Mar 2007 14:24:00 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-new, port 10024) with ESMTP id 60901-05; Fri, 23 Mar 2007 14:22:26 -0300 (ADT) X-Greylist: delayed 00:20:28.103952 by SQLgrey-1.7.4 X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from gwmta.wicourts.gov (gwmta.wicourts.gov [165.219.244.99]) by postgresql.org (Postfix) with ESMTP id D58479FB505; Fri, 23 Mar 2007 14:22:24 -0300 (ADT) Received: from Courts-MTA by gwmta.wicourts.gov with Novell_GroupWise; Fri, 23 Mar 2007 12:01:55 -0500 Message-Id: <4603C1A3.EE98.0025.0@wicourts.gov> X-Mailer: Novell GroupWise Internet Agent 7.0.1 Date: Fri, 23 Mar 2007 12:01:40 -0500 From: "Kevin Grittner" To: , Cc: "Andrea Olson" , "Bill Severson" , "John Hutchins" , "Kevin Grittner" , "Randy Peterson" , "Shannon Spranger" Subject: EXISTS optimization Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: quoted-printable Content-Disposition: inline X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200703/1409 X-Sequence-Number: 100893 I'm posting this to performance in case our workaround may be of benefit = to someone with a similar issue. I'm posting to hackers because I hope we = can improve our planner in this area so that a workaround is not necessary.= (It might make sense to reply to one group or the other, depending on = reply content.) =20 We are converting from a commercial database (which shall remain unnamed = here, due to license restrictions on publishing benchmarks). Most queries = run faster on PostgreSQL; a small number choose very poor plans and run = much longer. This particular query runs on the commercial product in 6.1s = first time, 1.4s cached. In PostgreSQL it runs in about 144s both first = time and cached. I was able to use an easy but fairly ugly rewrite = (getting duplicate rows and eliminating them with DISTINCT) which runs on = the commercial product in 9.2s/3.0s and in PostgreSQL in 2.0s/0.7s. =20 Here are the tables: =20 Table "public.TranHeader" Column | Type | Modifiers ---------------+------------------+----------- tranNo | "TranNoT" | not null countyNo | "CountyNoT" | not null acctPd | "DateT" | not null date | "DateT" | not null isComplete | boolean | not null tranId | "TranIdT" | not null tranType | "TranTypeT" | not null userId | "UserIdT" | not null workstationId | "WorkstationIdT" | not null time | "TimeT" | Indexes: "TranHeader_pkey" PRIMARY KEY, btree ("tranNo", "countyNo") "TranHeader_TranAcctPeriod" UNIQUE, btree ("acctPd", "tranNo", = "countyNo") "TranHeader_TranDate" UNIQUE, btree (date, "tranNo", "countyNo") Table "public.TranDetail" Column | Type | Modifiers -----------------+--------------------+----------- tranNo | "TranNoT" | not null tranDetailSeqNo | "TranDetailSeqNoT" | not null countyNo | "CountyNoT" | not null acctCode | "AcctCodeT" | not null amt | "MoneyT" | not null assessNo | "TranIdT" | caseNo | "CaseNoT" | citnNo | "CitnNoT" | citnViolDate | "DateT" | issAgencyNo | "IssAgencyNoT" | partyNo | "PartyNoT" | payableNo | "PayableNoT" | rcvblNo | "RcvblNoT" | Indexes: "TranDetail_pkey" PRIMARY KEY, btree ("tranNo", "tranDetailSeqNo", = "countyNo") "TranDetail_TranDetCaseNo" UNIQUE, btree ("caseNo", "tranNo", = "tranDetailSeqNo", "countyNo") "TranDetail_TranDetPay" UNIQUE, btree ("payableNo", "tranNo", = "tranDetailSeqNo", "countyNo") "TranDetail_TranDetRcvbl" UNIQUE, btree ("rcvblNo", "tranNo", = "tranDetailSeqNo", "countyNo") "TranDetail_TranDetAcct" btree ("acctCode", "citnNo", "countyNo") Table "public.Adjustment" Column | Type | Modifiers -----------------+-----------------------+----------- adjustmentNo | "TranIdT" | not null countyNo | "CountyNoT" | not null date | "DateT" | not null isTranVoided | boolean | not null reasonCode | "ReasonCodeT" | not null tranNo | "TranNoT" | not null adjustsTranId | "TranIdT" | adjustsTranNo | "TranNoT" | adjustsTranType | "TranTypeT" | explanation | character varying(50) | Indexes: "Adjustment_pkey" PRIMARY KEY, btree ("adjustmentNo", "countyNo") "Adjustment_AdjustsTranId" btree ("adjustsTranId", "adjustsTranType", = "tranNo", "countyNo") "Adjustment_AdjustsTranNo" btree ("adjustsTranNo", "tranNo", "countyNo"= ) "Adjustment_Date" btree (date, "countyNo") =20 Admittedly, the indexes are optimized for our query load under the = commercial product, which can use the "covering index" optimization. =20 explain analyze SELECT "A"."adjustmentNo", "A"."tranNo", "A"."countyNo", "H"."date", = "H"."userId", "H"."time" FROM "Adjustment" "A" JOIN "TranHeader" "H" ON ("H"."tranId" =3D "A"."adjustmentNo" AND = "H"."countyNo" =3D "A"."countyNo" AND "H"."tranNo" =3D "A"."tranNo") WHERE "H"."tranType" =3D 'A' AND "A"."date" > DATE '2006-01-01' AND "H"."countyNo" =3D 66 AND "A"."countyNo" =3D 66 AND EXISTS ( SELECT 1 FROM "TranDetail" "D" WHERE "D"."tranNo" =3D "H"."tranNo" AND "D"."countyNo" =3D "H"."countyNo" AND "D"."caseNo" LIKE '2006TR%' ) ; Nested Loop (cost=3D182.56..72736.37 rows=3D1 width=3D46) (actual = time=3D6398.108..143631.427 rows=3D2205 loops=3D1) Join Filter: (("H"."tranId")::bpchar =3D ("A"."adjustmentNo")::bpchar) -> Bitmap Heap Scan on "Adjustment" "A" (cost=3D182.56..1535.69 = rows=3D11542 width=3D22) (actual time=3D38.098..68.324 rows=3D12958 = loops=3D1) Recheck Cond: (((date)::date > '2006-01-01'::date) AND (("countyNo= ")::smallint =3D 66)) -> Bitmap Index Scan on "Adjustment_Date" (cost=3D0.00..179.67 = rows=3D11542 width=3D0) (actual time=3D32.958..32.958 rows=3D12958 = loops=3D1) Index Cond: (((date)::date > '2006-01-01'::date) AND = (("countyNo")::smallint =3D 66)) -> Index Scan using "TranHeader_pkey" on "TranHeader" "H" (cost=3D0.00= ..6.15 rows=3D1 width=3D46) (actual time=3D11.073..11.074 rows=3D0 = loops=3D12958) Index Cond: ((("H"."tranNo")::integer =3D ("A"."tranNo")::integer)= AND (("H"."countyNo")::smallint =3D 66)) Filter: ((("tranType")::bpchar =3D 'A'::bpchar) AND (subplan)) SubPlan -> Index Scan using "TranDetail_TranDetCaseNo" on "TranDetail" = "D" (cost=3D0.00..4.73 rows=3D1 width=3D0) (actual time=3D11.038..11.038 = rows=3D0 loops=3D12958) Index Cond: ((("caseNo")::bpchar >=3D '2006TR'::bpchar) = AND (("caseNo")::bpchar < '2006TS'::bpchar) AND (("tranNo")::integer =3D = ($0)::integer) AND (("countyNo")::smallint =3D ($1)::smallint)) Filter: (("caseNo")::bpchar ~~ '2006TR%'::text) Total runtime: 143633.838 ms =20 The commercial product scans the index on caseNo in TranDetail to build a = work table of unique values, then uses indexed access to the TranHeader = and then to Adjustment. I was able to get approximately the same plan = (except the duplicates are eliminated at the end) in PostgreSQL by = rewriting to this: =20 SELECT DISTINCT "A"."adjustmentNo", "A"."tranNo", "A"."countyNo", = "H"."date", "H"."userId", "H"."time" FROM "Adjustment" "A" JOIN "TranHeader" "H" ON ("H"."tranId" =3D "A"."adjustmentNo" AND = "H"."countyNo" =3D "A"."countyNo" AND "H"."tranNo" =3D "A"."tranNo") JOIN "TranDetail" "D" ON ("D"."tranNo" =3D "H"."tranNo" AND "D"."countyNo= " =3D "H"."countyNo" AND "D"."caseNo" LIKE '2006TR%') WHERE "H"."tranType" =3D 'A' AND "A"."date" > DATE '2006-01-01' AND "H"."countyNo" =3D 66 AND "A"."countyNo" =3D 66 ; Unique (cost=3D130.96..130.98 rows=3D1 width=3D46) (actual time=3D694.591= ..715.008 rows=3D2205 loops=3D1) -> Sort (cost=3D130.96..130.96 rows=3D1 width=3D46) (actual time=3D694= .586..701.808 rows=3D16989 loops=3D1) Sort Key: "A"."adjustmentNo", "A"."tranNo", "A"."countyNo", = "H".date, "H"."userId", "H"."time" -> Nested Loop (cost=3D0.00..130.95 rows=3D1 width=3D46) = (actual time=3D0.157..636.779 rows=3D16989 loops=3D1) Join Filter: (("H"."tranNo")::integer =3D ("A"."tranNo")::in= teger) -> Nested Loop (cost=3D0.00..113.76 rows=3D4 width=3D50) = (actual time=3D0.131..452.544 rows=3D16989 loops=3D1) -> Index Scan using "TranDetail_TranDetCaseNo" on = "TranDetail" "D" (cost=3D0.00..27.57 rows=3D20 width=3D6) (actual = time=3D0.049..83.005 rows=3D46293 loops=3D1) Index Cond: ((("caseNo")::bpchar >=3D '2006TR'::= bpchar) AND (("caseNo")::bpchar < '2006TS'::bpchar) AND (66 =3D ("countyNo"= )::smallint)) Filter: (("caseNo")::bpchar ~~ '2006TR%'::text) -> Index Scan using "TranHeader_pkey" on "TranHeader"= "H" (cost=3D0.00..4.30 rows=3D1 width=3D46) (actual time=3D0.006..0.007 = rows=3D0 loops=3D46293) Index Cond: ((("D"."tranNo")::integer =3D = ("H"."tranNo")::integer) AND (("H"."countyNo")::smallint =3D 66)) Filter: (("tranType")::bpchar =3D 'A'::bpchar) -> Index Scan using "Adjustment_pkey" on "Adjustment" "A" = (cost=3D0.00..4.28 rows=3D1 width=3D22) (actual time=3D0.007..0.008 = rows=3D1 loops=3D16989) Index Cond: ((("H"."tranId")::bpchar =3D ("A"."adjustm= entNo")::bpchar) AND (("A"."countyNo")::smallint =3D 66)) Filter: ((date)::date > '2006-01-01'::date) Total runtime: 715.932 ms =20 I can't see any reason that PostgreSQL can't catch up to the other product = on this optimization issue. This usage of DISTINCT seems a bit sloppy; I = usually try to dissuade the application programmers from accumulating = duplicates during the joins and then eliminating them in this way. =20 -Kevin =20