Count vs select query performance in PostgreSQL

Question

We have a PosgtreSQL DB table with lots of data. I wonder what type of query is faster / has a better performance and why?

select * from table
select count (*) from table

Two different questions that give you different results. Why do you even care about the difference in time needed to get these different results? — Frank Heikens
– Frank Heikens, Commented Feb 10, 2023 at 10:09
It's possible to use them for the same purpose because clients tend to expose the ROW_COUNT variable. In the first case the app can read the row count associated with the result, in the second it can read the result. If you need both the count and the actual data, the first query can get them both at once. If you only need the row count, the contents of the table would just waste IO, weigh and slow things down, so count(*) is enough. — Zegarek
– Zegarek, Commented Feb 10, 2023 at 10:24
I know what they do, I just need to count at first and later, in some rare cases, use the result data. That's why I wonder if doing count at first and then select is better than doing select at the beggining. If the select has a worse performance, I'll use count — Ula
– Ula, Commented Feb 10, 2023 at 15:06

Tim Biegeleisen · Accepted Answer · 2023-02-10 10:08:41Z

3

While both queries do iterate over the entire table, the first query will perform generally much worse at an application level versus the second one. This is because the select * query will need to send the entire table across the network to the application which is executing the query. The count(*) query, on the other hand, only needs to send across a single integer count.

answered Feb 10, 2023 at 10:08

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

The Impaler Over a year ago

In addition to the network time, query #2 could skip the heap and count entries in the index, something that is much faster.

Collectives™ on Stack Overflow

Count vs select query performance in PostgreSQL

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related