Re: Bloat check query

On Tue, Aug 26, 2014 at 5:44 PM, Keith Fiske <keith(at)omniti(dot)com> wrote:

> So I recently wrote this script to help us in monitoring for table/index
> bloat
>
> https://github.com/keithf4/pg_bloat_check
>
> I based my query off of the one contained in check_postgres, since I
> thought it seemed fairly accurate with what I was expecting to get back.
>
>
> https://github.com/bucardo/check_postgres/blob/master/check_postgres.pl#L3512
>
> A client however, showed me that running the bloat check from heroku
> brings back some additional results. The additional results are actually
> tables we were kind of expecting to see that the check_postgres query does
> not return.
>
> I've tried decoding these queries to understand what they're actually
> doing, but they're both quite a bit over my head. I did notice one thing in
> the check_postgres one:
>
> CASE WHEN v ~ 'mingw32' OR v ~ '64-bit' THEN 8 ELSE 4 END AS ma
>
> Is that a typo? It seems to be looking for the 32bit version of mingw but
> the 64-bit version when compiled elsewhere. The heroku version just sets
> this value to 4 outright (since they know what they're running). I tried
> changing this value to see if it made a difference, but it doesn't seem to.
>
> Anyone that's better able to evaluate which one of these two bloat check
> queries is more accurate able to provide some assistance?
>
> --
> Keith Fiske
> Database Administrator
> OmniTI Computer Consulting, Inc.
> http://www.keithf4.com
>

Helps if I include the query from Heroku

https://github.com/heroku/heroku-pg-extras/blob/master/lib/heroku/command/pg.rb#L438-L504

--
Keith Fiske
Database Administrator
OmniTI Computer Consulting, Inc.
http://www.keithf4.com