Re: two queries and dual cpu (perplexed)

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=13276368loops=1) 
> >                            ->  Nested Loop  (cost=2.19..1067038.94 rows=44 width=52) (actual time=87.255..49743.796
rows=13276368loops=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
meansit'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
statisticson 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
shouldgive 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