[HACKERS] 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.