Re: [WIP] showing index maintenance on EXPLAIN - Mailing list pgsql-hackers

From Jaime Casanova
Subject Re: [WIP] showing index maintenance on EXPLAIN
Date
Msg-id CAJKUy5jsz1-h2CZQbNpKTg6=M_pFzZXQGkTasN=gXwz8hN5uEA@mail.gmail.com
Whole thread Raw
In response to Re: [WIP] showing index maintenance on EXPLAIN  (Amit Kapila <amit.kapila16@gmail.com>)
Responses Re: [WIP] showing index maintenance on EXPLAIN
Re: [WIP] showing index maintenance on EXPLAIN
List pgsql-hackers
On Wed, May 7, 2014 at 10:52 PM, Amit Kapila <amit.kapila16@gmail.com> wrote:
> On Thu, May 8, 2014 at 5:30 AM, Jaime Casanova <jaime@2ndquadrant.com> wrote:
>> Hi,
>>
>> This patch implements $subject only when ANALYZE and VERBOSE are on.
>> I made it that way because for years nobody seemed interested in this
>> info (at least no one did it) so i decided that maybe is to much
>> information for most people (actually btree indexes are normally very
>> fast).
>
>
> Why to capture only for Index Insert/Update and not for Read; is it
> because Read will be always fast ot implementation complexity?
>

EXPLAIN ANALYZE already shows that on any SELECT that uses an index in
some way. Or are you thinking on something else?

> Why not similar timings for heap?
>

well "actual time" shows us total time of the operation so just
deducting the time spent on triggers, indexes and planning seems like
a way to get "heap modification time".

yes, maybe we still need some additional data. for example, i could
want to know how much time we spent extending a relation.

> Why can't we print when only Analyze is used with Explain, the
> execution time is printed with Analyze option?
>

i'm not sure the info is useful for everyone, i'm not opposed to show
it all the time though

> Could you please tell in what all kind of scenario's, do you expect it
> to be useful?
> One I could think is that if there are multiple indexes on a table and user
> wants to find out if any particular index is consuming more time.
>

exactly my use case. consider this plan (we spent 78% of the time
updating the index uniq_idx_on_text):
  QUERY PLAN
--------------------------------------------------------------------------------------------Insert on public.t1 (actual
time=0.540..0.540rows=0 loops=1)  ->  Result (actual time=0.046..0.049 rows=1 loops=1)        Output: <some long data
here>Indexuniq_idx_on_text on t1: time=0.421 rows=1Index t1_pkey on t1: time=0.027 rows=1Total runtime: 0.643 ms 
(6 rows)

so i want to answer questions like, how much an index is hurting write
performance? once i know that i can look for alternative solutions.
In that vein, it was interesting to see how fastupdate affect
performance in a GIN index using gin_trgm_ops (5 times slower with
fastupdate=off)

(fastupdate=on) Index idx_ggin on t1: time=0.418 rows=1
(fastupdate=off) Index idx_ggin on t1: time=2.205 rows=1

this is not different to showing trigger time info, which we already do

--
Jaime Casanova         www.2ndQuadrant.com
Professional PostgreSQL: Soporte 24x7 y capacitación
Phone: +593 4 5107566         Cell: +593 987171157



pgsql-hackers by date:

Previous
From: Michael Paquier
Date:
Subject: Ignore files in src/interfaces/libpq generated by windows builds
Next
From: Heikki Linnakangas
Date:
Subject: Re: [COMMITTERS] pgsql: Clean up jsonb code.