slow update & index question

On Mon, 21 Oct 2002, Williams, Travis L, NPONS wrote:

I have a script that is updating the DB by reading in a file with 467
lines. Each file is named by the shelf the information was gotten from.
Each line is tab seperated and consists of a column name and the value
that it should be update to. The update line in the perl script looks
like this: update table set "column name" = 'value' where "Shelf_Name" =
'$shelf'

Now this works.. but it takes almost 1 minute to do the updates. With
nothing else at all touching the DB.. is this normal? The DB is
index'd on the Shelf_Name column.. but how do I know if it is using the
index.. or does that not even matter in this case?

Try putting a begin; and end; command around your updates, so that they
are all one big transaction. That should speed things up.

On Mon, 21 Oct 2002, Williams, Travis L, NPONS wrote:

I have a script that is updating the DB by reading in a file with 467
lines. Each file is named by the shelf the information was gotten
from. Each line is tab seperated and consists of a column name and
the value that it should be update to. The update line in the perl
script looks like this: update table set "column name" = 'value' where
"Shelf_Name" = '$shelf'

Now this works.. but it takes almost 1 minute to do the updates.
With nothing else at all touching the DB.. is this normal? The DB is
index'd on the Shelf_Name column.. but how do I know if it is using
the index.. or does that not even matter in this case?

You can use explain to determine whether it's using the index . You might
want to vacuum and analyze the table in question as well.

I did do a vacuum.. but I don't know what analyze does..

Travis

-----Original Message-----
From: Stephan Szabo [mailto:sszabo@megazone23.bigpanda.com]
Sent: Monday, October 21, 2002 1:51 PM
To: Williams, Travis L, NPONS
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] slow update & index question

On Mon, 21 Oct 2002, Williams, Travis L, NPONS wrote:

I have a script that is updating the DB by reading in a file with 467
lines. Each file is named by the shelf the information was gotten
from. Each line is tab seperated and consists of a column name and
the value that it should be update to. The update line in the perl
script looks like this: update table set "column name" = 'value' where
"Shelf_Name" = '$shelf'

Now this works.. but it takes almost 1 minute to do the updates.
With nothing else at all touching the DB.. is this normal? The DB is
index'd on the Shelf_Name column.. but how do I know if it is using
the index.. or does that not even matter in this case?

You can use explain to determine whether it's using the index . You
might
want to vacuum and analyze the table in question as well.

On Mon, 2002-10-21 at 13:54, Williams, Travis L, NPONS wrote:

I did do a vacuum.. but I don't know what analyze does..

ANALYZE (or VACUUM ANALYZE) resets the stats that the planner uses to
choose between different plans.

LER

Travis

-----Original Message-----
From: Stephan Szabo [mailto:sszabo@megazone23.bigpanda.com]
Sent: Monday, October 21, 2002 1:51 PM
To: Williams, Travis L, NPONS
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] slow update & index question

On Mon, 21 Oct 2002, Williams, Travis L, NPONS wrote:

I have a script that is updating the DB by reading in a file with 467
lines. Each file is named by the shelf the information was gotten
from. Each line is tab seperated and consists of a column name and
the value that it should be update to. The update line in the perl
script looks like this: update table set "column name" = 'value' where
"Shelf_Name" = '$shelf'

Now this works.. but it takes almost 1 minute to do the updates.
With nothing else at all touching the DB.. is this normal? The DB is
index'd on the Shelf_Name column.. but how do I know if it is using
the index.. or does that not even matter in this case?

You can use explain to determine whether it's using the index . You
might
want to vacuum and analyze the table in question as well.

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 E-Mail: ler@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749

On Mon, 21 Oct 2002, Williams, Travis L, NPONS wrote:

I did do a vacuum.. but I don't know what analyze does..

Analyze will update the statistics on the table that are used
for planning purposes. If for example explain shows that it's doing
a sequential scan, it may not for example realize that the index is
useful in limiting the number of rows sufficiently to beat the
sequential scan.