So this is what I did about my two complaints earlier about the explain buffer patch.
a) Changed the line description to "Total Buffer Usage" which at least hints that it's something more akin to the "Total runtime" listed at the bottom than the "actual time". b) Used units of memory -- I formatted them with 3 significant digits (unless the unit is bytes or kB where that would be silly). It's just what looked best to my eye. I'm finding "hit" and "read" kind of confusing myself but don't really have any better idea. It's not entirely clear whether read is the total accesses out of which some are cache hits or if they're two disjoint sets. postgres=# explain (analyze,buffers) select * from x limit 10000; QUERY PLAN ----------------------------------------------------------------------------------------------------------------- Limit (cost=0.00..266.68 rows=10000 width=105) (actual time=0.023..53.964 rows=10000 loops=1) Total Buffer Usage: shared hit=8kB read=1.30MB -> Seq Scan on x (cost=0.00..10667.00 rows=400000 width=105) (actual time=0.019..20.311 rows=10000 loops=1) Total Buffer Usage: shared hit=8kB read=1.30MB Total runtime: 71.074 ms (5 rows) -- greg
*** a/src/backend/commands/explain.c --- b/src/backend/commands/explain.c *************** *** 98,104 **** static void ExplainJSONLineEnding(ExplainState *es); static void ExplainYAMLLineStarting(ExplainState *es); static void escape_json(StringInfo buf, const char *str); static void escape_yaml(StringInfo buf, const char *str); ! /* * ExplainQuery - --- 98,104 ---- static void ExplainYAMLLineStarting(ExplainState *es); static void escape_json(StringInfo buf, const char *str); static void escape_yaml(StringInfo buf, const char *str); ! static double normalize_memory(double amount, char **unit, int *precision); /* * ExplainQuery - *************** *** 1081,1127 **** ExplainNode(Plan *plan, PlanState *planstate, if (has_shared || has_local || has_temp) { appendStringInfoSpaces(es->str, es->indent * 2); ! appendStringInfoString(es->str, "Buffers:"); if (has_shared) { appendStringInfoString(es->str, " shared"); ! if (usage->shared_blks_hit > 0) ! appendStringInfo(es->str, " hit=%ld", ! usage->shared_blks_hit); if (usage->shared_blks_read > 0) ! appendStringInfo(es->str, " read=%ld", ! usage->shared_blks_read); if (usage->shared_blks_written > 0) ! appendStringInfo(es->str, " written=%ld", ! usage->shared_blks_written); if (has_local || has_temp) appendStringInfoChar(es->str, ','); } if (has_local) { ! appendStringInfoString(es->str, " local"); ! if (usage->local_blks_hit > 0) ! appendStringInfo(es->str, " hit=%ld", ! usage->local_blks_hit); ! if (usage->local_blks_read > 0) ! appendStringInfo(es->str, " read=%ld", ! usage->local_blks_read); ! if (usage->local_blks_written > 0) ! appendStringInfo(es->str, " written=%ld", ! usage->local_blks_written); if (has_temp) appendStringInfoChar(es->str, ','); } if (has_temp) { appendStringInfoString(es->str, " temp"); if (usage->temp_blks_read > 0) ! appendStringInfo(es->str, " read=%ld", ! usage->temp_blks_read); ! if (usage->temp_blks_written > 0) ! appendStringInfo(es->str, " written=%ld", ! usage->temp_blks_written); } appendStringInfoChar(es->str, '\n'); } --- 1081,1143 ---- if (has_shared || has_local || has_temp) { appendStringInfoSpaces(es->str, es->indent * 2); ! appendStringInfoString(es->str, "Total Buffer Usage:"); if (has_shared) { + char *hit_unit, *read_unit, *written_unit; + int hit_prec, read_prec, written_prec; + double hit_mem = normalize_memory((double)usage->shared_blks_hit * BLCKSZ, &hit_unit, &hit_prec); + double read_mem = normalize_memory((double)usage->shared_blks_read * BLCKSZ, &read_unit, &read_prec); + double written_mem = normalize_memory((double)usage->shared_blks_written * BLCKSZ, &written_unit, &written_prec); + appendStringInfoString(es->str, " shared"); ! appendStringInfo(es->str, " hit=%.*f%s", ! hit_prec, hit_mem, hit_unit); if (usage->shared_blks_read > 0) ! appendStringInfo(es->str, " read=%.*f%s", ! read_prec, read_mem, read_unit); if (usage->shared_blks_written > 0) ! appendStringInfo(es->str, " written=%.*f%s", ! written_prec, written_mem, written_unit); if (has_local || has_temp) appendStringInfoChar(es->str, ','); } if (has_local) { ! char *hit_unit, *read_unit, *written_unit; ! int hit_prec, read_prec, written_prec; ! double hit_mem = normalize_memory((double)usage->local_blks_hit * BLCKSZ, &hit_unit, &hit_prec); ! double read_mem = normalize_memory((double)usage->local_blks_read * BLCKSZ, &read_unit, &read_prec); ! double written_mem = normalize_memory((double)usage->local_blks_written * BLCKSZ, &written_unit, &written_prec); ! ! appendStringInfoString(es->str, " local"); ! if (usage->local_blks_hit > 0) ! appendStringInfo(es->str, " hit=%.*f%s", ! hit_prec, hit_mem, hit_unit); ! if (usage->local_blks_read > 0) ! appendStringInfo(es->str, " read=%.*f%s", ! read_prec, read_mem, read_unit); ! if (usage->local_blks_written > 0) ! appendStringInfo(es->str, " written=%.*f%s", ! written_prec, written_mem, written_unit); if (has_temp) appendStringInfoChar(es->str, ','); } if (has_temp) { + char *read_unit, *written_unit; + int read_prec, written_prec; + double read_mem = normalize_memory((double)usage->temp_blks_read * BLCKSZ, &read_unit, &read_prec); + double written_mem = normalize_memory((double)usage->temp_blks_written * BLCKSZ, &written_unit, &written_prec); + appendStringInfoString(es->str, " temp"); if (usage->temp_blks_read > 0) ! appendStringInfo(es->str, " read=%.*f%s", ! read_prec, read_mem, read_unit); ! if (usage->temp_blks_written > 0) ! appendStringInfo(es->str, " written=%.*f%s", ! written_prec, written_mem, written_unit); } appendStringInfoChar(es->str, '\n'); } *************** *** 2153,2155 **** escape_yaml(StringInfo buf, const char *str) --- 2169,2204 ---- appendStringInfo(buf, "%s", str); } + + /* + * For a quantity of bytes pick a reasonable display unit for it and + * return the quantity in that unit. Also return the unit name and a + * reasonable precision via the reference parameters. + */ + + static double normalize_memory(double amount, char **unit, int *precision) + { + static char *units[] = {"bytes", "kB", "MB", "GB", "TB", "PB"}; + char **u = units, **last = units + (sizeof(units)/sizeof(*units)-1); + + while (amount > 1024.0 && u < last) + { + amount /= 1024.0; + u += 1; + } + + *unit = *u; + + /* if it's bytes or kB then don't print decimals since that's less + * than blocksize, otherwise always print 3 significant digits */ + if (u == units || u == units+1 ) + *precision = 0; + else if (amount < 10) + *precision = 2; + else if (amount < 100) + *precision = 1; + else + *precision = 0; + + return amount; + }
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers