pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: John A Meinel <john@arbash-meinel.com>
To: Shoaib Burq (VPAC) <sab@vpac.org>
To: pgsql-performance@postgresql.org
Subject: Re: two queries and dual cpu (perplexed)
Date: Tue, 26 Apr 2005 10:05:26 -0500
Message-ID: <426E58B6.2010607@arbash-meinel.com> (raw)
In-Reply-To: <Pine.LNX.4.44.0504270009510.22330-300000@hp.vpac.org>
References: <Pine.LNX.4.44.0504270009510.22330-300000@hp.vpac.org>

Shoaib Burq (VPAC) wrote:
> 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

Actually, you only need to alter the statistics for that particular
column, not for all columns in the db.

What you want to do is:

ALTER TABLE "ClimateChangeModel40"
	ALTER COLUMN <whatever the column is>
	SET STATISTICS 100;
VACUUM ANALYZE "ClimateChangeModel40";

The column is just the column that you have the "IX_ClimateId" index on,
I don't know which one that is.

The statistics value ranges from 1 - 1000, the default being 10, and for
indexed columns you are likely to want somewhere between 100-200.

If you set it to 100 and the planner is still mis-estimating the number
of rows, try 200, etc.

The reason to keep the number low is because with a high number the
planner has to spend more time planning. But especially for queries like
this one, you'd rather the query planner spent a little bit more time
planning, and got the right plan.

John
=:->

Attachments:

  signature.asc (application/pgp-signature, 250B) download

view thread (18+ messages)

Message-ID: <426E58B6.2010607@arbash-meinel.com>
Permalink:  ../426E58B6.2010607@arbash-meinel.com/
Also on:    postgresql.org/message-id/426E58B6.2010607@arbash-meinel.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: john@arbash-meinel.com, sab@vpac.org
  Subject: Re: two queries and dual cpu (perplexed)
  In-Reply-To: <426E58B6.2010607@arbash-meinel.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox