30

I'm facing a hard time trying to figure it out some performance problems in my database. I'm using a bunch of resources online to learn what to monitor and how to interpret that information.

From the above, I'm unable to find a clear explanation of what is the difference between pg_stat_database.tup_returned and pg_stat_database.tup_fetched.

In pgAdmin4, there is a beautiful chart called "Tuples out" where these two concepts are contrasted, but I don't know how to interpret the info. In the official documentation only says that:

  • tup_returned: Number of rows returned by queries in this database
  • tup_fetched: Number of rows fetched by queries in this database

What exactly does "fetched" and "returned" mean?

I'm using postgresql 10.

Improve this question
1

1 Answer 1

Reset to default
41

If you do select count(*) from million_row_table, one million row wills be returned, but only one row will be fetched.

I can't see I've ever found these fields useful for diagnosing performance problems. Find your slow query and do an EXPLAIN (ANALYZE, BUFFERS) of it.

Improve this answer
3
  • 3
    Thanks, that makes sense. It's funny that the documentation doesn't explain that clearly. At least I can't find it. Commented Oct 2, 2018 at 5:31
  • 2
    If there's an index for the count, does PG still report millions of rows returned or not? Commented Feb 25, 2020 at 18:34
  • In my experience, its the opposite of this. returned = search for on disk, fetched = returned to user. In our database, our returned is 3 orders of magnitude higher than fetched, presumably because our SQL is doing a lot of full table scans. This post: stackoverflow.com/questions/71272264/… says one is sequential scans and one it bitmap scans. Commented Jan 23 at 17:28

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.