Change the meaning of "tuples returned/s"

[kmoppel]

Hi, hoping to kickstart a discussion on a thing that's bugging me for some years :D

In short - currently the "tuples returned/s" value is not really helpful at all for the following reasons:

1. It does not show the actual row counts returned to the calling applications - what most developers assume.
2. In instead shows only one side of the Postgres-internal tuple reading story - namely tup_returned, i.e. seq. scanned rows + scanned index entries, while ignoring the other side - tup_fetched, i.e. rows fetched by those scanned index entries.

Thus I think to improve the sitation would make sense to consider a combination of:

1. Add "tup_fetched" to "tuples returned" the story
2. Rename "tuples returned/s" to something like "tuples scanned/s"
3. Start showing a new "rows returned/s" column based on pg_stat_statements.rows delta instead, when pg_stat_statements is available.

In any case the current "tuples returned" is just confusing for non-Postgres-guru users - for example below is a screenshot of app doing simple key read-write transactions, but were worried as "tuples returned" was very high still - indicating maybe some unwanted seq. scans, but most likely it's just caused by internal pg_class, pg_attribute etc access on query planning.

[blogh]

Hi, thank you for the feedback.

• Add "tup_fetched" to "tuples returned" the story
• Rename "tuples returned/s" to something like "tuples scanned/s"

I agree with your concern and I think the proposed change is a good idea.
Maybe we should scrap the column altogether?

• Start showing a new "rows returned/s" column based on pg_stat_statements.rows delta instead, when pg_stat_statements is available.

I am not sure about this one. The data could be wrong if the view "size" is
too small ... but at least the data means something. So I guess, it's a plus.

I've had ideas for backend and pg_stat_statement dedicated panels for
a while but never got to it.

[kmoppel]

Thanks for the quick response! Actually, yes - scrapping the column altogether is not a bad idea as well - as currently indeed it shows usually huge numbers, that are jumpy - and might actually not mean much, as most of those internal returns / fetches are cached...so yeah, less confusion is great.

And then could add the "approx rows/s" to the upcoming "pg_stat_statement" panel. My $0.02 on the panel - a good starting point would be something similar to pgcenter (which sadly doesn't seem to be actively maintained anymore) - but a bit smarter. Meaning we don't show the queries from pg_activity + don't show historic stuff as well, i.e. only the queries which have call count changes since we start pg_activity. And sorted by default by total_exec_time.

[kmoppel]

PS another idea - one global level block "crunching" number that actually would mean something could be "total processed block bandwith", based all of pg_statio read + hit counters. This shows approximately how much data flows through the instance per second. Let me know if this sounds good - I could do a patch @blogh

[blogh]

I am not sure about that one, I still lack experience using pg_stat_io for tuning. I'll check
with my colleagues what they think of the idea, thanks for the pointer anyway, it's great to
have feedback.

I am leaving tomorrow for two weeks hand have a few thing, I have to wrap up before then.

I'll mark this as enhancement. And I am ok with the other two proposition:

• remove tuples returned
• do something with pg_stat_statements in a panel

I'll look into it when I am back.

[blogh]

I haven't forgotten this, I am on a separate project which requires my attention right now.