Re: [WIP] showing index maintenance on EXPLAIN
От | Jaime Casanova |
---|---|
Тема | Re: [WIP] showing index maintenance on EXPLAIN |
Дата | |
Msg-id | CAJKUy5jsz1-h2CZQbNpKTg6=M_pFzZXQGkTasN=gXwz8hN5uEA@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: [WIP] showing index maintenance on EXPLAIN (Amit Kapila <amit.kapila16@gmail.com>) |
Ответы |
Re: [WIP] showing index maintenance on EXPLAIN
(Robert Haas <robertmhaas@gmail.com>)
Re: [WIP] showing index maintenance on EXPLAIN (Amit Kapila <amit.kapila16@gmail.com>) |
Список | 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 по дате отправления:
Предыдущее
От: Michael PaquierДата:
Сообщение: Ignore files in src/interfaces/libpq generated by windows builds