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
sequentialrows 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