If you need to quickly get an exact count, one option is to pay the time cost for this data in small pieces, ahead of time, by using triggers and functions to maintain a MySQL/MyISAM-like count that is kept up to date at all times. Sometimes this approach isn’t optimal, though, and other strategies are needed. If you can leverage an index to reduce the total number of rows that you are scanning, then you may be able to get an exact count quickly enough. As the previous example shows, sometimes you can use the EXPLAIN ANALYZE capability of both PostgreSQL and MySQL to help you optimize something so simple as a count. If you need an exact count on a regular basis, you should be doing your query in the most efficient way possible. Then it just has to scan that much smaller set in order to count them. The database engine was able to determine that it could leverage an index to find all of the distinct IDs. It is 10 times faster! The magic is in the following line: Index Only Scan using. This basic query for counting is slow with PostgreSQL and MySQL/InnoDB:
#POSTGRESQL COUNT ROWS IN TABLE HOW TO#
How to count SQL rows faster by leveraging indexes Slow counting is problematic if your app needs to know how many rows are in a table, or how many rows a query would return, without actually running the whole query.Įven though the database engines differ, the solutions to this problem for both PostgreSQL and MySQL/InnoDB are similar. Counting faster with PostgreSQL and MySQL/InnoDB That adds a small but measurable overhead, particularly with very large tables. The count(1) includes an argument and has to check each row to validate that 1 is still not null.
This is because PostgreSQL has optimized count(*) as a special case that includes no arguments. That’s not true for PostgreSQL or MySQL and the count(1) version is slightly slower on average. Count(1) vs Count(*)Īs a side note, you may have heard 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. Consequentially, 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, at that time. Essentially, each unique transaction may have slightly different data from other transactions processed at the same time.īecause the state of every table is unique to each transaction with Multiversion Concurrency Control, there cannot be one single true count of rows in the database table like there is with a MySQL/MyISAM table. This means the database keeps information about old rows to better support concurrency and rollback features. Unlike MySQL/MyISAM, both MySQL/InnoDB and PostgreSQL use Multiversion Concurrency Control for counting. Counting rows in MySQL/InnoDB and PostgreSQL That's a long time to wait to get a row count.
0 kommentar(er)
