Re: ltree + gist index performance degrades significantly over a night

--- Scott Marlowe <smarlowe(at)g2switchworks(dot)com> wrote:

> Are you vacuuming regularly, are your fsm settings high enough, and what
> does vacuum verbose say?

Autovacuum is running, but I do a nightly vacuum analyze. When I just do a
vacuum analyze on the table I get:

data=# vacuum analyze verbose search;
INFO: vacuuming "search"
INFO: index "search_id_pkey" now contains 1344661 row versions in 5134 pages
DETAIL: 9 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.09s/0.07u sec elapsed 4.91 sec.
INFO: index "search_vector_idx" now contains 1344672 row versions in 47725
pages
DETAIL: 9 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 1.77s/0.37u sec elapsed 407.55 sec.
INFO: index "search_item_id_idx" now contains 1344690 row versions in 6652
pages
DETAIL: 9 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.24s/0.08u sec elapsed 45.62 sec.
INFO: "search": removed 9 row versions in 2 pages
DETAIL: CPU 0.00s/0.00u sec elapsed 0.05 sec.
INFO: "letter_search": found 9 removable, 1344661 nonremovable row versions in
33984 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 141 unused item pointers.
0 pages are entirely empty.
CPU 2.41s/0.62u sec elapsed 483.06 sec.
INFO: vacuuming "pg_toast.pg_toast_174918394"
INFO: index "pg_toast_174918394_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.01 sec.
INFO: "pg_toast_174918394": 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.00s/0.00u sec elapsed 0.01 sec.
INFO: analyzing "search"
INFO: "search": scanned 3000 of 33984 pages, containing 119035 live rows and 0
dead rows; 3000 rows in sample, 1348428 estimated total rows
VACUUM

max_fsm_pages = 60000 # min max_fsm_relations*16, 6 bytes each
max_fsm_relations = 2000 # min 100, ~70 bytes each

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com