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 <john@arbash-meinel.com> 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.703rows=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 majordomo@postgresql.org)
>
--
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
