Thread: Activate Index

Activate Index

From
"Edwin Quijada"
Date:
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/


Re: Activate Index

From
"Edwin Quijada"
Date:
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/


Re: Activate Index

From
"scott.marlowe"
Date:
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
>


Re: Activate Index

From
"Edwin Quijada"
Date:
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/


Re: Activate Index

From
Ron Johnson
Date:
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


Re: Activate Index

From
"scott.marlowe"
Date:
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/
>
>