Re: Planning time in explain/explain analyze

From: Greg Stark <stark(at)mit(dot)edu>
To: Stephen Frost <sfrost(at)snowman(dot)net>
Cc: Andreas Karlsson <andreas(at)proxel(dot)se>, Robert Haas <robertmhaas(at)gmail(dot)com>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Planning time in explain/explain analyze
Date: 2014-01-09 20:58:01
Message-ID: CAM-w4HMhAEXn8F-q=frditZ043Pz6vxhDkLPVs4JJkKAZKt5rw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

On Thu, Jan 9, 2014 at 1:50 PM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
>> I do not think we want to always measure the time it took to
>> generate a plan due to slow clocks on some architectures. Also I
>> feel that such a patch would be more invasive.
>
> The slow-clock argument is really quite frustrating for those of us who
> are looking to add more and more metrics to PG.

Especially since implementing gettimeofday quickly is a solved problem
on most architectures.

However I don't see the issue here. Two gettimeofday calls per query
plan is not really going to hurt even on systems where it's slow. The
problems we run into are explain analyze which runs getimeofday twice
for every node for every tuple processed. For cpu-bound queries that's
can become the dominant cost.

The only way two gettimeofday calls per query plan becomes an issue is
if you're executing non-cached queries repeatedly on data that's
entirely in ram. That means the query processing is entirely cpu-bound
and adding two syscalls could actually be noticeable. The mitigation
strategy would be to prepare and cache the query handle to execute it
again.

> We're nowhere near the
> level that we need to be and it shows (particularly for users coming
> from $OTHER-RDBMS). Perhaps we should try and come up with a solution
> to address those cases (turn off metrics ala turning off stats?) while
> not preventing us from gathering metrics on more reasonable systems.

--
greg

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Jim Nasby 2014-01-09 21:04:47 Re: [ANNOUNCE] IMCS: In Memory Columnar Store for PostgreSQL
Previous Message Josh Berkus 2014-01-09 20:55:02 Re: [BUG] Archive recovery failure on 9.3+.