Re: Index overhead cost reporting

On 7 December 2013 19:41, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Thom Brown <thom(at)linux(dot)com> writes:
>> I was wondering whether anyone has any insight with regards to
>> measuring and reporting the overhead of maintaining indexes on
>> relations. If an UPDATE is issued to a table with, say, 6 indexes, it
>> would be useful to determine how much time is spent updating each of
>> those indexes. And perhaps such timings would not be confined to
>> indexes, but also other dependants that add overhead, such as
>> triggers, rules, and in future, eager incremental materialised view
>> updates.
>
> We already do track the time spent in triggers. Although Kevin might
> have a different idea, I'd think that matview updates should also be
> driven by triggers, so that the last item there would be covered.

Oh, of course. :)

>> Is that something that could be provided in an EXPLAIN ANALYSE node?
>
> Well, it'd not be particularly difficult to add measurements of the time
> spent in ExecInsertIndexTuples, but I'd have some concerns about that:
>
> * Instrumentation overhead. That's already painful on machines with
> slow gettimeofday, and this has the potential to add a lot more,
> especially with the more expansive readings of your proposal.
>
> * Is it really measuring the right thing? To a much greater degree
> than for some other things you might try to measure, just counting
> time spent in ExecInsertIndexTuples is going to understate the true
> cost of updating an index, because so much of the true cost is paid
> asynchronously; viz, writing WAL as well as the actual index pages.
> We already have that issue with measuring the runtime of a
> ModifyTable node as a whole, but slicing and dicing that time
> ten ways would make the results even more untrustworthy, IMO.
>
> * There are also other costs to think of, such as the time spent by
> VACUUM on maintaining the index, and the time spent by the planner
> considering (perhaps vainly) whether it can use the index for each
> query that reads the table. In principle you could instrument
> VACUUM to track the time it spends updating each index, and log
> that in the pgstats infrastructure. (I'd even think that might be
> a good idea, except for the bloat effect on the pgstats files.)
> I'm not at all sure there's any practical way to measure the distributed
> planner overhead; it's not paid in discrete chunks large enough to be
> timed easily. Perhaps it's small enough to ignore, but I'm not sure.
>
> Bottom line, I think it's a harder problem than it might seem at
> first glance.

Thanks for taking the time to explain the above.

Perhaps I may have misunderstood, or not explained my question with
enough detail, but you appear to be including activity that would, in
all likelihood, occur after the DML has returned confirmation to the
user that it has completed; in particular, VACUUM. What I was
thinking of was an execution plan node to communicate the index
modifications that are carried out prior to confirmation of the query
completing. The bgwriter, WAL writer et al. that spring into action
as a result of the index being updated wouldn't, as I see it, be
included.

So in essence, I'd only be looking for a breakdown of anything that
adds to the duration of the DML statement. However, it sounds like
even that isn't straightforward from what you've written.

Thom