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" <[EMAIL PROTECTED]> > >To: "Edwin Quijada" <[EMAIL PROTECTED]> > >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" <[EMAIL PROTECTED]> > >To: <[EMAIL PROTECTED]> > >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 > ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
