From: Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>
To: Rahila Syed <rahila(dot)syed(at)2ndquadrant(dot)com>
Cc: Andres Freund <andres(at)anarazel(dot)de>, Robert Haas <robertmhaas(at)gmail(dot)com>, Simon Riggs <simon(at)2ndquadrant(dot)com>, Pg Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>, David Fetter <david(at)fetter(dot)org>, Pavan Deolasee <pavan(dot)deolasee(at)gmail(dot)com>, Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp>
Subject: Re: monitoring CREATE INDEX [CONCURRENTLY]
Date: 2019-04-01 21:33:03
Message-ID: 20190401213303.GA26854@alvherre.pgsql
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

On 2019-Apr-02, Rahila Syed wrote:

> 1. FWIW, below results for CIC show that blocks_done does not become equal
> to blocks_total at the end of the phase or it processes last 800 blocks so
> fast that
> the update is not visible in less than 1 secs interval.

Yeah, I noticed this too and decided it's not fixable, nor it's
desirable to spend a lot of effort in getting it perfectly accurate -- I
mean, we could introduce locking or sleeping to get the results we want,
but do we really want to make the index building process slower just to
report those block numbers.

Anyway, I think this effect is caused by parallel btree building: those
final blocks are scanned by a worker process, and the leader didn't get
the latest block number scanned. If you set
max_parallel_maintenance_workers to 0, the effect disappears.

(I used \watch 0.01 to see even faster progress updates; even in that
case the final batch of block numbers is not seen in the updates. The
btree build code is stupidly fast.)

> 2. However in case of partitioned tables, the following difference in
> blocks_done versus blocks_total at the end of phase is notably high for the
> first partition . Subsequent partitions show negligible difference.
> Partition 1:
> Mon Mar 25 14:27:57 IST 2019
> pid | datid | datname | relid | phase |
> lockers_total | lockers_done | current_locker_pid | blocks_total |
> blocks_done | tuples_total | tuples_done | partitions_total |
> partitions_done
> -------+-------+----------+-------+----------------------------+---------------+--------------+--------------------+--------------+-------------+--------------+-------------+------------------+-----------------
> 10630 | 13533 | postgres | 16394 | building index: table scan
> | 0 | 0 | 0 | 381342 |
> 221233 | 0 | 0 | 3 | 0
> (1 row)

Hmm, in my tests with partitioned tables, I never noticed such a large
discrepancy. I'm going to have another look. 800 blocks scanned by
workers I can believe, but 160000 sounds a bit too much.

> 3. Sorry for nitpicking, I think following phase name can be made more
> consistent with the others.
> The non-am specific phase for scanning a table is named as scan heap while
> am-specific one is named as table scan.
> Can we use heap for am-specific one as well since heap is used elsewhere in
> progress reporting too?

Hmm, I'd rather go the other way and use "table" everywhere rather than
heap, since we've been getting a lot of stuff done for table AMs.

> 4. - scan = table_beginscan_parallel(btspool->heap,
> ParallelTableScanFromBTShared(btshared));
> + scan = table_beginscan_parallel(btspool->heap,
> +
> ParallelTableScanFromBTShared(btshared));
> Is this change required?

Yes, for my OCD.

Álvaro Herrera
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Alvaro Herrera 2019-04-01 21:34:41 Re: Fix XML handling with DOCTYPE
Previous Message Chapman Flack 2019-04-01 21:24:34 Re: Fix XML handling with DOCTYPE