Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 8CE3B9FA55B for ; Tue, 9 Oct 2007 17:10:22 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 57808-01 for ; Tue, 9 Oct 2007 17:10:00 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from gwmta.wicourts.gov (gwmta.wicourts.gov [165.219.244.99]) by postgresql.org (Postfix) with ESMTP id 83A3F9F9EFB for ; Tue, 9 Oct 2007 17:10:01 -0300 (ADT) Received: from Courts-MTA by gwmta.wicourts.gov with Novell_GroupWise; Tue, 09 Oct 2007 15:09:57 -0500 Message-Id: <470B99BE.EE98.0025.0@wicourts.gov> X-Mailer: Novell GroupWise Internet Agent 7.0.2 Date: Tue, 09 Oct 2007 15:09:51 -0500 From: "Kevin Grittner" To: Subject: hashjoin chosen over 1000x faster plan 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: 200710/88 X-Sequence-Number: 27110 I have a situation where a query is running much slower than I would expect. The ANALYZE showed that it is hashing some information which is rarely needed. When I set enable_hashjoin =3D off for the connection the query run in 1/1000 the time. =20 This isn't a debilitating level of performance, but it would be nice to clean it up, and we haven't yet come up with a viable solution. =20 The runs below are after several identical runs to create a fully cached situation. Autovacuum is aggressive and there is a nightly vacuum analyze of the whole database. This box has 4 x 2 GHz Xeon CPUs, 6 GB RAM, RAID 5 with 13 spindles on 256 MB BBU controller. =20 I simplified the original a bit; sorry it's still kinda big. =20 -Kevin =20 listen_addresses =3D '*'=20 max_connections =3D 200 shared_buffers =3D 160MB temp_buffers =3D 50MB work_mem =3D 10MB maintenance_work_mem =3D 160MB max_fsm_pages =3D 800000 bgwriter_lru_percent =3D 20.0 bgwriter_lru_maxpages =3D 200 bgwriter_all_percent =3D 10.0 bgwriter_all_maxpages =3D 600 wal_buffers =3D 160kB checkpoint_segments =3D 10 random_page_cost =3D 2.0 effective_cache_size =3D 5GB redirect_stderr =3D on log_line_prefix =3D '[%m] %p %q<%u %d %r> ' stats_block_level =3D on stats_row_level =3D on autovacuum =3D on autovacuum_naptime =3D 10s autovacuum_vacuum_threshold =3D 1 autovacuum_analyze_threshold =3D 1 datestyle =3D 'iso, mdy' lc_messages =3D 'C' lc_monetary =3D 'C' lc_numeric =3D 'C' lc_time =3D 'C' escape_string_warning =3D off standard_conforming_strings =3D on sql_inheritance =3D off =20 bigbird=3D> select version(); version ---------------------------------------------------------------------------= ---------- PostgreSQL 8.2.4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.3 = (SuSE Linux) (1 row) bigbird=3D> explain analyze SELECT "CH"."caseNo", "CH"."countyNo", "CH"."chargeNo", "CH"."statuteCite", "CH"."sevClsCode", "CH"."modSevClsCode", "CH"."descr", "CH"."offenseDate", "CH"."pleaCode", "CH"."pleaDate", "CH"."chargeSeqNo", "CHST"."eventDate" AS "reopEventDate", "CTHE"."descr" AS "reopEventDescr" FROM "Charge" "CH" LEFT OUTER JOIN "CaseHist" "CHST" ON ( "CHST"."countyNo" =3D "CH"."countyNo" AND "CHST"."caseNo" =3D "CH"."caseNo" AND "CHST"."histSeqNo" =3D "CH"."reopHistSeqNo" ) LEFT OUTER JOIN "CaseTypeHistEvent" "CTHE" ON ( "CHST"."eventType" =3D "CTHE"."eventType" AND "CHST"."caseType" =3D "CTHE"."caseType" AND "CHST"."countyNo" =3D "CTHE"."countyNo" ) WHERE ( ("CH"."caseNo" =3D '2005CF000001') AND ("CH"."countyNo" =3D 13)) ORDER BY "chargeNo", "chargeSeqNo" ; = QUERY PLAN = =20 ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= --------------------------------------------------- Sort (cost=3D2554.50..2554.52 rows=3D7 width=3D146) (actual time=3D443.06= 8..443.070 rows=3D3 loops=3D1) Sort Key: "CH"."chargeNo", "CH"."chargeSeqNo" -> Hash Left Join (cost=3D2318.91..2554.40 rows=3D7 width=3D146) = (actual time=3D443.004..443.039 rows=3D3 loops=3D1) Hash Cond: ((("CHST"."eventType")::bpchar =3D ("CTHE"."eventType")= ::bpchar) AND (("CHST"."caseType")::bpchar =3D ("CTHE"."caseType")::bpchar)= ) -> Nested Loop Left Join (cost=3D0.00..208.13 rows=3D7 = width=3D131) (actual time=3D0.062..0.093 rows=3D3 loops=3D1) -> Index Scan using "Charge_pkey" on "Charge" "CH" = (cost=3D0.00..15.37 rows=3D7 width=3D112) (actual time=3D0.052..0.059 = rows=3D3 loops=3D1) Index Cond: ((("countyNo")::smallint =3D 13) AND = (("caseNo")::bpchar =3D '2005CF000001'::bpchar)) -> Index Scan using "CaseHist_pkey" on "CaseHist" "CHST" = (cost=3D0.00..27.46 rows=3D6 width=3D41) (actual time=3D0.002..0.002 = rows=3D0 loops=3D3) Index Cond: ((("CHST"."countyNo")::smallint =3D 13) = AND (("CHST"."caseNo")::bpchar =3D '2005CF000001'::bpchar) AND (("CHST"."hi= stSeqNo")::smallint =3D ("CH"."reopHistSeqNo")::smallint)) -> Hash (cost=3D2084.80..2084.80 rows=3D15607 width=3D98) = (actual time=3D442.919..442.919 rows=3D15607 loops=3D1) -> Subquery Scan "CTHE" (cost=3D1630.43..2084.80 = rows=3D15607 width=3D98) (actual time=3D331.665..411.390 rows=3D15607 = loops=3D1) -> Merge Right Join (cost=3D1630.43..1928.73 = rows=3D15607 width=3D89) (actual time=3D331.661..391.999 rows=3D15607 = loops=3D1) Merge Cond: (((d."countyNo")::smallint =3D = "inner"."?column9?") AND ((d."caseType")::bpchar =3D "inner"."?column10?") = AND ((d."eventType")::bpchar =3D "inner"."?column11?")) -> Index Scan using "CaseTypeHistEventD_pkey" = on "CaseTypeHistEventD" d (cost=3D0.00..87.77 rows=3D2051 width=3D21) = (actual time=3D0.026..0.730 rows=3D434 loops=3D1) -> Sort (cost=3D1630.43..1669.45 rows=3D15607 = width=3D76) (actual time=3D331.022..341.450 rows=3D15607 loops=3D1) Sort Key: (c."countyNo")::smallint, = (b."caseType")::bpchar, (b."eventType")::bpchar -> Nested Loop (cost=3D0.00..543.41 = rows=3D15607 width=3D76) (actual time=3D0.035..47.206 rows=3D15607 = loops=3D1) -> Index Scan using "ControlRecord_= pkey" on "ControlRecord" c (cost=3D0.00..4.27 rows=3D1 width=3D2) (actual = time=3D0.010..0.017 rows=3D1 loops=3D1) Index Cond: (("countyNo")::sma= llint =3D 13) -> Seq Scan on "CaseTypeHistEventB"= b (cost=3D0.00..383.07 rows=3D15607 width=3D74) (actual time=3D0.019..14.= 634 rows=3D15607 loops=3D1) Total runtime: 444.452 ms (21 rows) bigbird=3D> set enable_hashjoin =3D off; SET bigbird=3D> [same query] = QUERY PLAN = =20 ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= --------------------------------------------------------- Sort (cost=3D3497.26..3497.28 rows=3D7 width=3D146) (actual time=3D0.115.= .0.117 rows=3D3 loops=3D1) Sort Key: "CH"."chargeNo", "CH"."chargeSeqNo" -> Merge Left Join (cost=3D3380.05..3497.17 rows=3D7 width=3D146) = (actual time=3D0.091..0.097 rows=3D3 loops=3D1) Merge Cond: (("outer"."?column16?" =3D "inner"."?column5?") AND = ("outer"."?column17?" =3D "inner"."?column6?")) -> Sort (cost=3D208.23..208.25 rows=3D7 width=3D131) (actual = time=3D0.087..0.089 rows=3D3 loops=3D1) Sort Key: ("CHST"."caseType")::bpchar, ("CHST"."eventType"):= :bpchar -> Nested Loop Left Join (cost=3D0.00..208.13 rows=3D7 = width=3D131) (actual time=3D0.053..0.070 rows=3D3 loops=3D1) -> Index Scan using "Charge_pkey" on "Charge" "CH" = (cost=3D0.00..15.37 rows=3D7 width=3D112) (actual time=3D0.043..0.048 = rows=3D3 loops=3D1) Index Cond: ((("countyNo")::smallint =3D 13) = AND (("caseNo")::bpchar =3D '2005CF000001'::bpchar)) -> Index Scan using "CaseHist_pkey" on "CaseHist" = "CHST" (cost=3D0.00..27.46 rows=3D6 width=3D41) (actual time=3D0.001..0.00= 1 rows=3D0 loops=3D3) Index Cond: ((("CHST"."countyNo")::smallint =3D = 13) AND (("CHST"."caseNo")::bpchar =3D '2005CF000001'::bpchar) AND = (("CHST"."histSeqNo")::smallint =3D ("CH"."reopHistSeqNo")::smallint)) -> Sort (cost=3D3171.82..3210.84 rows=3D15607 width=3D98) = (never executed) Sort Key: ("CTHE"."caseType")::bpchar, ("CTHE"."eventType"):= :bpchar -> Subquery Scan "CTHE" (cost=3D1630.43..2084.80 = rows=3D15607 width=3D98) (never executed) -> Merge Right Join (cost=3D1630.43..1928.73 = rows=3D15607 width=3D89) (never executed) Merge Cond: (((d."countyNo")::smallint =3D = "inner"."?column9?") AND ((d."caseType")::bpchar =3D "inner"."?column10?") = AND ((d."eventType")::bpchar =3D "inner"."?column11?")) -> Index Scan using "CaseTypeHistEventD_pkey" = on "CaseTypeHistEventD" d (cost=3D0.00..87.77 rows=3D2051 width=3D21) = (never executed) -> Sort (cost=3D1630.43..1669.45 rows=3D15607 = width=3D76) (never executed) Sort Key: (c."countyNo")::smallint, = (b."caseType")::bpchar, (b."eventType")::bpchar -> Nested Loop (cost=3D0.00..543.41 = rows=3D15607 width=3D76) (never executed) -> Index Scan using "ControlRecord_= pkey" on "ControlRecord" c (cost=3D0.00..4.27 rows=3D1 width=3D2) (never = executed) Index Cond: (("countyNo")::sma= llint =3D 13) -> Seq Scan on "CaseTypeHistEventB"= b (cost=3D0.00..383.07 rows=3D15607 width=3D74) (never executed) Total runtime: 0.437 ms (24 rows) bigbird=3D> \d "Charge" Table "public.Charge" Column | Type | Modifiers --------------------+---------------------+----------- caseNo | "CaseNoT" | not null chargeSeqNo | "ChargeSeqNoT" | not null countyNo | "CountyNoT" | not null areSentCondsMet | boolean | not null caseType | "CaseTypeT" | not null chargeNo | "ChargeNoT" | not null descr | "StatuteDescrT" | not null isPartyTo | boolean | not null lastChargeModSeqNo | integer | not null lastJdgmtSeqNo | "JdgmtSeqNoT" | not null ordStatuteFlag | character(1) | not null plntfAgencyNo | "PlntfAgencyNoT" | not null statuteAgencyNo | "PlntfAgencyNoT" | not null statuteCite | "StatuteCiteT" | not null statuteEffDate | "DateT" | arrestCaseNo | "ArrestCaseNoT" | arrestDate | "DateT" | arrestTrackingNo | "ArrestTrackingNoT" | bookAgencyNo | "IssAgencyNoT" | bookCaseNo | "BookCaseNoT" | chargeId | "ChargeIdT" | dispoCode | "DispoCodeT" | issAgencyNo | "IssAgencyNoT" | modSevClsCode | "SevClsCodeT" | offenseDate | "DateT" | offenseDateRange | "OffenseDateRangeT" | pleaCode | "PleaCodeT" | pleaDate | "DateT" | reopHistSeqNo | "HistSeqNoT" | sevClsCode | "SevClsCodeT" | statuteSevSeqNo | "StatuteSevSeqNoT" | wcisClsCode | "WcisClsCodeT" | pleaHistSeqNo | "HistSeqNoT" | chargeStatusCode | "ChargeStatusCodeT" | Indexes: "Charge_pkey" PRIMARY KEY, btree ("countyNo", "caseNo", "chargeSeqNo") "Charge_ArrestTrackingNo" UNIQUE, btree ("arrestTrackingNo", "countyNo"= , "caseNo", "chargeSeqNo") "Charge_OffenseDate" btree ("offenseDate", "countyNo", "issAgencyNo") bigbird=3D> \d "CaseHist" Table "public.CaseHist" Column | Type | Modifiers ---------------+------------------+----------- caseNo | "CaseNoT" | not null histSeqNo | "HistSeqNoT" | not null countyNo | "CountyNoT" | not null caseType | "CaseTypeT" | eventAmt | "MoneyT" | eventDate | "DateT" | eventType | "EventTypeT" | userId | "UserIdT" | courtRptrCode | "CtofcNoT" | ctofcNo | "CtofcNoT" | dktTxt | "TextT" | prevRespCtofc | "CtofcNoT" | tag | "TagTypeT" | tapeCounterNo | "TapeCounterNoT" | tapeLoc | "TapeLocT" | wcisReported | "DateT" | weightPd | "PdCodeT" | weightTime | "CalDurationT" | sealCtofcNo | "CtofcNoT" | sccaCaseNo | "SccaCaseNoT" | Indexes: "CaseHist_pkey" PRIMARY KEY, btree ("countyNo", "caseNo", "histSeqNo") "CaseHist_CaseHistCibRpt" btree ("countyNo", "eventDate", "eventType", = "caseType") bigbird=3D> \d "CaseTypeHistEvent" View "public.CaseTypeHistEvent" Column | Type | Modifiers ----------------+---------------+----------- caseType | "CaseTypeT" | eventType | "EventTypeT" | descr | "EventDescrT" | isActive | boolean | isKeyEvent | boolean | isMoneyEnabled | boolean | keyEventSeqNo | integer | countyNo | "CountyNoT" | View definition: SELECT b."caseType", b."eventType", b.descr, b."isActive", CASE WHEN d."eventType" IS NOT NULL THEN d."isKeyEvent" ELSE b."isKeyEvent" END AS "isKeyEvent", CASE WHEN d."eventType" IS NOT NULL THEN d."isMoneyEnabled" ELSE b."isMoneyEnabled" END AS "isMoneyEnabled", COALESCE( CASE WHEN d."eventType" IS NOT NULL THEN d."keyEventSeqNo"::smallint= ELSE b."keyEventSeqNo"::smallint END::integer, 0) AS "keyEventSeqNo", c."countyNo" FROM ONLY "CaseTypeHistEventB" b JOIN ONLY "ControlRecord" c ON 1 =3D 1 LEFT JOIN ONLY "CaseTypeHistEventD" d ON d."caseType"::bpchar =3D = b."caseType"::bpchar AND d."eventType"::bpchar =3D b."eventType"::bpchar = AND d."countyNo"::smallint =3D c."countyNo"::smallint; bigbird=3D> \d "CaseTypeHistEventB" Table "public.CaseTypeHistEventB" Column | Type | Modifiers ----------------+----------------+----------- caseType | "CaseTypeT" | not null eventType | "EventTypeT" | not null descr | "EventDescrT" | not null isActive | boolean | not null isKeyEvent | boolean | not null isMoneyEnabled | boolean | not null keyEventSeqNo | "KeyEventSeqT" | Indexes: "CaseTypeHistEventB_pkey" PRIMARY KEY, btree ("caseType", "eventType") = CLUSTER bigbird=3D> \d "CaseTypeHistEventD" Table "public.CaseTypeHistEventD" Column | Type | Modifiers ----------------+----------------+----------- countyNo | "CountyNoT" | not null caseType | "CaseTypeT" | not null eventType | "EventTypeT" | not null isMoneyEnabled | boolean | not null isKeyEvent | boolean | not null keyEventSeqNo | "KeyEventSeqT" | Indexes: "CaseTypeHistEventD_pkey" PRIMARY KEY, btree ("countyNo", "caseType", = "eventType") "CaseTypeHistEventD_CaseType" btree ("caseType", "eventType") bigbird=3D> select count(*), count("reopHistSeqNo") from "Charge"; count | count ----------+-------- 14041511 | 141720 (1 row)