Re: [PERFORM] Slow select, insert, update
Paul Langard [EMAIL PROTECTED] writes: Having trouble with one table (see time to count records below!). Fairly new to postgres so any help much appreciated. It only contains 9,106 records - as you can see from: select count(id) from project count 9106 1 row(s) Total runtime: 45,778.813 ms ... the database is regularly vaccuumed. Hmm. You might try a VACUUM FULL and a REINDEX on the table (you don't say what version you are running--REINDEX is sometimes needed on 7.3 and below). Also, use EXPLAIN ANALYZE on your query and post the result--that's helpful diagnostic information. -Doug -- Let us cross over the river, and rest under the shade of the trees. --T. J. Jackson, 1863 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Slow select, insert, update
Paul, Paul Langard wrote: Having trouble with one table (see time to count records below!). Fairly new to postgres so any help much appreciated. It only contains 9,106 records - as you can see from: select count(id) from project *count *9106 1 row(s) Total runtime: 45,778.813 ms snip ... the database is regularly vaccuumed. Have you tried doing a VACUUM FULL, CLUSTER, or drop/restore on the table? This sounds symptomatic of a table with a bunch of dead tuples not in the FSM (free space map). Only tuples in the FSM are reclaimed by a regular VACUUM. If your FSM parameters in postgresql.conf are not big enough for your ratio of UPDATE/DELETE operations to VACUUM frequency, you will end up with dead tuples that will only be reclaimed by a VACUUM FULL. To prevent this problem in the future, look at increasing your FSM size and possibly vacuuming more frequently or using pg_autovacuum. Good Luck, Bill Montgomery ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] Slow select, insert, update
Paul Langard [EMAIL PROTECTED] writes: select count(id) from project count 9106 1 row(s) Total runtime: 45,778.813 ms Yipes. The only explanation I can think of is tremendous table bloat. What do you get from vacuum verbose project --- in particular, how many pages in the table? ... the database is regularly vaccuumed. Not regularly enough, perhaps ... or else you need to increase the free space map size parameters. In any case you'll probably need to do one round of vacuum full to get this table back within bounds. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] Slow select, insert, update
Does that mean reindex is not needed for PG version 7.4? In what kind situations under PG 7.4, reindex is worthwhile? Thanks, Here is doc from 7.3: PostgreSQL is unable to reuse B-tree index pages in certain cases. The problem is that if indexed rows are deleted, those index pages can only be reused by rows with similar values. For example, if indexed rows are deleted and newly inserted/updated rows have much higher values, the new rows can't use the index space made available by the deleted rows. Instead, such new rows must be placed on new index pages. In such cases, disk space used by the index will grow indefinitely, even if VACUUM is run frequently. As a solution, you can use the REINDEX command periodically to discard pages used by deleted rows. There is also contrib/reindexdb which can reindex an entire database. The counterpart of 7.4 is: In some situations it is worthwhile to rebuild indexes periodically with the REINDEX command. (There is also contrib/reindexdb which can reindex an entire database.) However, PostgreSQL 7.4 has substantially reduced the need for this activity compared to earlier releases. --- Doug McNaught [EMAIL PROTECTED] wrote: Paul Langard [EMAIL PROTECTED] writes: Having trouble with one table (see time to count records below!). Fairly new to postgres so any help much appreciated. It only contains 9,106 records - as you can see from: select count(id) from project count 9106 1 row(s) Total runtime: 45,778.813 ms ... the database is regularly vaccuumed. Hmm. You might try a VACUUM FULL and a REINDEX on the table (you don't say what version you are running--REINDEX is sometimes needed on 7.3 and below). Also, use EXPLAIN ANALYZE on your query and post the result--that's helpful diagnostic information. -Doug -- Let us cross over the river, and rest under the shade of the trees. --T. J. Jackson, 1863 ---(end of broadcast)--- TIP 8: explain analyze is your friend __ Do you Yahoo!? Read only the mail you want - Yahoo! Mail SpamGuard. http://promotions.yahoo.com/new_mail ---(end of broadcast)--- TIP 8: explain analyze is your friend
