temp_buffers vs temp vs local and explain

-hackers,

I was reviewing an explain plan today and with some help from Andrew G,
I got a lot more information than I deserved. It did however bring up
quite a usability issue that I think we should consider.

Let's review the following two lines:

Sort Method: external merge Disk: 19352kB
Buffers: shared hit=257714, temp read=8822 written=8808

Now the first line is pretty obvious. We spilled over work_mem and hit
the disk for ~ 20MB of use.

The second line is not so clear.

Buffers, shared_buffers? We hit 257714 of those. That makes sense but
what about temp? Temp refers to temp files, not temp_buffers or temp
tables. Temp buffers refers to a temp table (ala create temp table) but
is represented as local in an explain plan. Further the values of temp
are blocks, not bytes.

Basically, it is a little convoluted.

I am not 100% what the answer here is but it seems more consistency
might be a good start.

Also, it would be a huge boon for many (almost all) of our users if we
could just do (something like) this:

EXPLAIN (ANALYZE,SUMMARY)

And it said:

Query 1
========
shared_buffers
*
*
work_mem
* Total Used =
* In Memory =
* On Disk =
Rows
* Estimated =
* Actual =

etc...

I know that access to the details are needed but for day to day
operations for a huge portion of our users, they just want to know how
much memory they need, or if they need a faster disk etc...

Thanks,

JD

--
Command Prompt, Inc. http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.
Unless otherwise stated, opinions are my own.