X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 7A6B153AA2 for ; Tue, 26 Apr 2005 12:05:57 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 20765-08 for ; Tue, 26 Apr 2005 15:05:54 +0000 (GMT) Received: from ct.radiology.uiowa.edu (ct.radiology.uiowa.edu [129.255.60.186]) by svr1.postgresql.org (Postfix) with ESMTP id 548C853A12 for ; Tue, 26 Apr 2005 12:05:53 -0300 (ADT) Received: from [192.168.1.11] (12-215-118-172.client.mchsi.com [12.215.118.172]) by ct.radiology.uiowa.edu (8.11.6/8.11.6) with ESMTP id j3QF5X311744; Tue, 26 Apr 2005 10:05:33 -0500 Message-ID: <426E58B6.2010607@arbash-meinel.com> Date: Tue, 26 Apr 2005 10:05:26 -0500 From: John A Meinel User-Agent: Mozilla Thunderbird 1.0.2-1.3.2 (X11/20050324) X-Accept-Language: en-us, en MIME-Version: 1.0 To: "Shoaib Burq (VPAC)" , pgsql-performance@postgresql.org Subject: Re: two queries and dual cpu (perplexed) References: In-Reply-To: X-Enigmail-Version: 0.89.6.0 X-Enigmail-Supports: pgp-inline, pgp-mime Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="------------enig822B4FC0BF096712A2D3800C" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200504/698 X-Sequence-Number: 12095 This is an OpenPGP/MIME signed message (RFC 2440 and 3156) --------------enig822B4FC0BF096712A2D3800C Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit 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 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 =:-> --------------enig822B4FC0BF096712A2D3800C Content-Type: application/pgp-signature; name="signature.asc" Content-Description: OpenPGP digital signature Content-Disposition: attachment; filename="signature.asc" -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.1 (GNU/Linux) Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org iD8DBQFCbli2JdeBCYSNAAMRAiXeAKCVM0Ztv+uS4M3QVpvOlAmLNDUG1gCgqXO4 NDf1CoRgLcG6zRcfGonU844= =q+fT -----END PGP SIGNATURE----- --------------enig822B4FC0BF096712A2D3800C--