![]() If you use the EXPLAIN command to analyze a query, you will see that the query planner always reports a row count. Fortunately, there is a similar estimation strategy that can be employed with any query. However, it is useless if the actual need is to estimate the size of the return set of an arbitrary query. That last technique is great for very quickly generating a reliable estimate of the row size of an entire table. This is actually what the PostgreSQL query planner actually does to produce accurate estimates of row counts. It produces a surprisingly accurate estimate of the table size, in rows. It is a lot of words, but this is also very fast to write and to run as a query: If we assume that the estimated number of rows in pg_class, divided by the estimated number of pages (blocks) in pg_class provides a relatively accurate number of rows per page of storage, and we then multiply that by the actual number of pages that are in use, as calculated by dividing the actual bytes in use by the block size, the number should be more accurate than the the planner's estimate. ![]() It is also possible to query the current size of the table on disk, in bytes, separately, as well as to query the size in bytes of each block. The query planner keeps an estimate of both the number of rows in a table (the reltuples field of pg_class), and of the size, in pages, of the table (the relpages field of pg_class). It is possible to query a more accurate estimate through the use of a little math. It will rarely be highly accurate, however. If a ballpark number is sufficient, then this might be all that you need, and it has the benefit of being very fast to query. The basic template for doing a count is this: There are, unfortunately, only a few couple approaches to improving count performance with PostgreSQL. This performance can be problematic if there are legitimate in-app needs to know how many rows are in a table, or to know how many rows a query would return, without actually running the whole query. The count(1) variation has to check, for each row, to validate that 1 is still not null, and that adds a small, but measurable overhead, particularly on very large tables. The reason for this is that PostgreSQL has optimized count(*) as a special case, and it actually treats it as having no arguments at all. With PostgreSQL, this is incorrect, and in fact, the count(1) version is slightly slower, on average. That's a long time to wait to get a row count.Īs a side note, you may have seen a suggestion that it's faster to do a count(1) than a count(*), because of the assumption that the * requires the database to access the whole row. 838 ms JIT : Functions : 11 Options : Inlining false, Optimization false, Expressions true, Deforming true Timing : Generation 16. 965 rows = 1 loops = 3 ) -> Parallel Seq Scan on telemetries ( cost = 0. 251 rows = 3 loops = 1 ) Workers Planned : 2 Workers Launched : 2 -> Partial Aggregate ( cost = 315766. QUERY PLAN - Finalize Aggregate ( cost = 316766. Here is what that query plan looks like for a database with about 4.6 million rows: When one does a count, the database engine has to sequentially scan the table to determine how many rows are in the current transaction's view of reality for that table. Thus, there is no one single count of rows in the database table like there is with a MySQL/MyISAM table. Without going deeply into that, basically what it means is that each transaction that is interacting with the database sees its own version of reality, relative to the database contents. The fundamental difference between MySQL/MyISAM and PostgreSQL, with regard to counting, is that PostgreSQL uses something called Multiversion Concurrency Control. If the table was large, the query may have been despair-inducingly slow, even. The query probably did not return quickly. If you have ever done the same thing with a large PostgreSQL table, though, your experience was probably different. The MyISAM engine maintains a count of all rows in a table as part of the low level information for that table, so counts in MySQL/MyISAM, are very fast. You will have seen that the count returns immediately. Enter fullscreen mode Exit fullscreen mode
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |