[PERFORM] Query plan with missing timespans

2015-04-22 Thread Andomar 
This is a question about how to read "explain analyze".  I've anonymized 
column names and table names.


In the output of "explain analyze" below, what was the query doing 
between actual time 1.426 and 17.077?


Kind regards,
Andomar


 HashAggregate  (cost=862.02..862.62 rows=48 width=90) (actual 
time=17.077..17.077 rows=0 loops=1)

   Group Key: col, col, col
   Buffers: shared hit=6018
   ->  Nested Loop  (cost=1.52..861.18 rows=48 width=90) (actual 
time=17.077..17.077 rows=0 loops=1)

 Buffers: shared hit=6018
 ->  Nested Loop  (cost=1.09..26.74 rows=303 width=41) (actual 
time=0.033..1.426 rows=384 loops=1)

   Buffers: shared hit=845
   ->  Index Scan using ind on tbl  (cost=0.42..8.44 rows=1 
width=8) (actual time=0.010..0.011 rows=1 loops=1)

 Index Cond: (col = 123)
 Buffers: shared hit=4
   ->  Index Scan using ind on tbl (cost=0.67..18.28 rows=2 
width=49) (actual time=0.020..1.325 rows=384 loops=1)

 Index Cond: (col = col)
 Filter: (col = 'value')
 Rows Removed by Filter: 2720
 Buffers: shared hit=841
 ->  Index Scan using index on tbl (cost=0.42..2.74 rows=1 
width=57) (actual time=0.040..0.040 rows=0 loops=384)

   Index Cond: (col = col)
   Filter: (col = ANY (ARRAY[func('value1'::text), 
func('value2'::text)]))

   Rows Removed by Filter: 1
   Buffers: shared hit=5173
 Planning time: 0.383 ms
 Execution time: 17.128 ms


Version: PostgreSQL 9.4.1 on x86_64-unknown-linux-gnu, compiled by gcc 
(GCC) 4.4.7 20120313 (Red Hat 4.4.7-11), 64-bit




--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Query plan with missing timespans

2015-04-22 Thread Andomar 

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?


Is there a way to tell postgres that a function will always return the 
same result for the same parameter, within the same transaction?


Kind regards,
Andomar


--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance