Re: two queries and dual cpu (perplexed) - Mailing list pgsql-performance

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

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
loops=1)
         ->  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",
"CurrentAusClimate"."iMonth"
               ->  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:
"ClimateVariables"."ClimateId"
                                                   ->  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:
"GetFutureClimateParameters"."ClimateId"
                                                   ->  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" =
"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: 443983.269 ms
(25 rows)


Sheeeesshh...

> 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
it!