Re: Possible to find disk IOs for a Query?

On 01/09/16 10:01, Bobby Mozumder wrote:

> Is it possible to find the number of disk IOs performed for a query? EXPLAIN ANALYZE looks like it shows number of sequential rows scanned, but not number of IOs.
>
> My database is on an NVMe SSD, and am trying to cut microseconds of disk IO per query by possibly denormalizing.
>

Try EXPLAIN (ANALYZE, BUFFERS) e.g:

bench=# EXPLAIN (ANALYZE, BUFFERS) SELECT count(*) FROM pgbench_accounts
WHERE bid=1;
QUERY PLAN

--------------------------------------------------------------------------------
--------------------------------------------------------------------
Finalize Aggregate (cost=217118.90..217118.91 rows=1 width=8) (actual
time=259
.723..259.723 rows=1 loops=1)
Buffers: shared hit=2370 read=161727
-> Gather (cost=217118.68..217118.89 rows=2 width=8) (actual
time=259.686..
259.720 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=2370 read=161727
-> Partial Aggregate (cost=216118.68..216118.69 rows=1
width=8) (actu
al time=258.473..258.473 rows=1 loops=3)
Buffers: shared hit=2208 read=161727
-> Parallel Seq Scan on pgbench_accounts
(cost=0.00..216018.33
rows=40139 width=0) (actual time=0.014..256.820 rows=33333 loops=3)
Filter: (bid = 1)
Rows Removed by Filter: 3300000
Buffers: shared hit=2208 read=161727
Planning time: 0.044 ms
Execution time: 260.357 ms
(14 rows)

...shows the number of (8k unless you've changed it) pages read from
disk or cache. Now this might not be exactly what you are after - the
other way to attack this is to trace your backend postgres process (err
perfmon...no idea how to do this on windows...) and count read and write
calls.

regards

Mark