agora inbox for pgsql-docs@postgresql.org
help / color / mirror / Atom feedmore detailed description of tup_returned and tup_fetched
15+ messages / 4 participants
[nested] [flat]
* more detailed description of tup_returned and tup_fetched
@ 2021-05-14 08:00 Masahiro Ikeda <ikedamsh@oss.nttdata.com>
0 siblings, 1 reply; 15+ messages in thread
From: Masahiro Ikeda @ 2021-05-14 08:00 UTC (permalink / raw)
To: pgsql-docs@lists.postgresql.org
Hi,
I worried the difference between "tup_returned" and "tup_fetched" in
pg_stat_database. I assumed that "tup_returned" means the number of tuples
that returned to clients. Of course, this is wrong.
So, why don't you describe in more detail? If my understanding is right, they
correspond to "seq_tup_read" and "idx_tup_fetch" in pg_stat_all_tables.
Regards,
--
Masahiro Ikeda
NTT DATA CORPORATION
Attachments:
[text/x-patch] add-more-detailed-description-of-tup_returned-and-tup_fetched.patch (935B, ../../9eeeccdb-5dd7-90f9-2807-a4b5d2b76ca3@oss.nttdata.com/2-add-more-detailed-description-of-tup_returned-and-tup_fetched.patch)
download | inline diff:
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index dcbb10fb6f..09a22a43ac 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -3712,7 +3712,7 @@ SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event i
<structfield>tup_returned</structfield> <type>bigint</type>
</para>
<para>
- Number of rows returned by queries in this database
+ Number of live rows returned by sequential scans of queries in this database
</para></entry>
</row>
@@ -3721,7 +3721,7 @@ SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event i
<structfield>tup_fetched</structfield> <type>bigint</type>
</para>
<para>
- Number of rows fetched by queries in this database
+ Number of live rows fetched by index scan of queries in this database
</para></entry>
</row>
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: more detailed description of tup_returned and tup_fetched
@ 2021-05-17 06:32 Fujii Masao <masao.fujii@oss.nttdata.com>
parent: Masahiro Ikeda <ikedamsh@oss.nttdata.com>
0 siblings, 1 reply; 15+ messages in thread
From: Fujii Masao @ 2021-05-17 06:32 UTC (permalink / raw)
To: Masahiro Ikeda <ikedamsh@oss.nttdata.com>; pgsql-docs@lists.postgresql.org
On 2021/05/14 17:00, Masahiro Ikeda wrote:
> Hi,
>
> I worried the difference between "tup_returned" and "tup_fetched" in
> pg_stat_database. I assumed that "tup_returned" means the number of tuples
> that returned to clients. Of course, this is wrong.
- Number of rows returned by queries in this database
+ Number of live rows returned by sequential scans of queries in this database
- Number of rows fetched by queries in this database
+ Number of live rows fetched by index scan of queries in this database
I found the following comments in pgstat.h. So maybe even these
new descriptions are incorrect?
* Note: for a table, tuples_returned is the number of tuples successfully
* fetched by heap_getnext, while tuples_fetched is the number of tuples
* successfully fetched by heap_fetch under the control of bitmap indexscans.
* For an index, tuples_returned is the number of index entries returned by
* the index AM, while tuples_fetched is the number of tuples successfully
* fetched by heap_fetch under the control of simple indexscans for this index.
Regards,
--
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: more detailed description of tup_returned and tup_fetched
@ 2021-05-17 09:58 Masahiro Ikeda <ikedamsh@oss.nttdata.com>
parent: Fujii Masao <masao.fujii@oss.nttdata.com>
0 siblings, 1 reply; 15+ messages in thread
From: Masahiro Ikeda @ 2021-05-17 09:58 UTC (permalink / raw)
To: Fujii Masao <masao.fujii@oss.nttdata.com>; pgsql-docs@lists.postgresql.org
On 2021/05/17 15:32, Fujii Masao wrote:
>
>
> On 2021/05/14 17:00, Masahiro Ikeda wrote:
>> Hi,
>>
>> I worried the difference between "tup_returned" and "tup_fetched" in
>> pg_stat_database. I assumed that "tup_returned" means the number of tuples
>> that returned to clients. Of course, this is wrong.
>
> - Number of rows returned by queries in this database
> + Number of live rows returned by sequential scans of queries in this
> database
>
> - Number of rows fetched by queries in this database
> + Number of live rows fetched by index scan of queries in this database
>
> I found the following comments in pgstat.h. So maybe even these
> new descriptions are incorrect?
>
> * Note: for a table, tuples_returned is the number of tuples successfully
> * fetched by heap_getnext, while tuples_fetched is the number of tuples
> * successfully fetched by heap_fetch under the control of bitmap indexscans.
> * For an index, tuples_returned is the number of index entries returned by
> * the index AM, while tuples_fetched is the number of tuples successfully
> * fetched by heap_fetch under the control of simple indexscans for this index.
Oh, Thanks!
I updated the sentences using the descriptions of
"pg_stat_all_tables.seq_tup_read", "pg_stat_all_tables.idx_tup_fetch", and
"pg_stat_all_index.idx_tup_read".
- Number of rows returned by queries in this database
+ Number of rows returned by queries in this database. The rows
correspond to the live rows fetched by sequential scans and index entries
returned by scans on indexes
- Number of rows fetched by queries in this database
+ Number of rows fetched by queries in this database. The rows
correspond to the live rows fetched by index scans
Regards,
--
Masahiro Ikeda
NTT DATA CORPORATION
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: more detailed description of tup_returned and tup_fetched
@ 2021-05-17 11:46 Fujii Masao <masao.fujii@oss.nttdata.com>
parent: Masahiro Ikeda <ikedamsh@oss.nttdata.com>
0 siblings, 1 reply; 15+ messages in thread
From: Fujii Masao @ 2021-05-17 11:46 UTC (permalink / raw)
To: Masahiro Ikeda <ikedamsh@oss.nttdata.com>; pgsql-docs@lists.postgresql.org
On 2021/05/17 18:58, Masahiro Ikeda wrote:
>
>
> On 2021/05/17 15:32, Fujii Masao wrote:
>>
>>
>> On 2021/05/14 17:00, Masahiro Ikeda wrote:
>>> Hi,
>>>
>>> I worried the difference between "tup_returned" and "tup_fetched" in
>>> pg_stat_database. I assumed that "tup_returned" means the number of tuples
>>> that returned to clients. Of course, this is wrong.
>>
>> - Number of rows returned by queries in this database
>> + Number of live rows returned by sequential scans of queries in this
>> database
>>
>> - Number of rows fetched by queries in this database
>> + Number of live rows fetched by index scan of queries in this database
>>
>> I found the following comments in pgstat.h. So maybe even these
>> new descriptions are incorrect?
>>
>> * Note: for a table, tuples_returned is the number of tuples successfully
>> * fetched by heap_getnext, while tuples_fetched is the number of tuples
>> * successfully fetched by heap_fetch under the control of bitmap indexscans.
>> * For an index, tuples_returned is the number of index entries returned by
>> * the index AM, while tuples_fetched is the number of tuples successfully
>> * fetched by heap_fetch under the control of simple indexscans for this index.
>
> Oh, Thanks!
>
> I updated the sentences using the descriptions of
> "pg_stat_all_tables.seq_tup_read", "pg_stat_all_tables.idx_tup_fetch", and
> "pg_stat_all_index.idx_tup_read".
>
> - Number of rows returned by queries in this database
> + Number of rows returned by queries in this database. The rows
> correspond to the live rows fetched by sequential scans and index entries
> returned by scans on indexes
This is still not correct because this counter is incremented even when
other scan like TidScan happens?
Regards,
--
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: more detailed description of tup_returned and tup_fetched
@ 2021-05-18 04:20 Masahiro Ikeda <ikedamsh@oss.nttdata.com>
parent: Fujii Masao <masao.fujii@oss.nttdata.com>
0 siblings, 1 reply; 15+ messages in thread
From: Masahiro Ikeda @ 2021-05-18 04:20 UTC (permalink / raw)
To: Fujii Masao <masao.fujii@oss.nttdata.com>; pgsql-docs@lists.postgresql.org
On 2021/05/17 20:46, Fujii Masao wrote:
>
>
> On 2021/05/17 18:58, Masahiro Ikeda wrote:
>>
>>
>> On 2021/05/17 15:32, Fujii Masao wrote:
>>>
>>>
>>> On 2021/05/14 17:00, Masahiro Ikeda wrote:
>>>> Hi,
>>>>
>>>> I worried the difference between "tup_returned" and "tup_fetched" in
>>>> pg_stat_database. I assumed that "tup_returned" means the number of tuples
>>>> that returned to clients. Of course, this is wrong.
>>>
>>> - Number of rows returned by queries in this database
>>> + Number of live rows returned by sequential scans of queries in this
>>> database
>>>
>>> - Number of rows fetched by queries in this database
>>> + Number of live rows fetched by index scan of queries in this database
>>>
>>> I found the following comments in pgstat.h. So maybe even these
>>> new descriptions are incorrect?
>>>
>>> * Note: for a table, tuples_returned is the number of tuples successfully
>>> * fetched by heap_getnext, while tuples_fetched is the number of tuples
>>> * successfully fetched by heap_fetch under the control of bitmap indexscans.
>>> * For an index, tuples_returned is the number of index entries returned by
>>> * the index AM, while tuples_fetched is the number of tuples successfully
>>> * fetched by heap_fetch under the control of simple indexscans for this
>>> index.
>>
>> Oh, Thanks!
>>
>> I updated the sentences using the descriptions of
>> "pg_stat_all_tables.seq_tup_read", "pg_stat_all_tables.idx_tup_fetch", and
>> "pg_stat_all_index.idx_tup_read".
>>
>> - Number of rows returned by queries in this database
>> + Number of rows returned by queries in this database. The rows
>> correspond to the live rows fetched by sequential scans and index entries
>> returned by scans on indexes
>
> This is still not correct because this counter is incremented even when
> other scan like TidScan happens?
Sorry, I couldn't find the way to increment tup_returned by TidScan.
Do you mean that Tid Range Scan increments the counter?
Tid Range Scan increments the tup_returned, and
pg_stat_all_tables.seq_tup_read is also incremented. I thought it's ok because
Tid Range Scan is like sequential scan. That's the reason why the document of
pg_stat_all_tables.seq_tup_read says "Number of live rows fetched by
sequential scans"
Regards,
--
Masahiro Ikeda
NTT DATA CORPORATION
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: more detailed description of tup_returned and tup_fetched
@ 2021-05-18 07:01 Fujii Masao <masao.fujii@oss.nttdata.com>
parent: Masahiro Ikeda <ikedamsh@oss.nttdata.com>
0 siblings, 1 reply; 15+ messages in thread
From: Fujii Masao @ 2021-05-18 07:01 UTC (permalink / raw)
To: Masahiro Ikeda <ikedamsh@oss.nttdata.com>; pgsql-docs@lists.postgresql.org
On 2021/05/18 13:20, Masahiro Ikeda wrote:
>
>
> On 2021/05/17 20:46, Fujii Masao wrote:
>>
>>
>> On 2021/05/17 18:58, Masahiro Ikeda wrote:
>>>
>>>
>>> On 2021/05/17 15:32, Fujii Masao wrote:
>>>>
>>>>
>>>> On 2021/05/14 17:00, Masahiro Ikeda wrote:
>>>>> Hi,
>>>>>
>>>>> I worried the difference between "tup_returned" and "tup_fetched" in
>>>>> pg_stat_database. I assumed that "tup_returned" means the number of tuples
>>>>> that returned to clients. Of course, this is wrong.
>>>>
>>>> - Number of rows returned by queries in this database
>>>> + Number of live rows returned by sequential scans of queries in this
>>>> database
>>>>
>>>> - Number of rows fetched by queries in this database
>>>> + Number of live rows fetched by index scan of queries in this database
>>>>
>>>> I found the following comments in pgstat.h. So maybe even these
>>>> new descriptions are incorrect?
>>>>
>>>> * Note: for a table, tuples_returned is the number of tuples successfully
>>>> * fetched by heap_getnext, while tuples_fetched is the number of tuples
>>>> * successfully fetched by heap_fetch under the control of bitmap indexscans.
>>>> * For an index, tuples_returned is the number of index entries returned by
>>>> * the index AM, while tuples_fetched is the number of tuples successfully
>>>> * fetched by heap_fetch under the control of simple indexscans for this
>>>> index.
>>>
>>> Oh, Thanks!
>>>
>>> I updated the sentences using the descriptions of
>>> "pg_stat_all_tables.seq_tup_read", "pg_stat_all_tables.idx_tup_fetch", and
>>> "pg_stat_all_index.idx_tup_read".
>>>
>>> - Number of rows returned by queries in this database
>>> + Number of rows returned by queries in this database. The rows
>>> correspond to the live rows fetched by sequential scans and index entries
>>> returned by scans on indexes
>>
>> This is still not correct because this counter is incremented even when
>> other scan like TidScan happens?
>
> Sorry, I couldn't find the way to increment tup_returned by TidScan.
> Do you mean that Tid Range Scan increments the counter?
Yes, what I tried to mean is Tid Range Scan.
>
> Tid Range Scan increments the tup_returned, and
> pg_stat_all_tables.seq_tup_read is also incremented. I thought it's ok because
> Tid Range Scan is like sequential scan.
Yes, you're right. One interesting thing I found is;
when Tid Range Scan happens, seq_tup_read is incremented
but seq_scan is not. I'm not sure if this is expected behavior or not.
> That's the reason why the document of
> pg_stat_all_tables.seq_tup_read says "Number of live rows fetched by
> sequential scans"
Regarding the original issue, as far as I understand correctly,
* pg_stat_database.tup_returned = sum(pg_stat_all_tables.seq_tup_read) + sum(pg_stat_all_indexes.idx_tup_read)
* pg_stat_database.tup_fetched = sum(pg_stat_all_tables.idx_tup_fetch)
But the counters for some system catalogs like pg_database shared
across all databases of a cluster are excluded from that calculation.
Is this my understanding right? If right, probably we can reuse
the existing descriptions for those counters to document
pg_stat_database counters. For example,
pg_stat_database.tup_returned:
Number of live rows fetched by sequential and index scans in this database
pg_stat_database.tup_fetched:
Number of index entries returned by scans on indexes in this database
Regards,
--
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: more detailed description of tup_returned and tup_fetched
@ 2021-05-18 09:23 Masahiro Ikeda <ikedamsh@oss.nttdata.com>
parent: Fujii Masao <masao.fujii@oss.nttdata.com>
0 siblings, 1 reply; 15+ messages in thread
From: Masahiro Ikeda @ 2021-05-18 09:23 UTC (permalink / raw)
To: Fujii Masao <masao.fujii@oss.nttdata.com>; pgsql-docs@lists.postgresql.org
On 2021/05/18 16:01, Fujii Masao wrote:
> On 2021/05/18 13:20, Masahiro Ikeda wrote:
>> Tid Range Scan increments the tup_returned, and
>> pg_stat_all_tables.seq_tup_read is also incremented. I thought it's ok because
>> Tid Range Scan is like sequential scan.
>
> Yes, you're right. One interesting thing I found is;
> when Tid Range Scan happens, seq_tup_read is incremented
> but seq_scan is not. I'm not sure if this is expected behavior or not.
The following comment says that this behavior is expected. But, I agree it's
odd and it's natural both seq_tup_read and seq_scan are incremented at the
same time or not...
/*
* Currently, we only have a stats counter for sequential heap scans (but
* e.g for bitmap scans the underlying bitmap index scans will be counted,
* and for sample scans we update stats for tuple fetches).
*/
if (scan->rs_base.rs_flags & SO_TYPE_SEQSCAN)
pgstat_count_heap_scan(scan->rs_base.rs_rd);
>> That's the reason why the document of
>> pg_stat_all_tables.seq_tup_read says "Number of live rows fetched by
>> sequential scans"
>
> Regarding the original issue, as far as I understand correctly,
>
> * pg_stat_database.tup_returned = sum(pg_stat_all_tables.seq_tup_read) +
> sum(pg_stat_all_indexes.idx_tup_read)
> * pg_stat_database.tup_fetched = sum(pg_stat_all_tables.idx_tup_fetch)
>
> But the counters for some system catalogs like pg_database shared
> across all databases of a cluster are excluded from that calculation.
> Is this my understanding right? If right, probably we can reuse
> the existing descriptions for those counters to document
> pg_stat_database counters. For example,
Yes, my understanding is same now.
> pg_stat_database.tup_returned:> Number of live rows fetched by sequential and index scans in this database
I wonder "live rows fetched by index scans" may mislead. I think "live" means
it's not dead tuple and "rows" mean the tuple user want to get.
But, pg_stat_all_indexes.idx_tup_read says that "index entires returned by
scans on this index". There is no meaning of "live" and "rows", so I thought
it's better to distinguish them.
So, why don't you change to "Number of live rows fetched by sequential scans
and index entries returned by index scans in this database"?
> pg_stat_database.tup_fetched:
> Number of index entries returned by scans on indexes in this database
Is this the sum of pg_stat_all_indexes.idx_tup_read? This is accounted to
pg_stat_database.tup_returned.
"Number of live rows fetched by index scans in this database" seems to be correct.
Regards,
--
Masahiro Ikeda
NTT DATA CORPORATION
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: more detailed description of tup_returned and tup_fetched
@ 2021-05-18 11:10 Fujii Masao <masao.fujii@oss.nttdata.com>
parent: Masahiro Ikeda <ikedamsh@oss.nttdata.com>
0 siblings, 1 reply; 15+ messages in thread
From: Fujii Masao @ 2021-05-18 11:10 UTC (permalink / raw)
To: Masahiro Ikeda <ikedamsh@oss.nttdata.com>; pgsql-docs@lists.postgresql.org
On 2021/05/18 18:23, Masahiro Ikeda wrote:
>
>
> On 2021/05/18 16:01, Fujii Masao wrote:
>> On 2021/05/18 13:20, Masahiro Ikeda wrote:
>>> Tid Range Scan increments the tup_returned, and
>>> pg_stat_all_tables.seq_tup_read is also incremented. I thought it's ok because
>>> Tid Range Scan is like sequential scan.
>>
>> Yes, you're right. One interesting thing I found is;
>> when Tid Range Scan happens, seq_tup_read is incremented
>> but seq_scan is not. I'm not sure if this is expected behavior or not.
>
> The following comment says that this behavior is expected. But, I agree it's
> odd and it's natural both seq_tup_read and seq_scan are incremented at the
> same time or not...
>
> /*
> * Currently, we only have a stats counter for sequential heap scans (but
> * e.g for bitmap scans the underlying bitmap index scans will be counted,
> * and for sample scans we update stats for tuple fetches).
> */
> if (scan->rs_base.rs_flags & SO_TYPE_SEQSCAN)
> pgstat_count_heap_scan(scan->rs_base.rs_rd);
>
>
>>> That's the reason why the document of
>>> pg_stat_all_tables.seq_tup_read says "Number of live rows fetched by
>>> sequential scans"
>>
>> Regarding the original issue, as far as I understand correctly,
>>
>> * pg_stat_database.tup_returned = sum(pg_stat_all_tables.seq_tup_read) +
>> sum(pg_stat_all_indexes.idx_tup_read)
>> * pg_stat_database.tup_fetched = sum(pg_stat_all_tables.idx_tup_fetch)
>>
>> But the counters for some system catalogs like pg_database shared
>> across all databases of a cluster are excluded from that calculation.
>> Is this my understanding right? If right, probably we can reuse
>> the existing descriptions for those counters to document
>> pg_stat_database counters. For example,
>
> Yes, my understanding is same now.
>
>
>> pg_stat_database.tup_returned:> Number of live rows fetched by sequential and index scans in this database
>
> I wonder "live rows fetched by index scans" may mislead. I think "live" means
> it's not dead tuple and "rows" mean the tuple user want to get.
>
> But, pg_stat_all_indexes.idx_tup_read says that "index entires returned by
> scans on this index". There is no meaning of "live" and "rows", so I thought
> it's better to distinguish them.
>
> So, why don't you change to "Number of live rows fetched by sequential scans
> and index entries returned by index scans in this database"?
Yes, LGTM.
>> pg_stat_database.tup_fetched:
>> Number of index entries returned by scans on indexes in this database
> Is this the sum of pg_stat_all_indexes.idx_tup_read? This is accounted to
> pg_stat_database.tup_returned.
I was thinking that pg_stat_database.tup_fetched is the same as
the sum of pg_stat_all_tables.idx_tup_fetch. Because they both
are incremented by bitmap index scans, but pg_stat_all_indexes.idx_tup_read
is not.
Regards,
--
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: more detailed description of tup_returned and tup_fetched
@ 2021-05-20 00:46 Masahiro Ikeda <ikedamsh@oss.nttdata.com>
parent: Fujii Masao <masao.fujii@oss.nttdata.com>
0 siblings, 1 reply; 15+ messages in thread
From: Masahiro Ikeda @ 2021-05-20 00:46 UTC (permalink / raw)
To: Fujii Masao <masao.fujii@oss.nttdata.com>; pgsql-docs@lists.postgresql.org
On 2021/05/18 20:10, Fujii Masao wrote:
>
>
> On 2021/05/18 18:23, Masahiro Ikeda wrote:
>>
>>
>> On 2021/05/18 16:01, Fujii Masao wrote:
>>> On 2021/05/18 13:20, Masahiro Ikeda wrote:
>>>> Tid Range Scan increments the tup_returned, and
>>>> pg_stat_all_tables.seq_tup_read is also incremented. I thought it's ok
>>>> because
>>>> Tid Range Scan is like sequential scan.
>>>
>>> Yes, you're right. One interesting thing I found is;
>>> when Tid Range Scan happens, seq_tup_read is incremented
>>> but seq_scan is not. I'm not sure if this is expected behavior or not.
>>
>> The following comment says that this behavior is expected. But, I agree it's
>> odd and it's natural both seq_tup_read and seq_scan are incremented at the
>> same time or not...
>>
>> /*
>> * Currently, we only have a stats counter for sequential heap scans (but
>> * e.g for bitmap scans the underlying bitmap index scans will be counted,
>> * and for sample scans we update stats for tuple fetches).
>> */
>> if (scan->rs_base.rs_flags & SO_TYPE_SEQSCAN)
>> pgstat_count_heap_scan(scan->rs_base.rs_rd);
>>
>>
>>>> That's the reason why the document of
>>>> pg_stat_all_tables.seq_tup_read says "Number of live rows fetched by
>>>> sequential scans"
>>>
>>> Regarding the original issue, as far as I understand correctly,
>>>
>>> * pg_stat_database.tup_returned = sum(pg_stat_all_tables.seq_tup_read) +
>>> sum(pg_stat_all_indexes.idx_tup_read)
>>> * pg_stat_database.tup_fetched = sum(pg_stat_all_tables.idx_tup_fetch)
>>>
>>> But the counters for some system catalogs like pg_database shared
>>> across all databases of a cluster are excluded from that calculation.
>>> Is this my understanding right? If right, probably we can reuse
>>> the existing descriptions for those counters to document
>>> pg_stat_database counters. For example,
>>
>> Yes, my understanding is same now.
>>
>>
>>> pg_stat_database.tup_returned:> Number of live rows fetched by sequential
>>> and index scans in this database
>>
>> I wonder "live rows fetched by index scans" may mislead. I think "live" means
>> it's not dead tuple and "rows" mean the tuple user want to get.
>>
>> But, pg_stat_all_indexes.idx_tup_read says that "index entires returned by
>> scans on this index". There is no meaning of "live" and "rows", so I thought
>> it's better to distinguish them.
>>
>> So, why don't you change to "Number of live rows fetched by sequential scans
>> and index entries returned by index scans in this database"?
>
> Yes, LGTM.
>
>
>>> pg_stat_database.tup_fetched:
>>> Number of index entries returned by scans on indexes in this database
>> Is this the sum of pg_stat_all_indexes.idx_tup_read? This is accounted to
>> pg_stat_database.tup_returned.
>
> I was thinking that pg_stat_database.tup_fetched is the same as
> the sum of pg_stat_all_tables.idx_tup_fetch. Because they both
> are incremented by bitmap index scans, but pg_stat_all_indexes.idx_tup_read
> is not.
Yes. So, "Number of index entries returned by scans on indexes in this
database" is incorrect, and "Number of live rows fetched by index scans in
this database" is correct?
Regards,
--
Masahiro Ikeda
NTT DATA CORPORATION
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: more detailed description of tup_returned and tup_fetched
@ 2021-05-20 08:00 Fujii Masao <masao.fujii@oss.nttdata.com>
parent: Masahiro Ikeda <ikedamsh@oss.nttdata.com>
0 siblings, 1 reply; 15+ messages in thread
From: Fujii Masao @ 2021-05-20 08:00 UTC (permalink / raw)
To: Masahiro Ikeda <ikedamsh@oss.nttdata.com>; pgsql-docs@lists.postgresql.org
On 2021/05/20 9:46, Masahiro Ikeda wrote:
>
>
> On 2021/05/18 20:10, Fujii Masao wrote:
>>
>>
>> On 2021/05/18 18:23, Masahiro Ikeda wrote:
>>>
>>>
>>> On 2021/05/18 16:01, Fujii Masao wrote:
>>>> On 2021/05/18 13:20, Masahiro Ikeda wrote:
>>>>> Tid Range Scan increments the tup_returned, and
>>>>> pg_stat_all_tables.seq_tup_read is also incremented. I thought it's ok
>>>>> because
>>>>> Tid Range Scan is like sequential scan.
>>>>
>>>> Yes, you're right. One interesting thing I found is;
>>>> when Tid Range Scan happens, seq_tup_read is incremented
>>>> but seq_scan is not. I'm not sure if this is expected behavior or not.
>>>
>>> The following comment says that this behavior is expected. But, I agree it's
>>> odd and it's natural both seq_tup_read and seq_scan are incremented at the
>>> same time or not...
>>>
>>> /*
>>> * Currently, we only have a stats counter for sequential heap scans (but
>>> * e.g for bitmap scans the underlying bitmap index scans will be counted,
>>> * and for sample scans we update stats for tuple fetches).
>>> */
>>> if (scan->rs_base.rs_flags & SO_TYPE_SEQSCAN)
>>> pgstat_count_heap_scan(scan->rs_base.rs_rd);
>>>
>>>
>>>>> That's the reason why the document of
>>>>> pg_stat_all_tables.seq_tup_read says "Number of live rows fetched by
>>>>> sequential scans"
>>>>
>>>> Regarding the original issue, as far as I understand correctly,
>>>>
>>>> * pg_stat_database.tup_returned = sum(pg_stat_all_tables.seq_tup_read) +
>>>> sum(pg_stat_all_indexes.idx_tup_read)
>>>> * pg_stat_database.tup_fetched = sum(pg_stat_all_tables.idx_tup_fetch)
>>>>
>>>> But the counters for some system catalogs like pg_database shared
>>>> across all databases of a cluster are excluded from that calculation.
>>>> Is this my understanding right? If right, probably we can reuse
>>>> the existing descriptions for those counters to document
>>>> pg_stat_database counters. For example,
>>>
>>> Yes, my understanding is same now.
>>>
>>>
>>>> pg_stat_database.tup_returned:> Number of live rows fetched by sequential
>>>> and index scans in this database
>>>
>>> I wonder "live rows fetched by index scans" may mislead. I think "live" means
>>> it's not dead tuple and "rows" mean the tuple user want to get.
>>>
>>> But, pg_stat_all_indexes.idx_tup_read says that "index entires returned by
>>> scans on this index". There is no meaning of "live" and "rows", so I thought
>>> it's better to distinguish them.
>>>
>>> So, why don't you change to "Number of live rows fetched by sequential scans
>>> and index entries returned by index scans in this database"?
>>
>> Yes, LGTM.
>>
>>
>>>> pg_stat_database.tup_fetched:
>>>> Number of index entries returned by scans on indexes in this database
>>> Is this the sum of pg_stat_all_indexes.idx_tup_read? This is accounted to
>>> pg_stat_database.tup_returned.
>>
>> I was thinking that pg_stat_database.tup_fetched is the same as
>> the sum of pg_stat_all_tables.idx_tup_fetch. Because they both
>> are incremented by bitmap index scans, but pg_stat_all_indexes.idx_tup_read
>> is not.
>
> Yes. So, "Number of index entries returned by scans on indexes in this
> database" is incorrect, and "Number of live rows fetched by index scans in
> this database" is correct?
Yes, I think so!
Regards,
--
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: more detailed description of tup_returned and tup_fetched
@ 2021-05-20 08:38 Masahiro Ikeda <ikedamsh@oss.nttdata.com>
parent: Fujii Masao <masao.fujii@oss.nttdata.com>
0 siblings, 1 reply; 15+ messages in thread
From: Masahiro Ikeda @ 2021-05-20 08:38 UTC (permalink / raw)
To: Fujii Masao <masao.fujii@oss.nttdata.com>; pgsql-docs@lists.postgresql.org
On 2021/05/20 17:00, Fujii Masao wrote:
> On 2021/05/20 9:46, Masahiro Ikeda wrote:
>> On 2021/05/18 20:10, Fujii Masao wrote:
>>>>> pg_stat_database.tup_fetched:
>>>>> Number of index entries returned by scans on indexes in this database
>>>> Is this the sum of pg_stat_all_indexes.idx_tup_read? This is accounted to
>>>> pg_stat_database.tup_returned.
>>>
>>> I was thinking that pg_stat_database.tup_fetched is the same as
>>> the sum of pg_stat_all_tables.idx_tup_fetch. Because they both
>>> are incremented by bitmap index scans, but pg_stat_all_indexes.idx_tup_read
>>> is not.
>>
>> Yes. So, "Number of index entries returned by scans on indexes in this
>> database" is incorrect, and "Number of live rows fetched by index scans in
>> this database" is correct?
>
> Yes, I think so!
Thanks!
I updated the patch for summarizing this thread.
Regards,
--
Masahiro Ikeda
NTT DATA CORPORATION
Attachments:
[text/x-patch] v1-0002-add-more-detailed-description-of-tup_returned-and-tup_fetched.patch (955B, ../../e50ffb9e-153f-cb1a-3e59-7edee60941cc@oss.nttdata.com/2-v1-0002-add-more-detailed-description-of-tup_returned-and-tup_fetched.patch)
download | inline diff:
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index dcbb10fb6f..09a22a43ac 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -3712,7 +3712,7 @@ SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event i
<structfield>tup_returned</structfield> <type>bigint</type>
</para>
<para>
- Number of rows returned by queries in this database
+ Number of live rows fetched by sequential scans and index entries returned by index scans in this database
</para></entry>
</row>
@@ -3721,7 +3721,7 @@ SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event i
<structfield>tup_fetched</structfield> <type>bigint</type>
</para>
<para>
- Number of rows fetched by queries in this database
+ Number of live rows fetched by index scans in this database
</para></entry>
</row>
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: more detailed description of tup_returned and tup_fetched
@ 2021-05-21 13:26 Fujii Masao <masao.fujii@oss.nttdata.com>
parent: Masahiro Ikeda <ikedamsh@oss.nttdata.com>
0 siblings, 1 reply; 15+ messages in thread
From: Fujii Masao @ 2021-05-21 13:26 UTC (permalink / raw)
To: Masahiro Ikeda <ikedamsh@oss.nttdata.com>; pgsql-docs@lists.postgresql.org
On 2021/05/20 17:38, Masahiro Ikeda wrote:
>
>
> On 2021/05/20 17:00, Fujii Masao wrote:
>> On 2021/05/20 9:46, Masahiro Ikeda wrote:
>>> On 2021/05/18 20:10, Fujii Masao wrote:
>>>>>> pg_stat_database.tup_fetched:
>>>>>> Number of index entries returned by scans on indexes in this database
>>>>> Is this the sum of pg_stat_all_indexes.idx_tup_read? This is accounted to
>>>>> pg_stat_database.tup_returned.
>>>>
>>>> I was thinking that pg_stat_database.tup_fetched is the same as
>>>> the sum of pg_stat_all_tables.idx_tup_fetch. Because they both
>>>> are incremented by bitmap index scans, but pg_stat_all_indexes.idx_tup_read
>>>> is not.
>>>
>>> Yes. So, "Number of index entries returned by scans on indexes in this
>>> database" is incorrect, and "Number of live rows fetched by index scans in
>>> this database" is correct?
>>
>> Yes, I think so!
>
> Thanks!
> I updated the patch for summarizing this thread.
Thanks for updating the patch! LGTM.
This is an improvement of documentation, so this should be applied in
v15 dev cycle? If so, could you add the patch to the next CF? Or you think
this is a bug fix and needs to be back-patched?
Regards,
--
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: more detailed description of tup_returned and tup_fetched
@ 2021-05-24 02:37 Masahiro Ikeda <ikedamsh@oss.nttdata.com>
parent: Fujii Masao <masao.fujii@oss.nttdata.com>
0 siblings, 0 replies; 15+ messages in thread
From: Masahiro Ikeda @ 2021-05-24 02:37 UTC (permalink / raw)
To: Fujii Masao <masao.fujii@oss.nttdata.com>; pgsql-docs@lists.postgresql.org
On 2021/05/21 22:26, Fujii Masao wrote:
>
>
> On 2021/05/20 17:38, Masahiro Ikeda wrote:
>>
>>
>> On 2021/05/20 17:00, Fujii Masao wrote:
>>> On 2021/05/20 9:46, Masahiro Ikeda wrote:
>>>> On 2021/05/18 20:10, Fujii Masao wrote:
>>>>>>> pg_stat_database.tup_fetched:
>>>>>>> Number of index entries returned by scans on indexes in this database
>>>>>> Is this the sum of pg_stat_all_indexes.idx_tup_read? This is accounted to
>>>>>> pg_stat_database.tup_returned.
>>>>>
>>>>> I was thinking that pg_stat_database.tup_fetched is the same as
>>>>> the sum of pg_stat_all_tables.idx_tup_fetch. Because they both
>>>>> are incremented by bitmap index scans, but pg_stat_all_indexes.idx_tup_read
>>>>> is not.
>>>>
>>>> Yes. So, "Number of index entries returned by scans on indexes in this
>>>> database" is incorrect, and "Number of live rows fetched by index scans in
>>>> this database" is correct?
>>>
>>> Yes, I think so!
>>
>> Thanks!
>> I updated the patch for summarizing this thread.
>
> Thanks for updating the patch! LGTM.
>
> This is an improvement of documentation, so this should be applied in
> v15 dev cycle? If so, could you add the patch to the next CF? Or you think
> this is a bug fix and needs to be back-patched?
Thanks for checking the patch!
I thought this patch will be applied in V15 dev cycle.
OK. I added the patch to the next CF.
https://commitfest.postgresql.org/33/3130/
Regards,
--
Masahiro Ikeda
NTT DATA CORPORATION
^ permalink raw reply [nested|flat] 15+ messages in thread
* Clarification on the column order of UNION, INTERSECT, and EXCEPT
@ 2025-07-23 16:29 PG Doc comments form <noreply@postgresql.org>
0 siblings, 1 reply; 15+ messages in thread
From: PG Doc comments form @ 2025-07-23 16:29 UTC (permalink / raw)
To: pgsql-docs@lists.postgresql.org; +Cc: ruud@chorus.one
The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/17/queries-union.html
Description:
When combining queries with UNION, INTERSECT, or EXCEPT, columns are matched
in the order in which they occur, and not by column name. This can be
unexpected, and I think it would be helpful to call it out explicitly in the
documentation.
Consider the following query:
create table widgets (x text, y text);
insert into widgets (x, y) values ('x', 'y');
(select x, y from widgets) union all (select y, x from widgets);
On Postges 17.5, this returns:
x y
─ ─
x y
y x
Note that it returns a value 'y' for column 'x', despite column x only
containing the value 'x'. This result makes sense if you think of rows as
anonymous tuples (ordered lists of values), but it can be surprising when
you expect rows to behave like structs/dictionaries with named fields.
This behavior can be especially tricky when columns x and y have the same
data type, such that confusing them does not trigger an error. In particular
when the values are similar (e.g. numeric data in the same range), it may
not be obvious from the result that data from different columns got mixed
up.
I read the documentation at
- https://www.postgresql.org/docs/17/queries-union.html
- https://www.postgresql.org/docs/17/typeconv-union-case.html
- https://www.postgresql.org/docs/17/sql-select.html#SQL-UNION
These pages mention that the operands need to be compatible, e.g. ‘In order
to calculate the union, intersection, or difference of two queries, the two
queries must be “union compatible”, which means that they return the same
number of columns and the corresponding columns have compatible data types,
as described in Section 10.5.’ and ‘operands of the UNION must produce the
same number of columns, and corresponding columns must be of compatible data
types’. It does not explicitly state that “corresponding columns” means
“corresponding by index”, and not “corresponding by name”. I think it would
be helpful to clarify that, and maybe even warn about it.
Kind regards,
Ruud
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Clarification on the column order of UNION, INTERSECT, and EXCEPT
@ 2025-07-25 02:55 David G. Johnston <david.g.johnston@gmail.com>
parent: PG Doc comments form <noreply@postgresql.org>
0 siblings, 0 replies; 15+ messages in thread
From: David G. Johnston @ 2025-07-25 02:55 UTC (permalink / raw)
To: ruud@chorus.one, PostgreSQL Documentation <pgsql-docs@lists.postgresql.org>
On Thu, Jul 24, 2025, 09:22 PG Doc comments form <noreply@postgresql.org>
wrote:
> The following documentation comment has been logged on the website:
>
> Page: https://www.postgresql.org/docs/17/queries-union.html
> Description:
>
> When combining queries with UNION, INTERSECT, or EXCEPT, columns are
> matched
> in the order in which they occur, and not by column name. This can be
> unexpected, and I think it would be helpful to call it out explicitly in
> the
> documentation.
The docs never imply that matching by name is a thing in SQL generally.
People make assumptions all the time and we generally don't try and write
to anticipate and disabuse people of random assumptions. We instead
document how things work. We don't document here that all column names in
both queries must be identical which is what your assumption would require.
The existing wording strongly implies that corresponding matches are done
by index. Then concretely an example on that page unions columns, A and X,
this clearly showing that names are immaterial and, like the requirements
state, data type matching columns at each position are paired.
I could maybe see adding "position" somewhere but calling it out with a
warning seems unnecessary. Even if one gets confused it is immediately
obvious when checking the result that there is an issue.
David J.
^ permalink raw reply [nested|flat] 15+ messages in thread
end of thread, other threads:[~2025-07-25 02:55 UTC | newest]
Thread overview: 15+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-05-14 08:00 more detailed description of tup_returned and tup_fetched Masahiro Ikeda <ikedamsh@oss.nttdata.com>
2021-05-17 06:32 ` Fujii Masao <masao.fujii@oss.nttdata.com>
2021-05-17 09:58 ` Masahiro Ikeda <ikedamsh@oss.nttdata.com>
2021-05-17 11:46 ` Fujii Masao <masao.fujii@oss.nttdata.com>
2021-05-18 04:20 ` Masahiro Ikeda <ikedamsh@oss.nttdata.com>
2021-05-18 07:01 ` Fujii Masao <masao.fujii@oss.nttdata.com>
2021-05-18 09:23 ` Masahiro Ikeda <ikedamsh@oss.nttdata.com>
2021-05-18 11:10 ` Fujii Masao <masao.fujii@oss.nttdata.com>
2021-05-20 00:46 ` Masahiro Ikeda <ikedamsh@oss.nttdata.com>
2021-05-20 08:00 ` Fujii Masao <masao.fujii@oss.nttdata.com>
2021-05-20 08:38 ` Masahiro Ikeda <ikedamsh@oss.nttdata.com>
2021-05-21 13:26 ` Fujii Masao <masao.fujii@oss.nttdata.com>
2021-05-24 02:37 ` Masahiro Ikeda <ikedamsh@oss.nttdata.com>
2025-07-23 16:29 Clarification on the column order of UNION, INTERSECT, and EXCEPT PG Doc comments form <noreply@postgresql.org>
2025-07-25 02:55 ` Re: Clarification on the column order of UNION, INTERSECT, and EXCEPT David G. Johnston <david.g.johnston@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox