Is there any other way to do this? - Mailing list pgsql-performance

Hi, I have a small table that has only 23 rows, but I do frequent updates(
every second ) on it.

After running the updates for a while, the performance of SELECT from that
table has deteriated into something like 30 seconds.

So, natually, I did a VACUUM ANALYZE first. Here is the VERBOSE output.

Test=> VACUUM VERBOSE analyze schedule ;
INFO:  vacuuming "public.schedule"
INFO:  index "schedule_pkey" now contains 23 row versions in 2519 pages
DETAIL:  2499 index pages have been deleted, 2499 are currently reusable.
CPU 0.27s/0.04u sec elapsed 12.49 sec.
INFO:  "schedule": found 0 removable, 23 nonremovable row versions in 37638
pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 974282 unused item pointers.
0 pages are entirely empty.
CPU 3.64s/0.48u sec elapsed 76.15 sec.
INFO:  vacuuming "pg_toast.pg_toast_22460"
INFO:  index "pg_toast_22460_index" now contains 0 row versions in 1 pages
DETAIL:  0 index pages have been deleted, 0 are currently reusable.
CPU 0.01s/0.00u sec elapsed 0.03 sec.
INFO:  "pg_toast_22460": found 0 removable, 0 nonremovable row versions in 0
pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.01s/0.00u sec elapsed 0.03 sec.
INFO:  analyzing "public.schedule"
INFO:  "schedule": 37638 pages, 23 rows sampled, 23 estimated total rows
VACUUM

And it didn't help at all. The explain of the query still shows up as:

Test=> explain select id from schedule;
                          QUERY PLAN
-------------------------------------------------------------
  Seq Scan on schedule  (cost=0.00..37638.23 rows=23 width=4)
(1 row)

It still takes 30 seconds to finish a simple query. ugh.

So I then tried VACUUM FULL schedule. Here is the output:

fazzt=> VACUUM FULL VERBOSE schedule ;
INFO:  vacuuming "public.schedule"
INFO:  "schedule": found 0 removable, 23 nonremovable row versions in 37638
pages
DETAIL:  0 dead row versions cannot be removed yet.
Nonremovable row versions range from 253 to 418 bytes long.
There were 974282 unused item pointers.
Total free space (including removable row versions) is 303672968 bytes.
37629 pages are or will become empty, including 0 at the end of the table.
37638 pages containing 303672968 free bytes are potential move destinations.
CPU 3.08s/0.50u sec elapsed 28.64 sec.
INFO:  index "schedule_pkey" now contains 23 row versions in 2182 pages
DETAIL:  0 index row versions were removed.
2162 index pages have been deleted, 2162 are currently reusable.
CPU 0.28s/0.02u sec elapsed 10.90 sec.
INFO:  "schedule": moved 13 row versions, truncated 37638 to 1 pages
DETAIL:  CPU 10.83s/10.96u sec elapsed 370.42 sec.
INFO:  index "schedule_pkey" now contains 23 row versions in 2182 pages
DETAIL:  13 index row versions were removed.
2162 index pages have been deleted, 2162 are currently reusable.
CPU 0.20s/0.05u sec elapsed 10.33 sec.
INFO:  vacuuming "pg_toast.pg_toast_22460"
INFO:  "pg_toast_22460": found 0 removable, 0 nonremovable row versions in 0
pages
DETAIL:  0 dead row versions cannot be removed yet.
Nonremovable row versions range from 0 to 0 bytes long.
There were 0 unused item pointers.
Total free space (including removable row versions) is 0 bytes.
0 pages are or will become empty, including 0 at the end of the table.
0 pages containing 0 free bytes are potential move destinations.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  index "pg_toast_22460_index" now contains 0 row versions in 1 pages
DETAIL:  0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM


This time it worked! But VACUUM FULL requires an exclusive lock on the table
which I don't really want to grant. So my question is: why is VACUUM ANALYZE
didn't do the job? Is there any setting I can tweak to make a VACUUM without
granting a exclusive lock?

Thanks!


Wei