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