Re: Query plan with missing timespans

Hello,

At Wed, 22 Apr 2015 21:59:27 +0200, Andomar <andomar(at)aule(dot)net> wrote in <5537FD9F(dot)3060109(at)aule(dot)net>
> > Looping through 384 index scans of tbl, each taking 0.040 ms.
> > That's 15.36 ms. That leaves 0.291 ms unaccounted for, which means
> > that's about how much time the top level nested loop took to do its
> > work.
> >
>
> Thanks for your reply, interesting! I'd have thought that this line
> actually implied 0 ms:
>
> actual time=0.040..0.040
>
> But based on your reply this means, it took between 0.040 and 0.040 ms
> for each loop?

You might mistake how to read it (besides the scale:). The index
scan took 40ms as the average through all loops. The number at
the left of '..' is "startup time".

http://www.postgresql.org/docs/9.4/static/sql-explain.html

# Mmm.. this doesn't explain about "startup time".. It's the time
# taken from execution start to returning the first result.

At Wed, 22 Apr 2015 14:18:40 -0600, Jason Petersen <jason(at)citusdata(dot)com> wrote in <4FB6E62B-3876-4D5C-9737-52F23D6693B6(at)citusdata(dot)com>
> > On Apr 22, 2015, at 1:59 PM, Andomar <andomar(at)aule(dot)net> wrote:
> >
> > Is there a way to tell postgres that a function will always return the same result for the same parameter, within the same transaction?
>
> Yup… read over the Function Volatility Categories
> <http://www.postgresql.org/docs/9.4/static/xfunc-volatility.html>
> page and decide which you need. What you’re describing is
> STABLE (or slightly stricter than STABLE, since STABLE makes
> that guarantee only for a single statement within a
> transaction).

And you will see what volatility category does a function go
under in pg_proc system catalog.

=# select proname, provolatile from pg_proc where oid = 'random'::regproc;
proname | provolatile
---------+-------------
random | v

random() is a volatile funciton.

http://www.postgresql.org/docs/9.4/static/catalog-pg-proc.html

regards,

--
Kyotaro Horiguchi
NTT Open Source Software Center