Re: Help optimize view - Mailing list pgsql-performance

From Relyea, Mike
Subject Re: Help optimize view
Date
Msg-id 1806D1F73FCB7F439F2C842EE0627B18065BED58@usa0300ms01.na.xerox.net
Whole thread Raw
Responses Re: Help optimize view  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-performance
Oops.  Realized I posted the wrong SQL and EXPLAIN ANALYZE results.
Also forgot to mention that my "server" has 1.5 GB memory.


SELECT "PrintSamples"."MachineID", "PrintSamples"."PrintCopyID",
"tblColors"."ColorID", avg("ParameterValues"."ParameterValue") AS
"Mottle_NMF"
   FROM "AnalysisModules"
   JOIN ("tblColors"
   JOIN ("tblTPNamesAndColors"
   JOIN "PrintSamples" ON "tblTPNamesAndColors"."TestPatternName"::text
= "PrintSamples"."TestPatternName"::text
   JOIN ("DigitalImages"
   JOIN "PrintSampleAnalyses" ON "DigitalImages"."ImageID" =
"PrintSampleAnalyses"."ImageID"
   JOIN ("ParameterNames"
   JOIN ("Measurements"
   JOIN "ParameterValues" ON "Measurements"."MeasurementID" =
"ParameterValues"."MeasurementID") ON "ParameterNames"."ParameterID" =
"ParameterValues"."ParameterID") ON "PrintSampleAnalyses"."psaID" =
"Measurements"."psaID") ON "PrintSamples"."PrintSampleID" =
"DigitalImages"."PrintSampleID") ON "tblColors"."ColorID" =
"tblTPNamesAndColors"."ColorID") ON "AnalysisModules"."MetricID" =
"Measurements"."MetricID"
  GROUP BY "PrintSamples"."MachineID", "PrintSamples"."PrintCopyID",
"tblColors"."ColorID", "AnalysisModules"."AnalysisModuleName",
"ParameterNames"."ParameterName", "PrintSamples"."TestPatternName"
 HAVING "AnalysisModules"."AnalysisModuleName"::text = 'NMF'::text AND
"ParameterNames"."ParameterName"::text = 'NMF'::text AND
"tblColors"."ColorID" <> 3 AND "PrintSamples"."TestPatternName"::text ~~
'IQAF-TP8%'::text;


QUERY PLAN
HashAggregate (cost=519801.96..519898.00 rows=7683 width=70) (actual
time=106219.710..106249.456 rows=14853 loops=1)
 -> Hash Join (cost=286101.76..519667.51 rows=7683 width=70) (actual
time=50466.513..106111.635 rows=15123 loops=1)
    Hash Cond: ("Measurements"."MetricID" =
"AnalysisModules"."MetricID")
    -> Hash Join (cost=286099.98..519260.45 rows=87588 width=61) (actual
time=50466.417..106055.182 rows=15123 loops=1)
       Hash Cond: ("ParameterValues"."MeasurementID" =
"Measurements"."MeasurementID")
       -> Nested Loop (cost=8054.81..238636.75 rows=454040 width=21)
(actual time=143.017..55178.583 rows=289724 loops=1)
          -> Seq Scan on "ParameterNames" (cost=0.00..1.94 rows=1
width=17) (actual time=0.012..0.027 rows=1 loops=1)
             Filter: (("ParameterName")::text = 'NMF'::text)
          -> Bitmap Heap Scan on "ParameterValues"
(cost=8054.81..231033.70 rows=608089 width=12)

(actual time=142.986..54432.650 rows=289724 loops=1)
             Recheck Cond: ("ParameterNames"."ParameterID" =
"ParameterValues"."ParameterID")
             -> Bitmap Index Scan on "PVParameterID_idx"
(cost=0.00..7902.79 rows=608089 width=0)

(actual time=109.178..109.178 rows=289724 loops=1)
                Index Cond: ("ParameterNames"."ParameterID" =
"ParameterValues"."ParameterID")
       -> Hash (cost=259861.12..259861.12 rows=1454724 width=48) (actual
time=50306.950..50306.950 rows=961097 loops=1)
          -> Hash Join (cost=8139.75..259861.12 rows=1454724 width=48)
                (actual time=971.910..48649.190
rows=961097 loops=1)
             Hash Cond: ("Measurements"."psaID" =
"PrintSampleAnalyses"."psaID")
             -> Seq Scan on "Measurements" (cost=0.00..199469.09
rows=7541009 width=12)
                            (actual
time=0.047..35628.599 rows=7539838 loops=1)
             -> Hash (cost=7949.67..7949.67 rows=15206 width=44) (actual
time=971.734..971.734 rows=18901 loops=1)
                -> Hash Join (cost=5069.24..7949.67 rows=15206 width=44)
(actual time=590.003..938.744 rows=18901 loops=1)
                   Hash Cond: ("PrintSampleAnalyses"."ImageID" =
"DigitalImages"."ImageID")
                   -> Seq Scan on "PrintSampleAnalyses"
(cost=0.00..2334.25 rows=78825 width=8)

(actual time=0.021..130.335 rows=78859 loops=1)
                   -> Hash (cost=4879.10..4879.10 rows=15211 width=44)
                    (actual time=589.940..589.940
rows=18901 loops=1)
                      -> Hash Join (cost=2220.11..4879.10 rows=15211
width=44)
                        (actual
time=168.307..557.675 rows=18901 loops=1)
                         Hash Cond: ("DigitalImages"."PrintSampleID" =
"PrintSamples"."PrintSampleID")
                         -> Seq Scan on "DigitalImages"
(cost=0.00..1915.50 rows=78850 width=8)

(actual time=16.126..194.911 rows=78859 loops=1)
                         -> Hash (cost=2029.98..2029.98 rows=15211
width=44)
                        (actual
time=152.128..152.128 rows=18645 loops=1)
                            -> Hash Join (cost=564.39..2029.98
rows=15211 width=44)
                            (actual
time=13.951..121.903 rows=18645 loops=1)
                               Hash Cond:
(("PrintSamples"."TestPatternName")::text =

("tblTPNamesAndColors"."TestPatternName")::text)
                               -> Bitmap Heap Scan on "PrintSamples"
(cost=561.39..1781.53 rows=24891 width=40)

(actual time=13.680..59.919 rows=24914 loops=1)
                                  Filter: (("TestPatternName")::text ~~
'IQAF-TP8%'::text)
                                  -> Bitmap Index Scan on
"PSTestPatternName_idx" (cost=0.00..555.17 rows=24891 width=0)

(actual time=13.487..13.487 rows=24914 loops=1)
                                     Index Cond:
((("TestPatternName")::text >= 'IQAF-TP8'::character varying) AND
(("TestPatternName")::text < 'IQAF-TP9'::character varying))
                               -> Hash (cost=2.72..2.72 rows=22
width=30) (actual time=0.242..0.242 rows=21 loops=1)
                                  -> Hash Join (cost=1.09..2.72 rows=22
width=30)
                                (actual
time=0.101..0.200 rows=21 loops=1)
                                     Hash Cond:
("tblTPNamesAndColors"."ColorID" = "tblColors"."ColorID")
                                     -> Seq Scan on
"tblTPNamesAndColors" (cost=0.00..1.30 rows=30 width=30)

(actual time=0.050..0.085 rows=30 loops=1)
                                     -> Hash (cost=1.05..1.05 rows=3
width=4) (actual time=0.028..0.028 rows=3 loops=1)
                                        -> Seq Scan on "tblColors"
(cost=0.00..1.05 rows=3 width=4)

(actual time=0.009..0.016 rows=3 loops=1)
                                           Filter: ("ColorID" <> 3)
    -> Hash (cost=1.71..1.71 rows=5 width=17) (actual time=0.072..0.072
rows=5 loops=1)
       -> Seq Scan on "AnalysisModules" (cost=0.00..1.71 rows=5
width=17) (actual time=0.038..0.055 rows=5 loops=1)
          Filter: (("AnalysisModuleName")::text = 'NMF'::text)
Total runtime: 106358.738 ms

pgsql-performance by date:

Previous
From: "Jignesh K. Shah"
Date:
Subject: Re: CLOG Patch
Next
From: "Kevin Grittner"
Date:
Subject: Bitmap Index Scan optimization opportunity