Re: EXPLAIN BUFFERS

Greg Smith <greg(at)2ndquadrant(dot)com> wrote:

> I just executed that. Note that there are two bits of subjective
> tweaking possible to do with this one when it's committed: slimming
> down the width of the display, and Euler's suggestion's for rewording.
> I linked to both of those messages in the CF app, labeled as notes the
> committer might want to consider, but that the patch hasn't been updated
> to include yet.

Sure, I should have merge all of the comments. Patch attached.

- Updated documentation as Euler's suggestion, but I replaced
the "It" of the second last sentence to "This parameter".
- Updated the output format as follows. I think this format is the most
similar to existing lines. ("actual" by ANALYZE and "Filter:").

Note that the patch also removes buffer counters from log_statement_stats,
but we only have brief descriptions about the options. Our documentation
say nothing about buffer counters, so I didn't modify those lines in sgml.
http://developer.postgresql.org/pgdocs/postgres/runtime-config-statistics.html#RUNTIME-CONFIG-STATISTICS-MONITOR
IMHO, we could remove those options completely because we can use
EXPLAIN BUFFERS and DTrace probes instead of them.

=# EXPLAIN (BUFFERS, ANALYZE) SELECT *
FROM pgbench_accounts a, pgbench_branches b
WHERE a.bid = b.bid AND abalance > 0 ORDER BY abalance;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
Sort (cost=2891.03..2891.04 rows=1 width=461) (actual time=22.494..22.494 rows=0 loops=1)
Sort Key: a.abalance
Sort Method: quicksort Memory: 25kB
Blocks: (shared hit=96 read=1544 written=0) (local hit=0 read=0 written=0) (temp read=0 written=0)
-> Nested Loop (cost=0.00..2891.02 rows=1 width=461) (actual time=22.488..22.488 rows=0 loops=1)
Join Filter: (a.bid = b.bid)
Blocks: (shared hit=96 read=1544 written=0) (local hit=0 read=0 written=0) (temp read=0 written=0)
-> Seq Scan on pgbench_accounts a (cost=0.00..2890.00 rows=1 width=97) (actual time=22.486..22.486 rows=0 loops=1)
Filter: (abalance > 0)
Blocks: (shared hit=96 read=1544 written=0) (local hit=0 read=0 written=0) (temp read=0 written=0)
-> Seq Scan on pgbench_branches b (cost=0.00..1.01 rows=1 width=364) (never executed)
Blocks: (shared hit=0 read=0 written=0) (local hit=0 read=0 written=0) (temp read=0 written=0)
Total runtime: 22.546 ms
(13 rows)

Regards,
---
Takahiro Itagaki
NTT Open Source Software Center