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