adrian_avila_mtz wrote:

> I can't believe it doesn't have such basic feature, I need to make paginated consults of a 3.6 millions table and when I need to count the record to get the number of pages needed It takes to long, the plan is natural and is slow.
>
> Im so dissapointed.
>

Would you be less disappointed if you knew that every transaction is
likely to get a different value for the count because each has a
different and stable view of the state of the database? And that
the individual stable views allow your pagination calculation to
match the actual records you retrieve and format without blocking
other transactions from changing the table?

There are four ways (that I know of) for counting records in a
table. The database can maintain a count which is complicated
and not generally useful - useful to you but not useful for
everyone. The database can count entries in a primary key
index, which doesn't work if the index contains old entries
for records that have been deleted but are still potentially
useful to old transactions, as well as multiple entries for
the same record if its primary key was modified. The database
can read all the records and count the ones that are in your
view, which works, gets the right answer, but is slow.
The fourth is for you to keep a count of the tables you want
counted.

One relatively non-blocking way to maintain a count is to have
a table that has a table_name column and a count column. Put
a trigger on the tables you're interested in so that an insert
stores the table name and +1; a delete stores the table name
and -1. Seed the counting table with the current counts. To
get a count for your transaction sum the counts for the table
you're interested in. Periodically, run a transaction that
adds up all the +1 and -1 entries and updates the master count.

Why couldn't the database do that for you? In the general case,
it would need to keep a count of the committed records in the
table, plus for each transaction it would need to keep the
number of committed records for each table when the transaction
started and add or subtract as the transaction inserts or
deletes, and undo those actions when the transaction undoes
a savepoint ... That's a lot of work for the number of applications
where counting a large table is necessary.


Good luck,

Ann