here's explain sorry about the mess: I can attach it as text-file if you 

ausclimate=# explain ANALYZE  select count(*) from "getfutureausclimate";

QUERY PLAN                                                                      
 Aggregate  (cost=1069345.85..1069345.85 rows=1 width=0) (actual 
time=443241.241..443241.242 rows=1 loops=1)
   ->  Subquery Scan getfutureausclimate  (cost=1069345.61..1069345.81 
rows=16 width=0) (actual time=411449.034..436165.259 rows=13276368 
         ->  Sort  (cost=1069345.61..1069345.65 rows=16 width=58) (actual 
time=411449.026..426001.199 rows=13276368 loops=1)
               Sort Key: "Aus40_DEM"."AusPosNumber", 
               ->  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)
                                 ->  Nested Loop  (cost=2.19..8.09 rows=1 
width=32) (actual time=52.684..52.695 rows=1 loops=1)
                                       ->  Merge Join  (cost=2.19..2.24 
rows=1 width=24) (actual time=28.000..28.007 rows=1 loops=1)
                                             Merge Cond: 
("outer"."ClimateId" = "inner"."ClimateId")
                                             ->  Sort  (cost=1.17..1.19 
rows=7 width=10) (actual time=10.306..10.307 rows=3 loops=1)
                                                   Sort Key: 
                                                   ->  Seq Scan on 
"ClimateVariables"  (cost=0.00..1.07 rows=7 width=10) (actual 
time=10.277..10.286 rows=7 loops=1)
                                             ->  Sort  (cost=1.02..1.02 
rows=1 width=14) (actual time=17.679..17.680 rows=1 loops=1)
                                                   Sort Key: 
                                                   ->  Seq Scan on 
"GetFutureClimateParameters"  (cost=0.00..1.01 rows=1 width=14) (actual 
time=17.669..17.671 rows=1 loops=1)
                                       ->  Index Scan using 
"PK_ScenarioEmissionLevels" on "ScenarioEmissionLevels"  (cost=0.00..5.83 
rows=1 width=18) (actual time=24.676..24.679 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=34.564..19435.855 rows=13276368 loops=1)
                                       Index Cond: ("outer"."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" = 
                     ->  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" = 
 Total runtime: 443983.269 ms
(25 rows)


> You should really, really bump up shared_buffers and given you have 8GB 
> of ram this query would likely benefit from more work_mem.

I actually tried that and there was a decrease in performance. Are the
shared_buffers and work_mem the only things I should change to start with? 
If so what's the reasoning.

> Is this an IO intensive query?  If running both in parellel results in 
> 2x the run time and you have sufficient cpus it would (to me) indicate 
> you don't have enough IO bandwidth to satisfy the query.

Yes I think so too: ... I am just compiling some io stats...

Also will jump on to irc...

Whoa! thanks all... I am overwhelmed with the help I am getting... I love 

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Reply via email to