Re: Activate Index - Mailing list pgsql-general

ok!
U can tell me about the best setup and performance?
and best parameters to setup I am so newbie  ?
I am using Postgres to emigrate a system in 4D Server to PgSql With Delphi
for insurance company.
My server is Pentium IV 2.0 Ghz , 1 GB Ram, 40 GB HD 7200RPM?
Now I wanna know what is the best setup



>From: "scott.marlowe" <scott.marlowe@ihs.com>
>To: Edwin Quijada <listas_quijada@hotmail.com>
>CC: <mattias@kregert.se>, <pgsql-general@postgresql.org>
>Subject: Re: [GENERAL] Activate Index
>Date: Thu, 4 Sep 2003 08:58:57 -0600 (MDT)
>
>What query are you running?
>
>If you run
>
>select * from indexed_table
>
>you'll get a seq scan because you're hitting every row.
>
>If you're doing
>
>select * from indexed_table where indexed_field=1
>
>you should get an index scan.
>
>Anything in between the query planner will decide.  Unlike some older
>databases that used a rules based planner, postgresql uses a cost based
>planner.  I.e. it figures out which "costs" more, a seq scan or an index
>scan, and chooses accordingly.
>
>In order to do this properly, it needs to know how your data is laid out.
>Have you run analyze on your database?
>
>If the query planner still makes the wrong you can force things with "set
>enable_seqscan = off" temporarily to see if postgresql really is making
>the best choice.  Then run your query with "explain analyze " at the front
>and postgresql will run the query, report back how it ran it, how much it
>THOUGHT it would cost, and how long it really took.
>
>Then, you can tune your database by messing about with random_page_cost,
>cpu*cost (3 different vars) effective_cache_size, shared_buffers, and
>sort_mem.
>
>Postgresql is like a large complex piece of machinery that runs great once
>it's been setup, but suffers from pretty mediocre performance in it's
>default installation format due to the need to start on some pretty
>ancient operating systems / hardware setups.  Lately, some of those
>defaults have been increased a bit, but they're still small for a
>workgroup or enterprise class database.
>
>
>On Thu, 4 Sep 2003, Edwin Quijada wrote:
>
> >
> > Well , My table has 380000 records!!!
> >
> >
> > >From: "Mattias Kregert" <mattias@kregert.se>
> > >To: "Edwin Quijada" <listas_quijada@hotmail.com>
> > >Subject: Re: [GENERAL] Activate Index
> > >Date: Thu, 4 Sep 2003 15:58:33 +0200
> > >
> > >Hello Edwin,
> > >
> > >The index will be used automatically when you have enough rows in the
> > >table. For tables with a small number of rows, a seq scan will be made
> > >because it is actually faster than using the index with a small number
>of
> > >rows.
> > >
> > >You also have to remember to run VACUUM from time to time, so that the
> > >statistics are updated so the planner can see how many rows are in the
> > >table.
> > >
> > >
> > >/Mattias
> > >
> > >----- Original Message -----
> > >From: "Edwin Quijada" <listas_quijada@hotmail.com>
> > >To: <pgsql-general@postgresql.org>
> > >Sent: Thursday, September 04, 2003 4:02 PM
> > >Subject: [GENERAL] Activate Index
> > >
> > >
> > > > Hi!!
> > > > This is my first post to the list. I have a problem.
> > > > I have a table with a two index , int4, but when I do a search using
>the
> > > > field indexed but when I applied an explain to the query it says Seq
> > >Scan ,
> > > > I mean , that it doesnot use the index.
> > > > I cant use the cast field::int4 because I use delphi and delphi does
>not
> > > > support this.
> > > > I know there is a command to activate index aand always postgrres
>will
> > >be
> > > > the index
> > > > Which are this command???
> > > > Anyone knows?
> > > >
> > > >
> > > > *-------------------------------------------------------*
> > > > *-Edwin Quijada
> > > > *-Developer DataBase
> > > > *-JQ Microsistemas
> > > > *-809-747-2787
> > > > * " Si deseas lograr cosas excepcionales debes de hacer cosas fuera
>de
> > >lo
> > > > comun"
> > > > *-------------------------------------------------------*
> > > >
> > > > _________________________________________________________________
> > > > ¿Estás buscando un auto nuevo?  http://www.yupimsn.com/autos/
> > > >
> > > >
> > > > ---------------------------(end of
>broadcast)---------------------------
> > > > TIP 5: Have you checked our extensive FAQ?
> > > >
> > > >                http://www.postgresql.org/docs/faqs/FAQ.html
> > > >
> >
> > _________________________________________________________________
> > ¿Estás buscando un auto nuevo?  http://www.yupimsn.com/autos/
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 4: Don't 'kill -9' the postmaster
> >
>

_________________________________________________________________
Charla con tus amigos en línea mediante MSN Messenger:
http://messenger.yupimsn.com/