Activate Index

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:

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: