Re: Activate Index - Mailing list pgsql-general

You want this document:

http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html

great article on performance tuning postgresql.  After reading it and
digesting all you can, you should have a noticeably faster machine.

On Thu, 4 Sep 2003, Edwin Quijada wrote:

> 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/
>
>