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 6FF2C53616 for ; Thu, 14 Apr 2005 13:04:09 -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 22657-06 for ; Thu, 14 Apr 2005 16:04:03 +0000 (GMT) Received: from zproxy.gmail.com (zproxy.gmail.com [64.233.162.203]) by svr1.postgresql.org (Postfix) with ESMTP id 85F8853557 for ; Thu, 14 Apr 2005 13:04:02 -0300 (ADT) Received: by zproxy.gmail.com with SMTP id 34so529993nzf for ; Thu, 14 Apr 2005 09:04:02 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=nNk444moOlvVrzVjLVMnoKo0zfcCrJbvoe/xUkWdUSunBD0B97EjIaM1IdjemhyynrIkmTi/JeoZEaa74KybzOt3tVDA5YnsbMWfAP2tGe9syqWtkfuYfOpAu79E+bcyu99DcrNEtX6P0hzy9s2756tuOyIV9nD4nyWevM6aruw= Received: by 10.36.8.17 with SMTP id 17mr123494nzh; Thu, 14 Apr 2005 09:04:02 -0700 (PDT) Received: by 10.36.22.15 with HTTP; Thu, 14 Apr 2005 09:04:02 -0700 (PDT) Message-ID: <758d5e7f05041409044eadfcd1@mail.gmail.com> Date: Thu, 14 Apr 2005 18:04:02 +0200 From: Dawid Kuroczko Reply-To: Dawid Kuroczko To: Joel Fradkin Subject: Re: speed of querry? Cc: PERFORM In-Reply-To: <000b01c54100$a68d2300$797ba8c0@jfradkin> Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline References: <758d5e7f0504140720754c7750@mail.gmail.com> <000b01c54100$a68d2300$797ba8c0@jfradkin> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.755 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE, FROM_ENDS_IN_NUMS, RCVD_BY_IP X-Spam-Level: X-Archive-Number: 200504/294 X-Sequence-Number: 11691 On 4/14/05, Joel Fradkin wrote: > Josh from commandprompt.com had me alter the config to have > default_statistics_target =3D 250 >=20 > Is this somehow related to what your asking me to do? > I did do an analyze, but have only ran the viw a few times. well, he did suggest the right thing. However this parameter applies to newly created tables, so either recreate the tables or do the ALTER TABLE I've sent eariler. Basically it tells postgres how many values should it keep for statistics per column. The config default_statistics_target is the default (=3D used when creating table) and ALTER... is a way to change it later. The more statistics PostgreSQL has means it can better predict how much data will be returned -- and this directly leads to a choice how to handle the data (order in which tables should be read, whether to use index or not, which algorithm use for join, etc.). The more statistics, the better PostgreSQL is able to predict. The more statistics, the slower planner is able to do the analysis. So you have to find a value which will be as much as is needed to accurately predict the results but not more! PostgreSQL's default of 10 is a bit conservative, hence the suggestions to increase it. :) [ and so is random_page_cost or some people have found that in their cases it is beneficial to reduce the value, even as much as below 2. ] Hope this clairifies things a bit. Regards, Dawid