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
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
