OK ... so just to clearify... (and pardon my ignorance):
I need to increase the value of 'default_statistics_target' variable and
then run VACUUM ANALYZE, right? If so what should I choose for the
'default_statistics_target'?
BTW I only don't do any sub-selection on the View.
I have attached the view in question and the output of:
SELECT oid , relname, relpages, reltuples
FROM pg_class ORDER BY relpages DESC;
reg
shoaib
On Sat, 23 Apr 2005, Tom Lane wrote:
> John A Meinel <[EMAIL PROTECTED]> writes:
> > 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.
>
> Not nestloops anyway.
>
> > I don't understand how postgres could get the number of rows that wrong.
>
> No stats, or out-of-date stats is the most likely bet.
>
> > 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")
>
> Yeah, that's what jumped out at me too. It's not the full explanation
> for the join number being so far off, but this one at least you have a
> chance to fix by updating the stats on ClimateChangeModel40.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
>
--
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
oid | relname | relpages | reltuples
-----------+---------------------------------+----------+-------------
16996 | CurrentAusClimate | 474551 | 8.06736e+07
16983 | ClimateChangeModel40 | 338252 | 5.31055e+07
157821816 | PK_CurrentAusClimate | 265628 | 8.06736e+07
157835995 | idx_climateid | 176645 | 8.06736e+07
157835996 | idx_ausposnum | 176645 | 8.06736e+07
157835997 | idx_climatevalue | 176645 | 8.06736e+07
157821808 | PK_ClimateModelChange_40 | 174858 | 5.31055e+07
157821788 | IX_iMonth001 | 116280 | 5.31055e+07
157821787 | IX_ClimateId | 116280 | 5.31055e+07
157821786 | IX_AusPosNumber | 116280 | 5.31055e+07
17034 | NeighbourhoodTable | 54312 | 1.00476e+07
157821854 | PK_NeighbourhoodTable | 27552 | 1.00476e+07
157821801 | IX_NeighbourhoodId | 22002 | 1.00476e+07
157821800 | IX_NAusPosNumber | 22002 | 1.00476e+07
157821799 | IX_AusPosNumber006 | 22002 | 1.00476e+07
17012 | FutureEvapMonth | 12026 | 1.10636e+06
17014 | FutureMaxTMonth | 12026 | 1.10636e+06
17016 | FutureMinTMonth | 12026 | 1.10636e+06
17018 | FutureRainMonth | 12026 | 1.10636e+06
17000 | CurrentEvapMonth | 8239 | 1.12047e+06
17002 | CurrentMaxTMonth | 8239 | 1.12047e+06
17004 | CurrentMinTMonth | 8239 | 1.12047e+06
17006 | CurrentR_RMonth | 8239 | 1.12047e+06
17008 | CurrentRadMonth | 8239 | 1.12047e+06
17010 | CurrentRainMonth | 8239 | 1.12047e+06
16977 | Aus40_DEM | 6591 | 1.12047e+06
16979 | Aus40DemRandom | 6057 | 1.12047e+06
16981 | ClimateChange | 3752 | 543984
157821780 | IX_Random | 3075 | 1.12047e+06
157821832 | PK_FutureEvapMonth | 3036 | 1.10636e+06
157821834 | PK_FutureMaxTMonth | 3036 | 1.10636e+06
157821836 | PK_FutureMinTMonth | 3036 | 1.10636e+06
157821838 | PK_FutureRainMonth | 3036 | 1.10636e+06
157821804 | PK_Aus40DemRandom | 2456 | 1.12047e+06
157821802 | PK_Aus40_DEM | 2456 | 1.12047e+06
157821820 | PK_CurrentEvapMonth | 2456 | 1.12047e+06
157821822 | PK_CurrentMaxTMonth | 2456 | 1.12047e+06
157821824 | PK_CurrentMinTMonth | 2456 | 1.12047e+06
157821826 | PK_CurrentR_RMonth | 2456 | 1.12047e+06
157821828 | PK_CurrentRadMonth | 2456 | 1.12047e+06
157821830 | PK_CurrentRainMonth | 2456 | 1.12047e+06
157821790 | IX_ClimateModelId001 | 2425 | 1.10636e+06
157821789 | IX_AusPosNumber001 | 2425 | 1.10636e+06
157821792 | IX_ClimateModelId002 | 2425 | 1.10636e+06
157821791 | IX_AusPosNumber002 | 2425 | 1.10636e+06
157821794 | IX_ClimateModelId003 | 2425 | 1.10636e+06
157821793 | IX_AusPosNumber003 | 2425 | 1.10636e+06
157821796 | IX_ClimateModelId004 | 2425 | 1.10636e+06
157821795 | IX_AusPosNumber004 | 2425 | 1.10636e+06
157821806 | PK_ClimateChange | 2392 | 543984
157821785 | IX_Longitude | 1194 | 543984
157821784 | IX_Latitude | 1194 | 543984
157821783 | IX_iMonth | 1194 | 543984
157821782 | IX_ClimateModelId | 1194 | 543984
157821781 | IX_ClimateCId | 1194 | 543984
17026 | InputDataPoints | 354 | 40000
16640 | pg_proc_proname_args_nsp_index | 125 | 1492
157821846 | PK_InputDataPoints | 112 | 40000
157821798 | IX_ClimateId001 | 90 | 40000
157821797 | IX_AusPosNumber005 | 90 | 40000
1255 | pg_proc | 58 | 1492
16608 | pg_attribute_relid_attnam_index | 47 | 1732
1249 | pg_attribute | 31 | 1732
16598 | pg_depend | 26 | 3485
16623 | pg_depend_reference_index | 21 | 3485
17040 | SprengeliaList | 18 | 3217
16622 | pg_depend_depender_index | 17 | 3485
16613 | pg_class_relname_nsp_index | 14 | 232
1259 | pg_class | 13 | 232
16392 | pg_operator | 13 | 643
16416 | pg_description | 12 | 1390
16609 | pg_attribute_relid_attnum_index | 12 | 1732
16638 | pg_operator_oprname_l_r_n_index | 11 | 643
16639 | pg_proc_oid_index | 11 | 1492
16672 | pg_toast_16410 | 10 | 44
157821860 | PK_SprengeliaList | 10 | 3217
17036 | ScenarioEmissionLevels | 9 | 1311
16624 | pg_description_o_c_o_index | 8 | 1390
16651 | pg_type_typname_nsp_index | 7 | 226
16390 | pg_index | 7 | 115
157821856 | PK_ScenarioEmissionLevels | 7 | 1311
16410 | pg_rewrite | 6 | 46
1247 | pg_type | 5 | 226
16408 | pg_statistic | 5 | 143
16612 | pg_class_oid_index | 4 | 232
16618 | pg_conversion_name_nsp_index | 4 | 114
16637 | pg_operator_oid_index | 4 | 643
16645 | pg_statistic_relid_att_index | 4 | 143
16600 | pg_aggregate_fnoid_index | 2 | 60
16601 | pg_am_name_index | 2 | 4
16602 | pg_am_oid_index | 2 | 4
16603 | pg_amop_opc_opr_index | 2 | 180
16604 | pg_amop_opc_strategy_index | 2 | 180
16605 | pg_amproc_opc_procnum_index | 2 | 57
16610 | pg_cast_oid_index | 2 | 174
16611 | pg_cast_source_target_index | 2 | 174
16614 | pg_constraint_conname_nsp_index | 2 | 32
16615 | pg_constraint_conrelid_index | 2 | 32
16616 | pg_constraint_oid_index | 2 | 32
16617 | pg_conversion_default_index | 2 | 114
16619 | pg_conversion_oid_index | 2 | 114
16620 | pg_database_datname_index | 2 | 4
16621 | pg_database_oid_index | 2 | 4
16625 | pg_group_name_index | 2 | 1
16626 | pg_group_sysid_index | 2 | 1
16627 | pg_index_indrelid_index | 2 | 115
16628 | pg_index_indexrelid_index | 2 | 115
16630 | pg_language_name_index | 2 | 3
16631 | pg_language_oid_index | 2 | 3
16633 | pg_namespace_nspname_index | 2 | 4
16634 | pg_namespace_oid_index | 2 | 4
16635 | pg_opclass_am_name_nsp_index | 2 | 51
16636 | pg_opclass_oid_index | 2 | 51
16641 | pg_rewrite_oid_index | 2 | 46
16642 | pg_rewrite_rel_rulename_index | 2 | 46
16643 | pg_shadow_usename_index | 2 | 3
16644 | pg_shadow_usesysid_index | 2 | 3
16646 | pg_trigger_tgconstrname_index | 2 | 5
16647 | pg_trigger_tgconstrrelid_index | 2 | 5
16648 | pg_trigger_tgrelid_tgname_index | 2 | 5
16649 | pg_trigger_oid_index | 2 | 5
16650 | pg_type_oid_index | 2 | 226
16674 | pg_toast_16410_index | 2 | 44
16418 | pg_cast | 2 | 174
16596 | pg_conversion | 2 | 114
17044 | Templates | 2 | 238
157821810 | PK_ClimateIndicators | 2 | 35
157821812 | PK_ClimateModels | 2 | 10
157821814 | PK_ClimateVariables | 2 | 7
157821840 | PK_GetCurrentClimateParameters | 2 | 1
157821842 | PK_GetFutureClimateParameters | 2 | 1
157821844 | PK_GetPointsList | 2 | 1
157821848 | PK_Levels | 2 | 3
157821850 | PK_Months | 2 | 12
157821852 | PK_NeighbourDescription | 2 | 9
157821858 | PK_ScenarioNames | 2 | 18
157821862 | PK_Years | 2 | 23
1261 | pg_group | 1 | 1
16606 | pg_attrdef_adrelid_adnum_index | 1 | 0
16607 | pg_attrdef_oid_index | 1 | 0
16629 | pg_inherits_relid_seqno_index | 1 | 0
16632 | pg_largeobject_loid_pn_index | 1 | 0
16656 | pg_toast_16384_index | 1 | 0
16659 | pg_toast_16386_index | 1 | 0
16662 | pg_toast_1262_index | 1 | 0
16394 | pg_opclass | 1 | 51
16396 | pg_am | 1 | 4
16665 | pg_toast_16416_index | 1 | 0
16668 | pg_toast_1261_index | 1 | 0
16398 | pg_amop | 1 | 180
16400 | pg_amproc | 1 | 57
16402 | pg_language | 1 | 3
16671 | pg_toast_1255_index | 1 | 0
16406 | pg_aggregate | 1 | 60
16412 | pg_trigger | 1 | 5
16594 | pg_namespace | 1 | 4
16677 | pg_toast_1260_index | 1 | 0
1260 | pg_shadow | 1 | 3
16680 | pg_toast_16408_index | 1 | 0
16386 | pg_constraint | 1 | 32
1262 | pg_database | 1 | 4
17042 | TemplateDescriptions | 1 | 10
16994 | CreatedClimateModels | 1 | 9
16985 | ClimateIndicators | 1 | 35
157835892 | pg_toast_157835888_index | 1 | 0
16987 | ClimateModels | 1 | 10
16992 | ClimateVariables | 1 | 7
157821818 | PK_CurrentClimateXtab2 | 1 | 0
17020 | GetCurrentClimateParameters | 1 | 1
17022 | GetFutureClimateParameters | 1 | 1
17024 | GetPointsList | 1 | 1
17028 | Levels | 1 | 3
17030 | Months | 1 | 12
17032 | NeighbourDescription | 1 | 9
17038 | ScenarioNames | 1 | 18
17046 | Years | 1 | 23
16681 | pg_user | 0 | 0
16684 | pg_rules | 0 | 0
16688 | pg_views | 0 | 0
376 | pg_xactlock | 0 | 0
16691 | pg_tables | 0 | 0
16694 | pg_indexes | 0 | 0
16698 | pg_stats | 0 | 0
16702 | pg_stat_all_tables | 0 | 0
16706 | pg_stat_sys_tables | 0 | 0
16709 | pg_stat_user_tables | 0 | 0
16712 | pg_statio_all_tables | 0 | 0
16716 | pg_statio_sys_tables | 0 | 0
16719 | pg_statio_user_tables | 0 | 0
16722 | pg_stat_all_indexes | 0 | 0
16726 | pg_stat_sys_indexes | 0 | 0
16729 | pg_stat_user_indexes | 0 | 0
16732 | pg_statio_all_indexes | 0 | 0
16736 | pg_statio_sys_indexes | 0 | 0
16739 | pg_statio_user_indexes | 0 | 0
16742 | pg_statio_all_sequences | 0 | 0
16745 | pg_statio_sys_sequences | 0 | 0
16748 | pg_statio_user_sequences | 0 | 0
16751 | pg_stat_activity | 0 | 0
16754 | pg_stat_database | 0 | 0
16757 | pg_locks | 0 | 0
16654 | pg_toast_16384 | 0 | 0
16760 | pg_settings | 0 | 0
16657 | pg_toast_16386 | 0 | 0
16388 | pg_inherits | 0 | 0
16660 | pg_toast_1262 | 0 | 0
16663 | pg_toast_16416 | 0 | 0
16666 | pg_toast_1261 | 0 | 0
16669 | pg_toast_1255 | 0 | 0
16404 | pg_largeobject | 0 | 0
16414 | pg_listener | 0 | 0
16675 | pg_toast_1260 | 0 | 0
16678 | pg_toast_16408 | 0 | 0
16384 | pg_attrdef | 0 | 0
157821864 | aus6mindem | 0 | 0
157821917 | currentmonthr | 0 | 0
157821881 | fixausclimatechange | 0 | 0
157821921 | futuremonthr | 0 | 0
157821907 | getcurrent | 0 | 0
157821870 | getcurrentclimate | 0 | 0
157821904 | getcurrentclimateforcline | 0 | 0
157821896 | getcurrentclimaterestricted | 0 | 0
157821910 | getfuture | 0 | 0
157821884 | getfutureausclimate | 0 | 0
157821900 | getfutureclimateforcline | 0 | 0
157821892 | getfutureclimaterestricted | 0 | 0
157821913 | getfuturemonth | 0 | 0
157821867 | listpoints | 0 | 0
157821878 | noausposnum | 0 | 0
157821875 | selectuniqueclimatevalues | 0 | 0
157821888 | selectuniquefuturevalues | 0 | 0
157821929 | selectuniqueheights | 0 | 0
157821925 | sprengeliadata | 0 | 0
157835890 | pg_toast_157835888 | 0 | 0
16998 | CurrentClimateXtab | 0 | 0
(235 rows)
\d "getfutureausclimate"
View "public.getfutureausclimate"
Column | Type | Modifiers
--------------+------------------+-----------
ClimateId | smallint |
AusPosNumber | integer |
iMonth | integer |
Longitude | integer |
Latitude | integer |
Height | real |
ClimateValue | smallint |
ScenarioId | integer |
iYear | smallint |
LevelId | integer |
futurevalue | double precision |
CREATE OR REPLACE VIEW "public"."getfutureausclimate" (
"ClimateId",
"AusPosNumber",
"iMonth",
"Longitude",
"Latitude",
"Height",
"ClimateValue",
"ScenarioId",
"iYear",
"LevelId",
futurevalue)
AS
SELECT "ClimateVariables"."ClimateId", "Aus40_DEM"."AusPosNumber",
"CurrentAusClimate"."iMonth", "Aus40_DEM"."Longitude",
"Aus40_DEM"."Latitude", "Aus40_DEM"."Height",
"CurrentAusClimate"."ClimateValue",
"GetFutureClimateParameters"."ScenarioId",
"GetFutureClimateParameters"."iYear",
"GetFutureClimateParameters"."LevelId",
((("CurrentAusClimate"."ClimateValue")::double precision /
"ClimateVariables"."ClimateDivisor") +
("ScenarioEmissionLevels"."TempChange" *
"ClimateChangeModel40"."ChangePerDegree")) AS futurevalue
FROM ((("CurrentAusClimate" JOIN "ClimateChangeModel40" ON
(((("CurrentAusClimate"."ClimateId" = "ClimateChangeModel40"."ClimateId")
AND ("CurrentAusClimate"."AusPosNum" =
"ClimateChangeModel40"."AusPosNumber")) AND ("CurrentAusClimate"."iMonth"
=
"ClimateChangeModel40"."iMonth")))) JOIN ("ClimateVariables" JOIN
("ScenarioEmissionLevels" JOIN "GetFutureClimateParameters" ON
(((("ScenarioEmissionLevels"."iYear" =
"GetFutureClimateParameters"."iYear") AND
("ScenarioEmissionLevels"."LevelId" =
"GetFutureClimateParameters"."LevelId")) AND
("ScenarioEmissionLevels"."ScenarioId" =
"GetFutureClimateParameters"."ScenarioId")))) ON
(("ClimateVariables"."ClimateId" =
"GetFutureClimateParameters"."ClimateId"))) ON
(("CurrentAusClimate"."ClimateId" = "ClimateVariables"."ClimateId"))) JOIN
"Aus40_DEM" ON (("ClimateChangeModel40"."AusPosNumber" =
"Aus40_DEM"."AusPosNumber")))
ORDER BY "Aus40_DEM"."AusPosNumber", "CurrentAusClimate"."iMonth";
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
