Re: Activate Index

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(at)kregert(dot)se>
> >To: "Edwin Quijada" <listas_quijada(at)hotmail(dot)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(at)hotmail(dot)com>
> >To: <pgsql-general(at)postgresql(dot)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
>