Re: Index bloat problem?

On Fri, 22 Apr 2005 10:06:33 -0400, "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> said:
> David Roussel <pgsql-performance(at)diroussel(dot)xsmail(dot)com> writes:
> > |dave_data_update_events r 1593600.0 40209
> > |dave_data_update_events_event_id_key i 1912320.0 29271
>
> Hmm ... what PG version is this, and what does VACUUM VERBOSE on
> that table show?

PG 7.4

The disparity seems to have sorted itself out now, so hampering futher
investigations. I guess the regular inserts of new data, and the nightly
deletion and index recreation did it. However, we did suffer reduced
performance and the strange cardinality for several days before it went
away. For what it's worth..

ndb=# vacuum verbose iso_pjm_data_update_events;
INFO: vacuuming "public.iso_pjm_data_update_events"
INFO: index "iso_pjm_data_update_events_event_id_key" now contains
1912320 row versions in 29271 pages
DETAIL: 21969 index pages have been deleted, 20000 are currently
reusable.
CPU 6.17s/0.88u sec elapsed 32.55 sec.
INFO: index "iso_pjm_data_update_events_lds_idx" now contains 1912320
row versions in 7366 pages
DETAIL: 0 index pages have been deleted, 0 are currently reusable.
CPU 3.52s/0.57u sec elapsed 14.35 sec.
INFO: index "iso_pjm_data_update_events_obj_id_idx" now contains
1912320 row versions in 7366 pages
DETAIL: 0 index pages have been deleted, 0 are currently reusable.
CPU 3.57s/0.58u sec elapsed 12.87 sec.
INFO: "iso_pjm_data_update_events": found 0 removable, 1912320
nonremovable row versions in 40209 pages
DETAIL: 159384 dead row versions cannot be removed yet.
There were 745191 unused item pointers.
0 pages are entirely empty.
CPU 18.26s/3.62u sec elapsed 74.35 sec.
VACUUM

After each insert is does this...

VACUUM ANALYZE iso_pjm_DATA_UPDATE_EVENTS
VACUUM ANALYZE iso_pjm_CONTROL

Each night it does this...

BEGIN
DROP INDEX iso_pjm_control_obj_id_idx
DROP INDEX iso_pjm_control_real_name_idx
DROP INDEX iso_pjm_data_update_events_lds_idx
DROP INDEX iso_pjm_data_update_events_obj_id_idx
CREATE UNIQUE INDEX iso_pjm_control_obj_id_idx ON
iso_pjm_control(obj_id)
CLUSTER iso_pjm_control_obj_id_idx ON iso_pjm_control
CREATE UNIQUE INDEX iso_pjm_control_real_name_idx ON
iso_pjm_control(real_name)
CREATE INDEX iso_pjm_data_update_events_lds_idx ON
iso_pjm_data_update_events(lds)
CREATE INDEX iso_pjm_data_update_events_obj_id_idx ON
iso_pjm_data_update_events(obj_id)
COMMIT

Note there is no reference to iso_pjm_data_update_events_event_id_key
which is the index that went wacky on us. Does that seem weird to you?

Thanks

David