Обсуждение: slow update & index question
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 informationwas gotten from. Each line is tab seperated and consists of a column name and the value that it should be updateto. 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 matterin this case? Travis
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, 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, 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.