PostgreSQL Tuples – Difference Between Fetched and Returned Tuples

monitoringperformancepostgresql

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.

Best Answer

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.

Related Solutions

Postgresql – Difference between fsync and synchronous_commit – postgresql

When fsync is OFF, the PostgreSQL server will never issue any fsync system call, leaving entirely to the operating system the decision as to which blocks to transfer from write cache to disk, and when. The database engine never knows what has been sync'ed or not. In case of a server crash, it has to be assumed that the database is in an inconsistent state.

When synchronous_commit is OFF, the server issues fsync call, but not necessarily immediately at commit time for each transaction. It may delay them after the commit, for a maximum of wal_writer_delay multiplied by 3. With a default configuration that would be 600ms. This allows to reduce the number of fsync calls, especially for workloads with lots of small transactions. In case of a server crash, the transactions not yet sync'ed in the last 600ms before the crash may be rolled back, but the state of the database is always consistent.

Sql-server – Dealing with large log files

If the database is truly static in nature, putting it into read-only mode would prevent log growth. The queries that are causing the growth would fail however, so this might not actually be what you are looking for.

Put the database into simple recovery mode. That way the transaction logs will be routinely truncated. When in full mode, your logs will continue to grow until they are backed up.

USE [master]
GO
ALTER DATABASE [YourDatabase] SET RECOVERY SIMPLE WITH ROLLBACK IMMEDIATE
GO

Best Answer

Related Solutions

Postgresql – Difference between fsync and synchronous_commit – postgresql

Sql-server – Dealing with large log files

Related Question