Skip site navigation (1) Skip section navigation (2)

Re: two queries and dual cpu (perplexed)

From: "Shoaib Burq (VPAC)" <sab(at)vpac(dot)org>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: John A Meinel <john(at)arbash-meinel(dot)com>,Russell Smith <mr-russ(at)pws(dot)com(dot)au>, Jeff <threshar(at)torgo(dot)978(dot)org>,<pgsql-performance(at)postgresql(dot)org>
Subject: Re: two queries and dual cpu (perplexed)
Date: 2005-04-26 14:31:18
Message-ID: Pine.LNX.4.44.0504270009510.22330-300000@hp.vpac.org (view raw or flat)
Thread:
Lists: pgsql-performance
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(at)arbash-meinel(dot)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.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 majordomo(at)postgresql(dot)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



Attachment: getfutureausclimate_query.txt
Description: text/plain (2.4 KB)
Attachment: table_sizes.txt
Description: text/plain (16.4 KB)

In response to

Responses

pgsql-performance by date

Next:From: Dave HeldDate: 2005-04-26 14:58:00
Subject: Re: two queries and dual cpu (perplexed)
Previous:From: apoc9009@yahoo.deDate: 2005-04-26 09:57:33
Subject: Re: Table Partitioning: Will it be supported in Future?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group