Re: CLUSTER VERBOSE (9.1.3)

On Wed, Mar 7, 2012 at 9:33 AM, Larry Rosenman <ler(at)lerctr(dot)org> wrote:
>>> Is there any way to get more info out of CLUSTER VERBOSE so it
>>> says what index it's working on AFTER the table re-write?
>>>
>>> INFO:  clustering "public.values" using sequential scan and sort
>>> INFO:  "values": found 0 removable, 260953511 nonremovable row
>>> versions in 4224437 pages DETAIL:  0 dead row versions cannot be
>>> removed yet. CPU 168.02s/4324.68u sec elapsed 8379.12 sec.
>>>
>>>
>>> And at this point it's doing something(tm), I assume re-doing the
>>> indexes.
>>>
>>> It would be nice(tm) to get more info.
>>>
>>> Ideas?
>>
>> Try setting client_min_messages=DEBUG1.  At least on current
>> sources that gives some additional, relevant output; I think that's
>> probably there in 9.1.x as well.
>>
> Thanks.  That helps some, but I'd like to see:
> 1) these moved up to INFO when CLUSTER VERBOSE
> 2) time/statistics on each index build
>
> INFO:  clustering "public.values" using sequential scan and sort
> INFO:  "values": found 0 removable, 260953511 nonremovable row
> versions in 4224437 pages
> DETAIL:  0 dead row versions cannot be removed yet.
> CPU 167.57s/4325.52u sec elapsed 7687.28 sec.
> DEBUG:  building index "values_idx1" on table "values"
> DEBUG:  building index "values_idx2" on table "values"
> DEBUG:  building index "values_idx3" on table "values"
> DEBUG:  building index "values_idx4" on table "values"
> DEBUG:  building index "values_idx5" on table "values"
> DEBUG:  building index "values_idx6" on table "values"
> DEBUG:  building index "values_idx7" on table "values"
> DEBUG:  building index "values_idx_cluster" on table "values"
> CLUSTER
> Time: 28997806.474 ms
>
> Comments?

Seems like a good enhancement to me, if someone can figure out how to
do it cleanly. Unfortunately those debug messages may not be in a
place where it's real easy for them to know whether they're being
called from CLUSTER VERBOSE or not, so it might take a little thought
to produce a clean patch for this.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company