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 158A05294F for ; Thu, 23 Jun 2005 10:31:15 -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 42323-06 for ; Thu, 23 Jun 2005 13:31:10 +0000 (GMT) Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.207]) by svr1.postgresql.org (Postfix) with ESMTP id CB73A528D9 for ; Thu, 23 Jun 2005 10:31:08 -0300 (ADT) Received: by wproxy.gmail.com with SMTP id 69so62784wri for ; Thu, 23 Jun 2005 06:31:13 -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:references; b=HJg7DcGPOwXGz7DbUYfc3DcUb6cDSg7YBdRo0kOi3iQcNABRhA+/ZGQWAImjf6+nHl2C57Mh26s9wDv3VqWsKav9kD6MjEIluMQNMvbVWVVXxkM8gS3umyT0Q+uEx2azi+r4jFhGgDWyjo3fSPTNEkoZqndK/gcvM6KXLYxbx8g= Received: by 10.54.42.17 with SMTP id p17mr1120671wrp; Thu, 23 Jun 2005 06:31:13 -0700 (PDT) Received: by 10.54.104.3 with HTTP; Thu, 23 Jun 2005 06:31:13 -0700 (PDT) Message-ID: Date: Thu, 23 Jun 2005 15:31:13 +0200 From: Kjell Tore Fossbakk Reply-To: Kjell Tore Fossbakk To: Tobias Brox Subject: Re: Querying 19million records very slowly Cc: pgsql-performance@postgresql.org In-Reply-To: <20050622162844.GE7839@tobias> Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_10735_31559800.1119533473130" References: <42B976D0.3000909@PresiNET.com> <20050622162844.GE7839@tobias> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.368 tagged_above=0 required=5 tests=AWL, HTML_20_30, HTML_MESSAGE, RCVD_BY_IP X-Spam-Level: X-Archive-Number: 200506/450 X-Sequence-Number: 13087 ------=_Part_10735_31559800.1119533473130 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline -I also changed now() to current_time, which increased performance quite=20 good. I need to make further tests, before I'll post any results. -I tried to change now()- interval 'x hours' to like 2005-06-22 16:00:00+02= .=20 This also increased the performance. changing to time > '2005-06-22 16:00:00+02' (or what ever date is 24 hours= =20 back) or time > current_time - interval '24 hours' will be used. I'm running FreeBSD 4.11, and im editing the file in=20 /usr/local/etc/postgresql.conf, but it doesnt help. When i start up "psql= =20 database", none of the options are changed (with a restart of the=20 postmaster). I cant find a '--configuration=3Dpath/file' option for the=20 postmaster either... Kjell Tore On 6/22/05, Tobias Brox wrote:=20 >=20 > [Kjell Tore Fossbakk - Wed at 07:41:54AM -0700] > > I cant get the config file to load into my postgres. that's the > > problem. I want to set it to 10k, but it is only still at 1000... I > > save the file and restart the service.. > > > > yes, i ment 'pg_ctl reload', sry about that one. >=20 > Classical problem, a bit depending on the distro you are using. >=20 > The "master" file usually resides in /etc/postgresql while the actual fil= e > used usually resides in /var/lib/postgres/data ... or something. Some > distros copies over the file (such that one always should edit the file i= n > /etc) others don't (thus you either have to do that your self, or edit th= e > file in the database data directory. >=20 > -- > Tobias Brox, +86-13521622905 > Nordicbet, IT dept > ------=_Part_10735_31559800.1119533473130 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline
-I also changed now() to current_time, which increased performance qui= te good. I need to make further tests, before I'll post any results.
-I tried to change now()- interval 'x hours' to like 2005-06-22 16:00:= 00+02. This also increased the performance.
 
changing to time > '2005-06-22 16:00:00+02' (or what ever date is 2= 4 hours back) or time > current_time - interval '24 hours' will be used.=
 
I'm running FreeBSD 4.11, and im editing the file in /usr/local/etc/po= stgresql.conf, but it doesnt help. When i start up "psql database"= ;, none of the options are changed (with a restart of the postmaster). I ca= nt find a '--configuration=3Dpath/file' option for the postmaster either...
 
Kjell Tore
 
On 6/22/05, = Tobias Brox <tobias@nordicbe= t.com> wrote:
[Kjell Tore Fossbakk - Wed at 07= :41:54AM -0700]
> I cant get the config file to load into my postgres= . that's the
> problem. I want to set it to 10k, but it is only still at 1000... = I
> save the file and restart the service..
>
> yes, i me= nt 'pg_ctl reload', sry about that one.

Classical problem, a bit dep= ending on the distro you are using.

The "master" file usually resides in /etc/postgresql whil= e the actual file
used usually resides in /var/lib/postgres/data ... or = something.  Some
distros copies over the file (such that one a= lways should edit the file in
/etc) others don't (thus you either have to do that your self, or edit = the
file in the database data directory.

--
Tobias Brox, +86-1= 3521622905
Nordicbet, IT dept

------=_Part_10735_31559800.1119533473130--