Counting the number of records found in PostgreSQL

At work in a new project, the PostgreSQL DBMS is used. Since I have been working with MySQL so far, now I have to study and discover Postgri. The first problem that interested me was the replacement of the Muscle SQL_CALC_FOUND_ROWS . When using this constant in MySQL, you can get the number of all records found by a query, even if a query with limit is indispensable for paginating search results when “heavy” queries are used.
An immediate solution could not be found. The forums simply stated that there is no SQL_CALC_FOUND_ROWS in Postgri. Some wrote that it is necessary to use count (*). And no more information. But even from MySQL, I knew that searching with count () - query works almost 2 times slower than with SQL_CALC_FOUND_ROWS. I consulted with those who use PostgreSQL, tormented google for a day and as a result I got 4 options for replacing SQL_CALC_FOUND_ROWS in PostgreSQL, one of which is quite acceptable in speed.

So, I’ll immediately present those four options that will be discussed. Our target query looks for records in the table that contain adf text in the `text` field. We select id of 20 records starting from 180,000 in order and the total number of found.
Option 1. Taken from phpPgAdmin. I just looked into the code of this client for PostgreSQL and looked at how the calculation was done with them when viewing the table data. 2 queries are used with subqueries. The convenience is that you do not need to parse and change the original request in order to count the number of records found by it.

select count (id) from (select id from testing where text like '% adf%') as sub;
select * from (select id from testing where text like '% adf%') as sub limit 20 offset 180000

Option 2 . The easiest option, which is usually used by beginners both in MySQL and in Postgres and other DBMSs. 2 requests.

select count (id) from testing where text like '% adf%';
select id from testing where text like '% adf%' limit 20 offset 180000

Option 3 . max_posedon . This is an attempt to emulate the muscular SQL_CALC_FOUND_ROWS in Postgres by logic. The truth only works when sorting by id (in this case). Here, the id of the last record in the selection is substituted, i.e. records numbered 180,000 + 20.

select id from testing where text like '% adf%' limit 20 offset 180000;
select count (id) from testing where text like '% adf%' and id> 132629;

Option 4 . On the advice of users of irc.freenode.org, again max_posedon , and this answer on the PostgreSQL forum, which was hidden deep in Google. The cursor is used.

DECLARE curs CURSOR FOR select id from testing where text like '% adf%';
MOVE FORWARD 180,000 IN curs;
FETCH 20 FROM curs;
MOVE FORWARD ALL IN curs;

+ function PQcmdTuples () Postgres API (or $ count = pg_cmdtuples ($ result); in PHP).
Please note that all 4 query options should be executed in one transaction, then they work faster. The 4th option will not work at all if you do not use one transaction: the cursor is lost.
Now about the speeds . I tested the speed of these four options. In general, the tests confirmed the expectations. But I note an important fact. All queries were run on the default PostgreSQL configuration, which is not performance optimized. I just did not have an optimized server at hand. So the numbers can be slightly adjusted at startup with a “good” config. However, the essence will not change.
Test runs were carried out in PHP for 20 repetitions 2 times for each option. A php script is available that runs the tests. Who cares, there are full statistics of samples in Excel. Here I will publish only a summary table:

	Var 1	Var 2	Var 3	Var 4
Wed time (ms)	647.41	648.25	450.64	370.67
Attitude to Var 4	1.75	1.75	1.22	-

For comparison, query time without using a transaction:

• Var 1: 1204 ms,
• Var 2: 689 ms,
• Var 3: 560 ms,
• Var 4 only works within a transaction.

The results . The fastest option 4 using the cursor. Its speed is due to the fact that the “heavy” search query is executed only once. Next, operations with the cursor are performed. SQL_CALC_FOUND_ROWS in MySQL works similarly. Option 3 lags behind it by 20% - an attempt to emulate the operation of SQL_CALC_FOUND_ROWS in PostgreSQL. Options 1 and 2 work at approximately the same speed and are 75% faster (more than 2/3!) Inferior in speed to the query with the cursor.
PS for the pg guru. Firstly, if this information seems obvious to you, then believe me - for beginners, PostgreSQL is not at all obvious, and finding this information is not so simple. Secondly, I am waiting for comments from you about tests on a postgri configuration tuned for performance or about your experience, and about other calculation options.