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
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
