Re: EXPLAIN BUFFERS

On Wed, Dec 9, 2009 at 12:36 AM, Takahiro Itagaki
<itagaki(dot)takahiro(at)oss(dot)ntt(dot)co(dot)jp> wrote:
> 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

I'm not sure whether this is a good idea or not. Let me read the
patch. I'm not sure an EXPLAIN option is really an adequate
substitute for log_statement_stats - the latter will let you get stats
for all of your queries automatically, I believe, and might still be
useful as a quick and dirty tool.

> IMHO, we could remove those options completely because we can use
> EXPLAIN BUFFERS and DTrace probes instead of them.

We certainly should NOT count on dtrace as a substitute for anything.
It's not available on Windows, or all other platforms either.

> =# 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)

I still think this is a bad format. Instead of putting "(" and ")"
around each phrase, can't we just separate them with a "," or ";"?
The filter uses parentheses in a mathematical way, for grouping
related items. Not all filters are surrounded by parentheses
(consider a filter like "WHERE x", x being a boolean column) and some
will have multiple sets, if there are ANDs and ORs in there.

...Robert