Please see attached the output from explain analyse. This is with the 

        shared_buffers = 10600
        work_mem = 102400
        enable_seqscan = true

BTW I guess should mention that I am doing the select count(*) on a View.

Ran the Explain analyse with the nestedloop disabled but it was taking 
forever... and killed it after 30mins.

Thanks
shoaib
On Thu, 21 Apr 2005, John A Meinel wrote:

> Shoaib Burq (VPAC) wrote:
> 
> >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";
> >
> >
> Actually, you probably don't want enable_seqscan=off, you should try:
> SET enable_nestloop TO off.
> The problem is that it is estimating there will only be 44 rows, but in
> reality there are 13M rows. It almost definitely should be doing a
> seqscan with a sort and merge join.
> 
> Also, please attach you explain analyzes, the wrapping is really hard to
> read.
> 
> I don't understand how postgres could get the number of rows that wrong.
> 
> It seems to be misestimating the number of entries in IX_ClimateId
> 
> Here:
> 
> ->  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)
> 
> The first index scan is costing you 0.006*13276368=79s, and the second one is 
> 119s.
> 
> I can't figure out exactly what is where from the formatting, but the query 
> that seems misestimated is:
> ->  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")
> 
> Is there an unexpected correlaction between
> ClimateChangeModel40"."ClimateId" and whatever "outer" is at this point?
> 
> John
> =:->
> 
> 

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)
---------------------------(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