I knew I would forget to include some obvious info. I'm running PG
8.0.x on WinXP and have vacuum full analyzed.
-----Original Message-----
From: Relyea, Mike
Sent: Monday, June 27, 2005 5:10 PM
To: 'pgsql-general@xxxxxxxxxxxxxx'
Subject: Indexes not used - what am I missing?
Given my query:
====================================================
SELECT "PrintSamples"."MachineID" , "PrintSamples"."PrintCopyID" ,
"tblColors"."Color" , "tblBlockAC"."AreaCoverage" ,
"tblFriendlyParameterNames"."FriendlyParameterName" AS "Measurement" ,
"ParameterValues"."ParameterValue" AS "Value" FROM "AnalysisModules"
INNER JOIN (("tblBlockAC" INNER JOIN "Targets" ON
"tblBlockAC"."TargetID" = "Targets"."TargetID") INNER JOIN (("tblColors"
INNER JOIN ("tblTPNamesAndColors" INNER JOIN "PrintSamples" ON
"tblTPNamesAndColors"."TestPatternName" =
"PrintSamples"."TestPatternName") ON "tblColors"."ColorID" =
"tblTPNamesAndColors"."ColorID") INNER JOIN (("DigitalImages" INNER JOIN
"PrintSampleAnalyses" ON "DigitalImages"."ImageID" =
"PrintSampleAnalyses"."ImageID") INNER JOIN
(("tblFriendlyParameterNames" INNER JOIN "ParameterNames" ON
"tblFriendlyParameterNames"."ParameterID" =
"ParameterNames"."ParameterID") INNER JOIN ("Measurements" INNER JOIN
"ParameterValues" ON "Measurements"."MeasurementID" =
"ParameterValues"."MeasurementID") ON "ParameterNames"."ParameterID" =
"ParameterValues"."ParameterID") ON "PrintSampleAnalyses"."psaID" =
"Measurements"."psaID") ON "PrintSamples"."PrintSampleID" =
"DigitalImages"."PrintSampleID") ON "Targets"."TargetID" =
"Measurements"."TargetID") ON "AnalysisModules"."MetricID" =
"Measurements"."MetricID" WHERE (("PrintSamples"."MachineID" = '3179')
AND (("AnalysisModules"."AnalysisModuleName")='DeltaE') AND
(("PrintSamples"."TestPatternName") Like '%TP8%') AND
(("ParameterNames"."ParameterName") Like 'Mean%')) ORDER BY
"PrintSamples"."MachineID" , "PrintSamples"."PrintCopyID" ,
"tblBlockAC"."AreaCoverage" , "tblColors"."ColorID" ,
"tblFriendlyParameterNames"."FriendlyParameterName" ;
My explain analyze output:
====================================================
QUERY PLAN
Sort (cost=429075.71..429075.71 rows=2 width=45) (actual
time=44984.000..44984.000 rows=2352 loops=1)
Sort Key: "PrintSamples"."MachineID"
-> Nested Loop (cost=415721.80..429075.70 rows=2 width=45) (actual
time=44828.000..44968.000 rows=2352 loops=1)
-> Nested Loop (cost=415721.80..429069.64 rows=2 width=53) (actual
time=44828.000..44937.000 rows=2352 loops=1)
-> Hash Join (cost=415721.80..429054.51 rows=5 width=45) (actual
time=44828.000..44874.000 rows=2352 loops=1)
Hash Cond: ("outer"."MetricID" = "inner"."MetricID")
-> Hash Join (cost=415720.44..429052.95 rows=31 width=49) (actual
time=44828.000..44874.000 rows=2352 loops=1)
Hash Cond: ("outer"."PrintSampleID" = "inner"."PrintSampleID")
-> Hash Join (cost=415665.42..428234.44 rows=152635 width=28)
(actual time=35234.000..43656.000 rows=835385 loops=1)
Hash Cond: ("outer"."psaID" = "inner"."psaID")
-> Merge Join (cost=414212.50..424585.45 rows=146405 width=28)
(actual time=35062.000..41235.000 rows=835385 loops=1)
Merge Cond: ("outer"."MeasurementID" = "inner"."MeasurementID")
-> Sort (cost=202227.03..202593.04 rows=146405 width=20) (actual
time=27140.000..27451.000 rows=835385 loops=1)
Sort Key: "ParameterValues"."MeasurementID"
-> Hash Join (cost=3.23..189665.77 rows=146405 width=20) (actual
time=15.000..25139.000 rows=835385 loops=1)
Hash Cond: ("outer"."ParameterID" = "inner"."ParameterID")
-> Seq Scan on "ParameterValues" (cost=0.00..143853.18 rows=8787918
width=12) (actual time=0.000..13302.000 rows=8787896 loops=1)
-> Hash (cost=3.23..3.23 rows=1 width=20) (actual time=0.000..0.000
rows=0 loops=1)
-> Hash Join (cost=1.62..3.23 rows=1 width=20) (actual
time=0.000..0.000 rows=4 loops=1)
Hash Cond: ("outer"."ParameterID" = "inner"."ParameterID")
-> Seq Scan on "tblFriendlyParameterNames" (cost=0.00..1.40 rows=40
width=16) (actual time=0.000..0.000 rows=40 loops=1)
-> Hash (cost=1.61..1.61 rows=1 width=4) (actual time=0.000..0.000
rows=0 loops=1)
-> Seq Scan on "ParameterNames" (cost=0.00..1.61 rows=1 width=4)
(actual time=0.000..0.000 rows=4 loops=1)
Filter: (("ParameterName")::text ~~ 'Mean%'::text)
-> Sort (cost=211985.47..216074.23 rows=1635502 width=16) (actual
time=7922.000..9003.000 rows=2145644 loops=1)
Sort Key: "Measurements"."MeasurementID"
-> Seq Scan on "Measurements" (cost=0.00..43191.02 rows=1635502
width=16) (actual time=0.000..3598.000 rows=1635476 loops=1)
-> Hash (cost=1403.55..1403.55 rows=19748 width=8) (actual
time=157.000..157.000 rows=0 loops=1)
-> Hash Join (cost=529.85..1403.55 rows=19748 width=8) (actual
time=63.000..142.000 rows=19748 loops=1)
Hash Cond: ("outer"."ImageID" = "inner"."ImageID")
-> Seq Scan on "PrintSampleAnalyses" (cost=0.00..577.48 rows=19748
width=8) (actual time=0.000..16.000 rows=19748 loops=1)
-> Hash (cost=480.48..480.48 rows=19748 width=8) (actual
time=63.000..63.000 rows=0 loops=1)
-> Seq Scan on "DigitalImages" (cost=0.00..480.48 rows=19748
width=8) (actual time=0.000..32.000 rows=19748 loops=1)
-> Hash (cost=55.01..55.01 rows=4 width=29) (actual
time=0.000..0.000 rows=0 loops=1)
-> Hash Join (cost=53.33..55.01 rows=4 width=29) (actual
time=0.000..0.000 rows=29 loops=1)
Hash Cond: ("outer"."ColorID" = "inner"."ColorID")
-> Hash Join (cost=52.28..53.90 rows=4 width=20) (actual
time=0.000..0.000 rows=29 loops=1)
Hash Cond: (("outer"."TestPatternName")::text =
("inner"."TestPatternName")::text)
-> Seq Scan on "tblTPNamesAndColors" (cost=0.00..1.26 rows=26
width=29) (actual time=0.000..0.000 rows=26 loops=1)
-> Hash (cost=52.27..52.27 rows=4 width=44) (actual
time=0.000..0.000 rows=0 loops=1)
-> Index Scan using "PSMachineID_idx" on "PrintSamples"
(cost=0.00..52.27 rows=4 width=44) (actual time=0.000..0.000 rows=29
loops=1)
Index Cond: (("MachineID")::text = '3179'::text)
Filter: (("TestPatternName")::text ~~ '%TP8%'::text)
-> Hash (cost=1.04..1.04 rows=4 width=13) (actual time=0.000..0.000
rows=0 loops=1)
-> Seq Scan on "tblColors" (cost=0.00..1.04 rows=4 width=13) (actual
time=0.000..0.000 rows=4 loops=1)
-> Hash (cost=1.35..1.35 rows=4 width=4) (actual time=0.000..0.000
rows=0 loops=1)
-> Seq Scan on "AnalysisModules" (cost=0.00..1.35 rows=4 width=4)
(actual time=0.000..0.000 rows=3 loops=1)
Filter: (("AnalysisModuleName")::text = 'DeltaE'::text)
-> Index Scan using "tblBlockAC_pkey" on "tblBlockAC"
(cost=0.00..3.01 rows=1 width=8) (actual time=0.013..0.013 rows=1
loops=2352)
Index Cond: ("outer"."TargetID" = "tblBlockAC"."TargetID")
-> Index Scan using "Targets_pkey" on "Targets" (cost=0.00..3.01
rows=1 width=4) (actual time=0.006..0.013 rows=1 loops=2352)
Index Cond: ("Targets"."TargetID" = "outer"."TargetID")
Total runtime: 45312.000 ms
My indices:
====================================================
CREATE INDEX "PVMeasurementID_idx" ON
"ParameterValues"("MeasurementID");
CREATE INDEX "PVParameterID_idx" ON "ParameterValues"("ParameterID");
CREATE INDEX "PSTestPatternName_idx" ON
"PrintSamples"("TestPatternName");
CREATE INDEX "PSMachineID_idx" ON "PrintSamples"("MachineID");
CREATE INDEX "PSMachineIDTestPatternName_idx" ON
"PrintSamples"("MachineID", "TestPatternName");
CREATE INDEX "DIPrintSampleID_idx" ON "DigitalImages"("PrintSampleID");
CREATE INDEX "PSAImageID_idx" ON "PrintSampleAnalyses"("ImageID");
CREATE INDEX "MpsaID_idx" ON "Measurements"("psaID");
CREATE INDEX "MMetricID_idx" ON "Measurements"("MetricID");
CREATE INDEX "MTargetID_idx" ON "Measurements"("TargetID");
CREATE INDEX "AMAnalysisModuleName_idx" ON
"AnalysisModules"("AnalysisModuleName");
Why aren't more of my indices used? Especially "MpsaID_idx",
"MMetricID_idx", "MTargetID_idx", "PVMeasurementID_idx" or
"PVParameterID_idx". What am I missing?
Mike
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
