Re: two queries and dual cpu (perplexed)

From: Shoaib Burq (VPAC)
Subject: Re: two queries and dual cpu (perplexed)
Date: ,
Msg-id: Pine.LNX.4.44.0504270009510.22330-300000@hp.vpac.org
(view: Whole thread, Raw)
In response to: Re: two queries and dual cpu (perplexed)  (Tom Lane)
Responses: Re: two queries and dual cpu (perplexed)  (John A Meinel)
List: pgsql-performance

Tree view

two queries and dual cpu (perplexed)  ("Shoaib Burq (VPAC)", )
 Re: two queries and dual cpu (perplexed)  (Jeff, )
  Re: two queries and dual cpu (perplexed)  (Gavin Sherry, )
  Re: two queries and dual cpu (perplexed)  ("Shoaib Burq (VPAC)", )
   Re: two queries and dual cpu (perplexed)  (Russell Smith, )
    Re: two queries and dual cpu (perplexed)  ("Shoaib Burq (VPAC)", )
     Re: two queries and dual cpu (perplexed)  (John A Meinel, )
      Re: two queries and dual cpu (perplexed)  ("Shoaib Burq (VPAC)", )
       Re: two queries and dual cpu (perplexed)  (Gavin Sherry, )
       Re: two queries and dual cpu (perplexed)  (Jeff, )
      Re: two queries and dual cpu (perplexed)  (Tom Lane, )
       Re: two queries and dual cpu (perplexed)  ("Shoaib Burq (VPAC)", )
        Re: two queries and dual cpu (perplexed)  (John A Meinel, )
   Re: two queries and dual cpu (perplexed)  ("Shoaib Burq (VPAC)", )
  Re: two queries and dual cpu (perplexed)  ("Shoaib Burq (VPAC)", )
  Re: two queries and dual cpu (perplexed)  (Kenneth Marshall, )
 Re: two queries and dual cpu (perplexed)  (Daniel Schuchardt, )
 Re: two queries and dual cpu (perplexed)  ("Dave Held", )

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

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

pgsql-performance by date:

From: Matthew Nuzum
Date:
Subject: speed up query with max() and odd estimates
From: Simon Riggs
Date:
Subject: Re: [HACKERS] Bad n_distinct estimation; hacks suggested?