Postgresql – Understand PostgreSQL’s statistics about rows and times

postgresqlstatistics

I'm trying to find out how much database load an aplication causes. Therefore I'm looking at the statistics of a single database (there's only a single user working with it). The numbers I get are partially redundant and massively different. Here's the query I use to get the data:

with pss as (
    select pd.datname,
        sum(calls) as query_count,   -- Number of executed queries   
        sum(total_time)::integer as time,   -- Total time spent for queries, in milliseconds
        sum(rows) as rows,   -- Number of rows retrieved or affected by queries
        sum(blk_read_time)::integer as read_time,   -- Total time queries spent reading blocks, in milliseconds
        sum(blk_write_time)::integer as write_time   -- Total time queries spent writing blocks, in milliseconds
    from pg_stat_statements as pss
        left join pg_database as pd on (pd.oid = pss.dbid)
    group by pd.datname
)
select psd.numbackends,   -- Currently connected clients
    pss.query_count,
    psd.xact_commit + psd.xact_rollback as xact_count,   -- Transaction count
    psd.blks_read,   -- Disk blocks read
    psd.blks_hit,   -- Disk block found in buffer cache
    pss.rows,
    psd.tup_returned,   -- Rows returned by queries
    psd.tup_fetched,   -- Rows fetched by queries
    psd.tup_inserted + psd.tup_updated + psd.tup_deleted as tup_written,   -- Rows written by queries
    psd.blk_read_time::integer,   -- Time spent reading data file blocks by backends in this database, in milliseconds
    psd.blk_write_time::integer,   -- Time spent writing data file blocks by backends in this database, in milliseconds
    pss.time,
    pss.read_time,
    pss.write_time
from pg_stat_database as psd
    join pss on (pss.datname = psd.datname) 
where psd.datname = 'myappdb';

I have loaded and installed the pg_stat_statements extension and enabled the track_io_timing option. I've also reset both statistics at the same time a while ago as a superuser in this database:

select pg_stat_reset(), pg_stat_statements_reset();

The result currently looks like this:

numbackends:            28
query_count:       270,302
xact_count:        270,313
blks_read:      17,666,658
blks_hit:        5,063,072
rows:              462,105
tup_returned:   24,494,192
tup_fetched: 1,131,085,600
tup_written:        17,509
blk_read_time:      39,337
blk_write_time:      9,257
time:              229,049
read_time:          50,303
write_time:          9,261

My questions are these:

Why are the numbers in rows, tup_returned and tup_fetched so different? At least rows and tup_returned should be the same, right?
Why are blk_read_time and read_time a bit off while blk_write_time and write_time are so close?

Best Answer

The "rows" field only reports rows returned by the query in question. The pg_stat_database fields also count rows that had to be fetched from system catalogs in order to plan and execute your queries. pg_stat_statements doesn't track these, not even with pg_stat_statements.track=all. Since the catalog data is generally cached within a session, this can give you massive expansion in the number in pg_stat_database relative to pg_stat_statements if you only execute one small query in each session and then disconnect. These catalog reads can also explain the difference in read times.

There is more to the story than that, like whether they count input rows or output rows for aggregations and joins. Frankly I find tup_returned and tup_fetched to be useless.

What problem are you trying to solve?

Related Solutions

Sql-server – Estimated vs. Actual rows and multi-column statistics

You've actually got a few questions in here, so I'll break 'em out individually.

My problem/question is that the estimated number of rows for the above query is 256, the actual number of rows is 560K. I want to understand why there is such a big difference between these two numbers?

In order to answer that question, the first thing we would need is the actual execution plan for the query. In SQL Server Management Studio, you can get that by clicking Query, Include Actual Execution Plan. Run the query, click on the Execution Plan tab, and right-click anywhere in the whitespace to click Save Plan As. Save that, and post it somewhere for people to download and examine.

The next thing we would need is the output from DBCC SHOW_STATISTICS for the stats on that table. You've hinted at the output, and that's a good start, but the raw output will help us understand exactly what's going on.

If I run a DBCC SHOW_Statistics, the density section has both columns in it, the histogram does not. Does SQL server produce a histogram for the combination of columns in multi-column statistics?

No.

I have an index on (TaskExecStatusID,TaskExecUpdatedDate),

If you frequently use the query in the example (with TaskExecUpdateDate IS NULL), then you might check out filtered indexes. They're a new feature in SQL Server 2008 that allows you to put a where clause on your index, basically.

http://sqlfool.com/2009/04/filtered-indexes-what-you-need-to-know/

Sql-server – If a query triggers a statistics update and times out are the statistics still updated

That was actually my question at AskSSC. I should have just tested it myself as I accepted an incorrect answer.

With the following test table

CREATE TABLE StatsTest
(
a varchar(max),
b varchar(max)
)

DECLARE @VCM VARCHAR(MAX) = 'A'

INSERT INTO StatsTest
SELECT TOP 20000
       REPLICATE(@VCM,10000),
       REPLICATE(@VCM,10000)
FROM master..spt_values v1,  master..spt_values v2

And the following test code

         SqlConnection connection = new SqlConnection(...);
            connection.Open();

            SqlCommand command = connection.CreateCommand();
            command.CommandTimeout = 12;
            command.CommandType = CommandType.Text;
            command.CommandText = @"SELECT COUNT(*)
FROM StatsTest
WHERE 
      a LIKE '%foo%' OR 
      b LIKE '%foo%' ";
            command.ExecuteScalar();

Profiler shows the following

Profiler screenshot

First it creates the stats for column b successfully (the initial SP:StmtStarting /SP:StmtCompleted pair) . Then it starts creating the stats for column a (The selected SP:StmtStarting entry in the screen shot). This entry is followed by an AUTOSTATS event confirming that the statistics on b were created then the timeout kicks in.

It can be seen that the stats creation occurs on the same spid as the query and so this also aborts the creation of stats on column a. At the end of the process only one set of stats exists on the table.

Edit

The above refers to stats creation, to test auto update of the stats I ran the above query without a timeout so both sets of stats were successfully created then updated all columns of all rows so that the stats would be out of date and re-ran the test. The trace for that is pretty similar

Profiler 2

Finally just for completeness with SET AUTO_UPDATE_STATISTICS_ASYNC ON the trace looks as follows. It can be seen that system spids are used to perform the operation and they are unaffected by the query timeout as would be expected.

Profiler 3

Best Answer

Related Solutions

Sql-server – Estimated vs. Actual rows and multi-column statistics

Sql-server – If a query triggers a statistics update and times out are the statistics still updated

Related Question