Re: Activate Index - Mailing list pgsql-general

On Thu, 2003-09-04 at 14:08, 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

http://www.varlena.com/GeneralBits/
http://www.varlena.com/varlena/GeneralBits/archive.php
http://www.varlena.com/varlena/GeneralBits/Tidbits/index.php

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


--
-----------------------------------------------------------------
Ron Johnson, Jr. ron.l.johnson@cox.net
Jefferson, LA USA

"Fair is where you take your cows to be judged."
Unknown