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