X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 6CEE39DC9E3 for ; Fri, 10 Feb 2006 17:26:09 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 13822-05 for ; Fri, 10 Feb 2006 17:26:10 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.205]) by postgresql.org (Postfix) with ESMTP id 263F79DC800 for ; Fri, 10 Feb 2006 17:26:06 -0400 (AST) Received: by wproxy.gmail.com with SMTP id i34so435931wra for ; Fri, 10 Feb 2006 13:26:07 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references; b=caZ0T7NK1rwOjOWopp5fr0XoJtMIHF0rHV0LK3P8GqYUjdS2uTDIJHjXsw1UE60tpgW8W5mVJEcguQ7YEFAoHWK1UVNJKAR/RrtJQ2s9LsoD6T/jswDl5JPRv9euI9gVG3Qe8X8AXoJrXTzn74ZvW3SGB9HhYts1zvblmVaUk8k= Received: by 10.64.232.20 with SMTP id e20mr743520qbh; Fri, 10 Feb 2006 13:26:07 -0800 (PST) Received: by 10.65.188.20 with HTTP; Fri, 10 Feb 2006 13:26:07 -0800 (PST) Message-ID: <9c89e2490602101326m60ce0930y9446cef99c45855e@mail.gmail.com> Date: Fri, 10 Feb 2006 13:26:07 -0800 From: david drummard To: pgsql-admin@postgresql.org Subject: Re: performance question related to pgsql In-Reply-To: <20060210204653.GH4474@ns.snowman.net> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_1771_29929031.1139606767666" References: <9c89e2490602101241i6a182491l53478521f15f7f8d@mail.gmail.com> <20060210204653.GH4474@ns.snowman.net> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.442 required=5 tests=[AWL=0.441, HTML_MESSAGE=0.001] X-Spam-Score: 0.442 X-Spam-Level: X-Archive-Number: 200602/116 X-Sequence-Number: 20537 ------=_Part_1771_29929031.1139606767666 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline hi stephen, thanks very much for the response. Are there any special commands to analyz= e the index before using the table. If i rename the table, will the indexes still stay with the table ( i hope so). best regards vijay eranti On 2/10/06, Stephen Frost wrote: > > * david drummard (vijayspam@gmail.com) wrote: > > My question is what is the best way to do step (1) so that after the > copy is > > done, the table is fully indexed and properly balanced and optimized > for > > query. > > Should i create indexes before or after import ? I need to do this in > > shortest period of time so that the data is always uptodate. Note that > > incremental updates are not possible since almost every row will be > changed > > in the new file. > > Create indexes after import, definitely. analyze after indexes have > been created. You don't need to vacuum if you're creating a whole new > table each time. Remember that if you need to adjust analyze parameters > for some reason that you'll need to do that on every table creation as > well. Using COPY is a good approach. Not doing incremental updates > when most of the rows are changing also makes sense. > > Thanks, > > Stephen > > > -----BEGIN PGP SIGNATURE----- > Version: GnuPG v1.4.2 (GNU/Linux) > > iD8DBQFD7Pu9rzgMPqB3kigRArthAJ4vgBzbUL/0YbeWu/luVFIGaP43rwCgkkf2 > 2Y7ADi6UEbhpl8CyDNC2jA8=3D > =3DWhhZ > -----END PGP SIGNATURE----- > > > ------=_Part_1771_29929031.1139606767666 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline hi stephen,

thanks very much for the response. Are there any special commands to analyze the index before using the table. If i rename the table, will the indexes still stay with the table  ( i hope so).

best regards
vijay eranti

On 2/10/06, Stephen Frost <sfrost@snowman.net> wrote:
* david drummard (vijayspam@gmail.co= m) wrote:
> My question is what is the best way to do step (1) so= that after the copy is
> done, the table is fully indexed  = ;and properly balanced and optimized for
> query.
> Should i create indexes before or after import ? I = need to do this in
> shortest period of time so that the data is alwa= ys uptodate. Note that
> incremental updates are not possible since a= lmost every row will be changed
> in the new file.

Create indexes after import, definitely.&n= bsp; analyze after indexes have
been created.  You don't = need to vacuum if you're creating a whole new
table each time. &nbs= p;Remember that if you need to adjust analyze parameters
for some reason that you'll need to do that on every table creation as<= br>well.  Using COPY is a good approach.  Not doing inc= remental updates
when most of the rows are changing also makes sense.
        Thanks,

           &= nbsp;    Stephen


-----BEGIN PGP SIGNATURE---= --
Version: GnuPG v1.4.2 (GNU/Linux)

iD8DBQFD7Pu9rzgMPqB3kigRArth= AJ4vgBzbUL/0YbeWu/luVFIGaP43rwCgkkf2
2Y7ADi6UEbhpl8CyDNC2jA8=3D
=3DWh= hZ
-----END PGP SIGNATURE-----



------=_Part_1771_29929031.1139606767666--