Re: Help optimize view - Mailing list pgsql-performance

> >>> On Mon, Aug 13, 2007 at 10:35 AM, in message
> <1806D1F73FCB7F439F2C842EE0627B18065BF2C0@USA0300MS01.na.xerox
> .net>, "Relyea, Mike" <Mike.Relyea@xerox.com> wrote:
> > I'm running 8.2.4 on Windows XP with 1.5 GB memory.
> > shared_buffers = 12288
> > effective_cache_size = 10000
>
> For starters, you might want to adjust one or both of these.
> It looks to me like you're telling it that it only has 78.125
> MB cache space.  That will make it tend to want to scan
> entire tables, on the assumption that the cache hit ratio
> will be poor for random reads.
>
> Since you're on 8.2.4, you can use units of measure to help
> make this easier to read.  You could, for example, say:
>
> shared_buffers = 96MB
> effective_cache_size = 1200MB
>
> -Kevin

I've increased shared_buffers to 128MB, and restarted the server.  My
total run time didn't really change.

SELECT set_config('effective_cache_size', '1000MB', false); I have
another app that uses about 500MB.
SELECT set_config('join_collapse_limit', '20', false);

explain analyze 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;

HashAggregate (cost=489274.71..489372.94 rows=7858 width=70) (actual
time=117632.844..117663.228 rows=14853 loops=1)
 -> Hash Join (cost=256774.03..489137.20 rows=7858 width=70) (actual
time=50297.022..117530.665 rows=15123 loops=1)
    Hash Cond: ("ParameterValues"."MeasurementID" =
"Measurements"."MeasurementID")
    -> Nested Loop (cost=8054.81..238636.75 rows=454040 width=21)
(actual time=172.341..66959.288 rows=289724 loops=1)
       -> Seq Scan on "ParameterNames" (cost=0.00..1.94 rows=1 width=17)
(actual time=0.020..0.034 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=172.297..66241.380 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=147.690..147.690
rows=289724 loops=1)
             Index Cond: ("ParameterNames"."ParameterID" =
"ParameterValues"."ParameterID")
    -> Hash (cost=247087.84..247087.84 rows=130510 width=57) (actual
time=50109.022..50109.022 rows=15123 loops=1)
       -> Hash Join (cost=8141.52..247087.84 rows=130510 width=57)
(actual time=11095.022..50057.777 rows=15123 loops=1)
          Hash Cond: ("Measurements"."psaID" =
"PrintSampleAnalyses"."psaID")
          -> Hash Join (cost=1.77..234364.57 rows=661492 width=21)
(actual time=31.457..48123.380 rows=289724 loops=1)
             Hash Cond: ("Measurements"."MetricID" =
"AnalysisModules"."MetricID")
             -> Seq Scan on "Measurements" (cost=0.00..199469.09
rows=7541009 width=12) (actual time=10.920..37814.792 rows=7539838
loops=1)
             -> Hash (cost=1.71..1.71 rows=5 width=17) (actual
time=0.066..0.066 rows=5 loops=1)
                -> Seq Scan on "AnalysisModules" (cost=0.00..1.71 rows=5
width=17) (actual time=0.032..0.049 rows=5 loops=1)
                   Filter: (("AnalysisModuleName")::text = 'NMF'::text)
          -> Hash (cost=7949.67..7949.67 rows=15206 width=44) (actual
time=1424.025..1424.025 rows=18901 loops=1)
             -> Hash Join (cost=5069.24..7949.67 rows=15206 width=44)
(actual time=1007.901..1387.787 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=4.432..153.090 rows=78859 loops=1)
                -> Hash (cost=4879.10..4879.10 rows=15211 width=44)
(actual time=1003.424..1003.424 rows=18901 loops=1)
                   -> Hash Join (cost=2220.11..4879.10 rows=15211
width=44) (actual time=348.841..968.194 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=22.080..427.303 rows=78859 loops=1)
                      -> Hash (cost=2029.98..2029.98 rows=15211
width=44) (actual time=326.703..326.703 rows=18645 loops=1)
                         -> Hash Join (cost=564.39..2029.98 rows=15211
width=44) (actual time=90.425..293.223 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=90.188..221.310
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=72.897..72.897 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.210..0.210 rows=21 loops=1)
                               -> Hash Join (cost=1.09..2.72 rows=22
width=30) (actual time=0.070..0.168 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.022..0.056 rows=30
loops=1)
                                  -> Hash (cost=1.05..1.05 rows=3
width=4) (actual time=0.026..0.026 rows=3 loops=1)
                                     -> Seq Scan on "tblColors"
(cost=0.00..1.05 rows=3 width=4) (actual time=0.008..0.014 rows=3
loops=1)
                                        Filter: ("ColorID" <> 3)
Total runtime: 117692.834 ms