Just tried it with the following changes:
shared_buffers = 10600
work_mem = 102400
enable_seqscan = false
still no improvement
Ok here's the Plan with the enable_seqscan = false:
ausclimate=# explain ANALYZE select count(*) from "getfutureausclimate";
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=101069350.74..101069350.74 rows=1 width=0) (actual
time=461651.787..461651.787 rows=1 loops=1)
-> Subquery Scan getfutureausclimate (cost=101069350.50..101069350.70
rows=16 width=0) (actual time=426142.382..454571.397 rows=13276368
loops=1)
-> Sort (cost=101069350.50..101069350.54 rows=16 width=58)
(actual time=426142.375..444428.278 rows=13276368 loops=1)
Sort Key: "Aus40_DEM"."AusPosNumber",
"CurrentAusClimate"."iMonth"
-> Nested Loop (cost=100000001.02..101069350.18 rows=16
width=58) (actual time=72.740..366588.646 rows=13276368 loops=1)
-> Nested Loop (cost=100000001.02..101067308.96
rows=44 width=68) (actual time=35.788..184032.873 rows=13276368 loops=1)
-> Nested Loop
(cost=100000001.02..101067043.83 rows=44 width=52) (actual
time=35.753..47971.652 rows=13276368 loops=1)
-> Nested Loop
(cost=100000001.02..100000012.98 rows=1 width=32) (actual
time=7.433..7.446 rows=1 loops=1)
-> Merge Join
(cost=100000001.02..100000007.13 rows=1 width=24) (actual
time=7.403..7.412 rows=1 loops=1)
Merge Cond:
("outer"."ClimateId" = "inner"."ClimateId")
-> Index Scan using
"PK_ClimateVariables" on "ClimateVariables" (cost=0.00..6.08 rows=7
width=10) (actual time=0.011..0.015 rows=3 loops=1)
-> Sort
(cost=100000001.02..100000001.03 rows=1 width=14) (actual
time=7.374..7.375 rows=1 loops=1)
Sort Key:
"GetFutureClimateParameters"."ClimateId"
-> Seq Scan on
"GetFutureClimateParameters" (cost=100000000.00..100000001.01 rows=1
width=14) (actual time=7.361..7.362 rows=1 loops=1)
-> Index Scan using
"PK_ScenarioEmissionLevels" on "ScenarioEmissionLevels" (cost=0.00..5.83
rows=1 width=18) (actual time=0.021..0.024 rows=1 loops=1)
Index Cond:
(("ScenarioEmissionLevels"."ScenarioId" = "outer"."ScenarioId") AND
("ScenarioEmissionLevels"."iYear" = "outer"."iYear") AND
("ScenarioEmissionLevels"."LevelId" = "outer"."LevelId"))
-> Index Scan using "IX_ClimateId" on
"ClimateChangeModel40" (cost=0.00..1063711.75 rows=265528 width=20)
(actual time=28.311..17212.703 rows=13276368 loops=1)
Index Cond: ("outer"."ClimateId" =
"ClimateChangeModel40"."ClimateId")
-> Index Scan using "PK_Aus40_DEM" on
"Aus40_DEM" (cost=0.00..6.01 rows=1 width=16) (actual time=0.005..0.006
rows=1 loops=13276368)
Index Cond: ("outer"."AusPosNumber" =
"Aus40_DEM"."AusPosNumber")
-> Index Scan using "PK_CurrentAusClimate" on
"CurrentAusClimate" (cost=0.00..46.20 rows=11 width=14) (actual
time=0.007..0.009 rows=1 loops=13276368)
Index Cond: (("CurrentAusClimate"."ClimateId" =
"outer"."ClimateId") AND ("outer"."AusPosNumber" =
"CurrentAusClimate"."AusPosNum") AND ("CurrentAusClimate"."iMonth" =
"outer"."iMonth"))
Total runtime: 462218.120 ms
(23 rows)
On Thu, 21 Apr 2005, Russell Smith wrote:
> On Thu, 21 Apr 2005 10:44 pm, Shoaib Burq (VPAC) wrote:
> > -> Nested Loop (cost=2.19..1069345.29 rows=16 width=58) (actual
> > time=135.390..366902.373 rows=13276368 loops=1)
> > -> Nested Loop (cost=2.19..1067304.07 rows=44
> > width=68) (actual time=107.627..186390.137 rows=13276368 loops=1)
> > -> Nested Loop (cost=2.19..1067038.94 rows=44
> > width=52) (actual time=87.255..49743.796 rows=13276368 loops=1)
>
> OUCH, OUCH, OUCH.
>
> Most if not all of the time is going on nested loop joins. The tuple
> estimates are off by a factore of 10^6 which is means it's chosing the wrong
> join type.
>
> you could set enable_seqscan to OFF; to test what he performance is like
> with a different plan, and then set it back on.
>
> However you really need to get the row count estimates up to something
> comparable. within a factor of 10 at least.
> A number of the other rows estimates seem to be off by a reasonable amount
> too. You may want to bump up the statistics on the relevant
> columns. I can't find what they are from looking at that, I probably should
> be able too, but it's late.
>
> If you get the stats up to something near the real values, then the planner
> will choose a different plan, which should give a huge performance
> increase.
>
> Regards
>
> Russell Smith.
>
>
--
Shoaib Burq
--
VPAC - Geospatial Applications Developer
Building 91, 110 Victoria Street,
Carlton South, Vic 3053, Australia
_______________________________________________________________
w: www.vpac.org | e: sab_AT_vpac_DOT_org | mob: +61.431-850039
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
