fast count(*) through statistics collector

Hi hackers,

I'm novice in PostgreSQL codebase (and in English too :-)), but I'd be
glad to make a modest contribution to this great project.

By viewing this list, I see a lot of discussions on the problem of
"fast count (*)", but acceptable decision have not been formulated.
Well, I make bold to propose own view on the problem. It can be
described as: "Improve existing infrastructure of statistics collector
and use its for caching the number of rows in the table."

I plan to do the following steps to implement this principle:

1. Eliminate 500 ms lag between transaction commit and statistics
portion transfer from backend to collector. For this purpose
pgstat_report_tabstat() function must call in "force" mode only. We
pay therefor increased backend<-->collector traffic. As some
compensation could be invited to send TabStatusArray items, which have
not changed with the last shipment. This measure will reduce the size
of a messages.
I see here one more pitfall: new transaction can start after the
changes made earlier transaction became visible for other backends,
but before the statistics collector managed to take and process data
(despite the forced transfer). To avoid this, one may transfer
information before the changes made transaction will be visible,
collector, in one's turn, apply this info after that.
It is also possible that the use of shared memory instead of pipes
will help increase productivity.

2. Eliminate 500 ms lag between recieve statistics portion and write
pgstat.stat file. Realize the next todo item for this purpose:
"Allow statistics collector information to be pulled from the
collector process directly, rather than requiring the collector to
write a filesystem file twice a second".
As an additional effect, we will be able to reduce the burden on
I/O channels.

3. Field n_live_tuples of PgStat_StatTabEntry structure now holds the
number of inserted - deleted tuples for successful transactions, which
are known to collector. But we need field, which would contain the
number of inserted - deleted tuples for ALL successful transactions in
the history of the table, or it would be undefined (e.g. -1). If
n_live_tuples not suited for this role, creating additional field. In
any case, I will call this field "live_tuples counter" below.

4. Values in the live_tuples counters be questioned, if there was any
interruption of statistics collection. Therefore, if trac_counts was
set to false in cluster-wide or the collector process crash, then
live_tuples become undefined for all tables in the cluster. If
pg_stat_reset() call, then live_tuples become undefined for all tables
in DB. If pg_stat_clear_snapshot() call, or trac_counts set to false
during user session, then live_tuples counters should undefine for all
tables covered during this transaction/session. If compile such a list
of tables is not possible, well, for all tables in DB.

5. If live_tuples counter contain undefined value, but statistics
collector work normal, the counter must be restored through first
seqscan.

I hope that these steps will give us mvcc-compliant counters and
overhead cost will increase little.

The next step is relatively simple:

6. In the optimizer/plan/planagg.c file add a function similar to
optimize_minmax_aggregates () that return null for undefined
tuples_count counters (and count(*) determine by regular way through
seqscan) or plan for computation such as:

PgStat_StatTabEntry.live_tuples +
PgStat_TableCounts.t_new_lived_tuples +
PgStat_TableXactStatus.tuples_inserted -
PgStat_TableXactStatus.tuples_deleted

Restrictions:
1. Uninterrupted supply of statistics collector necessary for
efficient use of this algorithm.
2. Works only for simplest queries like:
select count (*) from regular_table

Any comments are welcome.

--
regards, Artem Yazkov mailto:markkrass(at)kemcity(dot)ru