9.0 EXPLAIN Buffers: written=nnnn

Started by Sahagian, Davidabout 14 years ago2 messagesgeneral
Jump to latest
#1Sahagian, David
david.sahagian@emc.com

Do EXPLAIN ANALYZE:

. . . only showing the bottom node . . .
-> Seq Scan on YYYYY
(cost=0.00..37962.29 rows=876029 width=40)
(actual time=16.728..92555.945 rows=876002 loops=1)
Output: foo, bar
Buffers:
shared hit=146
read=29056
written=2325 !!!!!

"Total runtime: 375542.347 ms"

Then Do EXPLAIN ANALYZE again:

. . . only showing the bottom node . . .
-> Seq Scan on YYYYY
(cost=0.00..37962.29 rows=876029 width=40)
(actual time=0.192..2972.127 rows=876002 loops=1)
Output: foo, bar
Buffers:
shared hit=210
read=28992

"Total runtime: 32913.884 ms"

In general, can you tell me why [written=2325] is displayed by the first EXPLAIN, but not the second EXPLAIN ?
The query is a SELECT, so what is getting "written" ?

Note that both EXPLAINs have the exact same plan. Bottom up is: Seq Scan on YYYY, Hash, Seq Scan on XXXX, Hash Join, HashAggregate

I am wondering if it is a clue as to why the "actual time" is so different from one run to the next.

The query is 2 tables joined, with a group by:
select
sum (case when X.hid is null and not Y.cntr = '0' then 1 else 0 end) as colp
from
xxxxx X
inner join yyyyy Y on Y.coln = X.colm
where
X.some_id = 'aeiou'
group by
X.some_type

Thanks,
-dvs-

#2Rodrigo Gonzalez
rjgonzale@estrads.com.ar
In reply to: Sahagian, David (#1)
Re: 9.0 EXPLAIN Buffers: written=nnnn

El Thu, 2 Feb 2012 12:03:20 -0500
<david.sahagian@emc.com> escribió:

Do EXPLAIN ANALYZE:

written=2325 !!!!!

"Total runtime: 375542.347 ms"

Then Do EXPLAIN ANALYZE again:

In general, can you tell me why [written=2325] is displayed by the
first EXPLAIN, but not the second EXPLAIN ? The query is a SELECT, so
what is getting "written" ?

Note that both EXPLAINs have the exact same plan. Bottom up is: Seq
Scan on YYYY, Hash, Seq Scan on XXXX, Hash Join, HashAggregate

I am wondering if it is a clue as to why the "actual time" is so
different from one run to the next.

I can be completely wrong, but maybe it is about hint bits.

Read http://wiki.postgresql.org/wiki/Hint_Bits

Thanks

Rodrigo