Re: Slow Query - Mailing list pgsql-performance

From Tom Lane
Subject Re: Slow Query
Date
Msg-id 6184.1188866982@sss.pgh.pa.us
Whole thread Raw
In response to Re: Slow Query  (Shawn <postgres@xmtservices.net>)
List pgsql-performance
Shawn <postgres@xmtservices.net> writes:
> "Kevin Grittner" <Kevin.Grittner@wicourts.gov> wrote:
>> Did you ever capture the output of VACUUM VERBOSE against this table

> vacuum verbose analyze shawns_data;
> ...
> INFO:  index "shawns_data_pkey" now contains 15445 row versions in
> 35230 pages

[ and comparably bloated sizes for other indexes ]

Ouch!  The table itself doesn't look nearly as bad:

> INFO:  "shawns_data": found 21444 removable, 15445 nonremovable row
> versions in 770 pages

but you've got a spectacularly bad case of index bloat.  An index 50
times bigger than its table is Not Good.  I think you'd find that
"REINDEX TABLE shawns_data" does wonders for the situation.

The next question is how it got to be that way... what is your
vacuuming policy for this database?  Maybe you need to raise
max_fsm_pages?

            regards, tom lane

pgsql-performance by date:

Previous
From: Shawn
Date:
Subject: Re: Slow Query
Next
From: "Merlin Moncure"
Date:
Subject: Re: schemas to limit data access