>>> Gregory Stark <[EMAIL PROTECTED]> wrote: > That sounds like it would be an interesting query to analyze in more detail. > Is there any chance to could run the complete graph and get a chart of > analyze > times for all statistics values from 1..1000 ? And log the explain plans to > a > file so we can look for at what statistics targets the plan changed? > > Or if the data is public I would be interested in looking at doing it if you > want to send it to me. There are some very big tables in that query which contain some confidential data. It would be hard do a lot of runs at high default_statistics_target values because the database analyze time goes so high. If I pick out which tables are used by the query, I might be able to put a script together which loops through analyze of those tables with different targets and capturing run time. There are two problems -- finding the time to set this up, and finding server time windows where other things wouldn't be distorting the results. If you could help with setting up the test script, that would go a long way toward solving the first problem. I think I could reserve a smaller multi-CPU machine with identical data but slower CPUs to run the test. I'll attach the query and plan. You'll note that the query looks a little odd, especially all the (1=1) tests. This is because the application allows users to plug in a variety of selection criteria, and any that aren't used are stubbed out that way. I picked one that was not too atypical for the 300,000 runs per day. I intentionally didn't "clean up" the white space, but left it just as it was emitted by our framework, in case any of that affected parse/plan time. If you need schema info not obvious from the plan, let me know. -Kevin
name-search.sql
Description: Binary data
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=473.48..473.57 rows=2 width=173)
-> Sort (cost=473.48..473.49 rows=2 width=173)
Sort Key: "caseNo", "filingDate", "countyName", "statusCodeDescr",
"nameF", "nameM", "nameL", suffix, dob, caption, "countyNo", "caseType",
"isSeal", "isPartySeal", "lastModified", "searchName", "isPetitionerSeal"
-> Append (cost=0.00..473.47 rows=2 width=173)
-> Subquery Scan "*SELECT* 1" (cost=0.00..235.74 rows=1
width=169)
-> Nested Loop (cost=0.00..235.73 rows=1 width=169)
-> Nested Loop (cost=0.00..126.19 rows=1 width=163)
-> Nested Loop Left Join (cost=0.00..124.31
rows=1 width=153)
-> Nested Loop Left Join
(cost=0.00..120.71 rows=1 width=153)
-> Nested Loop Left Join
(cost=0.00..117.82 rows=1 width=145)
Filter:
((("WPCT"."profileName" IS NOT NULL) OR ((("C"."caseType")::text = ANY
(('{PA,JD}'::character varying[])::text[])) AND (NOT "C"."isConfidential")))
AND ((("WPCT"."profileName")::text <> 'PUBLIC'::text) OR
(("C"."caseType")::text <> 'FA'::text) OR (("C"."wcisClsCode")::text <>
'40501'::text)))
-> Nested Loop
(cost=0.00..117.50 rows=1 width=146)
Join Filter:
((((("P"."partyType")::text = ANY (('{JV,CH}'::character varying[])::text[]))
AND (("C"."caseType")::text = 'ZZ'::text)) OR (("P"."partyType")::text <> ALL
(('{JV,CH}'::character varying[])::text[]))) AND ((("C"."caseType")::text <>
ALL (('{CF,CI,CM,CT,FO,TR}'::character varying[])::text[])) OR
(("P"."partyType")::text = 'DE'::text)) AND (((("C"."caseType")::text = ANY
(('{JA,JC,JG,JM,JO,JV,JI,TP}'::character varying[])::text[])) AND
(("P"."partyType")::text = ANY (('{CH,JV}'::character varying[])::text[]))) OR
((("C"."caseType")::text <> ALL (('{JA,JC,JG,JM,JO,JV,JI,TP}'::character
varying[])::text[])) AND (("P"."partyType")::text <> ALL (('{CH,JV}'::character
varying[])::text[])))) AND ((("P"."partyType")::text <> ALL
(('{PE,PL,JP}'::character varying[])::text[])) OR (((("C"."filingDate")::date <
'2008-11-01'::date) OR (("C"."wcisClsCode")::text <> '30709'::text)) AND
((("C"."caseType")::text <> ALL (('{CV,FA}'::character varying[])::text[])) OR
(("C"."wcisClsCode")::text <> '30711'::text) OR (NOT (subplan))))))
-> Index Scan using
"Party_SearchName" on "Party" "P" (cost=0.00..3.21 rows=1 width=81)
Index Cond:
((("searchName")::text >= 'HILL,J'::character varying) AND
(("searchName")::text < 'HILL,K'::character varying))
Filter: ((NOT
"isSeal") AND (("searchName")::text ~~ 'HILL,J%'::text))
-> Index Scan using
"Case_pkey" on "Case" "C" (cost=0.00..4.96 rows=1 width=87)
Index Cond:
((("C"."countyNo")::smallint = ("P"."countyNo")::smallint) AND
(("C"."caseNo")::text = ("P"."caseNo")::text))
Filter:
(("isExpunge" <> true) AND (NOT (subplan)))
SubPlan
-> Index Scan
using "HiddenCase_pkey" on "HiddenCase" "HCA" (cost=0.00..2.14 rows=1 width=0)
Index
Cond: ((("countyNo")::smallint = ($0)::smallint) AND (("caseNo")::text =
($1)::text))
SubPlan
-> Index Scan using
"CaseHist_pkey" on "CaseHist" "CHPET" (cost=0.00..109.25 rows=1 width=0)
Index Cond:
((($0)::smallint = ("countyNo")::smallint) AND (($1)::text = ("caseNo")::text))
Filter:
(("eventType")::text = ANY (('{FWBCA,CCTRO}'::character varying[])::text[]))
-> Index Scan using
"WccaPermCaseType_ProfileName" on "WccaPermCaseType" "WPCT" (cost=0.00..0.29
rows=1 width=32)
Index Cond:
((("WPCT"."profileName")::text = 'PUBLIC'::text) AND (("C"."caseType")::text =
("WPCT"."caseType")::text) AND (("C"."countyNo")::smallint =
("WPCT"."countyNo")::smallint))
-> Index Scan using
"LastCaseMod_pkey" on "LastCaseMod" "LCM" (cost=0.00..2.87 rows=1 width=26)
Index Cond:
((("LCM"."countyNo")::smallint = ("C"."countyNo")::smallint) AND
(("LCM"."caseNo")::text = ("C"."caseNo")::text) AND
(("LCM"."updateTypeId")::integer = 0))
-> Index Scan using "CaseDispo_pkey" on
"CaseDispo" "CD" (cost=0.00..3.59 rows=1 width=18)
Index Cond:
((("CD"."countyNo")::smallint = ("C"."countyNo")::smallint) AND
(("CD"."caseNo")::text = ("C"."caseNo")::text))
Filter: (NOT (subplan))
SubPlan
-> Index Scan using
"CaseDispo_pkey" on "CaseDispo" "CD2" (cost=0.00..2.58 rows=1 width=0)
Index Cond:
((("countyNo")::smallint = ($11)::smallint) AND (("caseNo")::text =
($10)::text) AND (("dispoDate")::date > ($12)::date))
-> Index Scan using "County_pkey" on "County"
"CY" (cost=0.00..1.87 rows=1 width=12)
Index Cond: (("C"."countyNo")::smallint
= ("CY"."countyNo")::smallint)
-> Index Scan using "StatusCode_pkey" on
"StatusCode" "S" (cost=0.00..0.27 rows=1 width=18)
Index Cond: (("C"."statusCode")::text =
("S"."statusCode")::text)
SubPlan
-> Index Scan using "CaseHist_pkey" on "CaseHist"
"CH" (cost=0.00..109.25 rows=1 width=0)
Index Cond: ((($0)::smallint =
("countyNo")::smallint) AND (($1)::text = ("caseNo")::text))
Filter: (("eventType")::text = ANY
(('{FWBCA,CCTRO}'::character varying[])::text[]))
-> Subquery Scan "*SELECT* 2" (cost=0.00..237.73 rows=1
width=173)
-> Nested Loop (cost=0.00..237.72 rows=1 width=173)
-> Nested Loop (cost=0.00..128.18 rows=1 width=167)
-> Nested Loop (cost=0.00..126.30 rows=1
width=159)
Join Filter: ((((("P"."partyType")::text
= ANY (('{JV,CH}'::character varying[])::text[])) AND (("C"."caseType")::text =
'ZZ'::text)) OR (("P"."partyType")::text <> ALL (('{JV,CH}'::character
varying[])::text[]))) AND ((("C"."caseType")::text <> ALL
(('{CF,CI,CM,CT,FO,TR}'::character varying[])::text[])) OR
(("P"."partyType")::text = 'DE'::text)) AND (((("C"."caseType")::text = ANY
(('{JA,JC,JG,JM,JO,JV,JI,TP}'::character varying[])::text[])) AND
(("P"."partyType")::text = ANY (('{CH,JV}'::character varying[])::text[]))) OR
((("C"."caseType")::text <> ALL (('{JA,JC,JG,JM,JO,JV,JI,TP}'::character
varying[])::text[])) AND (("P"."partyType")::text <> ALL (('{CH,JV}'::character
varying[])::text[])))) AND ((("P"."partyType")::text <> ALL
(('{PE,PL,JP}'::character varying[])::text[])) OR (((("C"."filingDate")::date <
'2008-11-01'::date) OR (("C"."wcisClsCode")::text <> '30709'::text)) AND
((("C"."caseType")::text <> ALL (('{CV,FA}'::character varying[])::text[])) OR
(("C"."wcisClsCode")::text <> '30711'::text) OR (NOT (subplan))))))
-> Nested Loop Left Join
(cost=0.00..13.77 rows=1 width=175)
-> Nested Loop Left Join
(cost=0.00..10.88 rows=1 width=167)
-> Nested Loop Left Join
(cost=0.00..7.28 rows=1 width=167)
Filter:
((("WPCT"."profileName" IS NOT NULL) OR ((("C"."caseType")::text = ANY
(('{PA,JD}'::character varying[])::text[])) AND (NOT "C"."isConfidential")))
AND ((("WPCT"."profileName")::text <> 'PUBLIC'::text) OR
(("C"."caseType")::text <> 'FA'::text) OR (("C"."wcisClsCode")::text <>
'40501'::text)))
-> Nested Loop
(cost=0.00..6.96 rows=1 width=168)
-> Index Scan
using "Alias_SearchName" on "Alias" "AL" (cost=0.00..1.99 rows=1 width=81)
Index
Cond: ((("searchName")::text >= 'HILL,J'::character varying) AND
(("searchName")::text < 'HILL,K'::character varying))
Filter:
(("searchName")::text ~~ 'HILL,J%'::text)
-> Index Scan
using "Case_pkey" on "Case" "C" (cost=0.00..4.96 rows=1 width=87)
Index
Cond: ((("C"."countyNo")::smallint = ("AL"."countyNo")::smallint) AND
(("AL"."caseNo")::text = ("C"."caseNo")::text))
Filter:
(("isExpunge" <> true) AND (NOT (subplan)))
SubPlan
->
Index Scan using "HiddenCase_pkey" on "HiddenCase" "HCA" (cost=0.00..2.14
rows=1 width=0)
Index Cond: ((("countyNo")::smallint = ($0)::smallint) AND (("caseNo")::text =
($1)::text))
-> Index Scan using
"WccaPermCaseType_ProfileName" on "WccaPermCaseType" "WPCT" (cost=0.00..0.29
rows=1 width=32)
Index Cond:
((("WPCT"."profileName")::text = 'PUBLIC'::text) AND (("C"."caseType")::text =
("WPCT"."caseType")::text) AND (("C"."countyNo")::smallint =
("WPCT"."countyNo")::smallint))
-> Index Scan using
"CaseDispo_pkey" on "CaseDispo" "CD" (cost=0.00..3.59 rows=1 width=18)
Index Cond:
((("CD"."countyNo")::smallint = ("C"."countyNo")::smallint) AND
(("CD"."caseNo")::text = ("C"."caseNo")::text))
Filter: (NOT (subplan))
SubPlan
-> Index Scan using
"CaseDispo_pkey" on "CaseDispo" "CD2" (cost=0.00..2.58 rows=1 width=0)
Index Cond:
((("countyNo")::smallint = ($4)::smallint) AND (("caseNo")::text = ($3)::text)
AND (("dispoDate")::date > ($5)::date))
-> Index Scan using
"LastCaseMod_pkey" on "LastCaseMod" "LCM" (cost=0.00..2.87 rows=1 width=26)
Index Cond:
((("LCM"."countyNo")::smallint = ("C"."countyNo")::smallint) AND
(("LCM"."caseNo")::text = ("C"."caseNo")::text) AND
(("LCM"."updateTypeId")::integer = 0))
-> Index Scan using "Party_pkey" on
"Party" "P" (cost=0.00..3.19 rows=1 width=26)
Index Cond:
((("C"."countyNo")::smallint = ("P"."countyNo")::smallint) AND
(("C"."caseNo")::text = ("P"."caseNo")::text) AND (("P"."partyNo")::smallint =
("AL"."partyNo")::smallint))
Filter: (NOT "isSeal")
SubPlan
-> Index Scan using "CaseHist_pkey"
on "CaseHist" "CHPET" (cost=0.00..109.25 rows=1 width=0)
Index Cond: ((($0)::smallint =
("countyNo")::smallint) AND (($1)::text = ("caseNo")::text))
Filter: (("eventType")::text =
ANY (('{FWBCA,CCTRO}'::character varying[])::text[]))
-> Index Scan using "County_pkey" on "County"
"CY" (cost=0.00..1.87 rows=1 width=12)
Index Cond: (("C"."countyNo")::smallint
= ("CY"."countyNo")::smallint)
-> Index Scan using "StatusCode_pkey" on
"StatusCode" "S" (cost=0.00..0.27 rows=1 width=18)
Index Cond: (("C"."statusCode")::text =
("S"."statusCode")::text)
SubPlan
-> Index Scan using "CaseHist_pkey" on "CaseHist"
"CH" (cost=0.00..109.25 rows=1 width=0)
Index Cond: ((($0)::smallint =
("countyNo")::smallint) AND (($1)::text = ("caseNo")::text))
Filter: (("eventType")::text = ANY
(('{FWBCA,CCTRO}'::character varying[])::text[]))
(88 rows)
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
