# How can it be possible that sum operation is so much faster than count?

When I make a simple benchmark test in a table that has 11.065.763 records. I get the results below:

``````select sum(t.amount * t.exchange) from table t;

Finalize Aggregate  (cost=658027.39..658027.40 rows=1 width=8) (actual time=2391.248..2391.248 rows=1 loops=1)
Buffers: shared hit=25366 read=550786 dirtied=18 written=18
->  Gather  (cost=658027.17..658027.38 rows=2 width=8) (actual time=2391.100..2391.229 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=25366 read=550786 dirtied=18 written=18
->  Partial Aggregate  (cost=657027.17..657027.18 rows=1 width=8) (actual time=2377.613..2377.613 rows=1 loops=3)
Buffers: shared hit=24961 read=550759 dirtied=18 written=18
->  Parallel Seq Scan on odeme_kaydi ok  (cost=0.00..622181.24 rows=4646124 width=16) (actual time=0.084..1972.061 rows=3688816 loops=3)
Buffers: shared hit=24961 read=550759 dirtied=18 written=18
Planning time: 0.279 ms
Execution time: 2408.745 ms

select count(t.id) from table t;

Aggregate  (cost=489270.14..489270.15 rows=1 width=8) (actual time=12256.560..12256.560 rows=1 loops=1)
->  Index Only Scan using pk_odeme_kaydi_id on odeme_kaydi ok  (cost=0.43..461393.25 rows=11150756 width=8) (actual time=0.169..11583.174 rows=11066478 loops=1)
Heap Fetches: 4085161
Planning time: 0.110 ms
Execution time: 12256.609 ms
``````

Note: amount field is always greater than 0 in the table. Postgresql version is 9.5.

Is it normal or is there anything tricky?

• Amd how much times takes `select count(*) from table t;`? – Akina Dec 6 '18 at 13:24
• @Akina for select count(*) result is : Planning time: 0.964 ms Execution time: 10891.418 ms – H.Ç.T Dec 6 '18 at 13:31
• It's too strange that counting the whole records count needs some scan instead of take the result from table statistic... and the same about `t.id` which is a primary index I assume... – Akina Dec 6 '18 at 13:59
• Table definition might also help shed some light on this. – Erwin Brandstetter Dec 6 '18 at 14:03
• @Akina: the count has to go through all rows. "table statistics" are only estimates to begin with, but if they weren't the count has to do honor concurrent transactions and thus it has to go through all rows. Everything else wouldn't be an accurate answer – a_horse_with_no_name Dec 6 '18 at 14:25

Typically, there is not much difference between `sum()` and `count()`, the number of data pages that have to be read are the dominant factor for performance, but `count()` is generally faster, especially `count(*)`:

Your sum uses `Parallel Seq Scan`, which turns out to be much faster. The count, on the other hand uses an `Index Only Scan`, which is typically the fastest way, but no Parallel support there. That's what probably makes the difference. (May be a limitation of pg 9.5, not sure.)

You are using Postgres 9.5. Parallel queries were pretty new and the cost estimation not too accurate - similar considerations apply for index-only scans, to a lesser degree. So the planner makes at least one bad cost estimate: `489270.15` for the count with and Index Only Scan, vs. `658027.40` for the sum - for which an index-only scan is probably not possible (no index covering `amount` and `exchange`). Maybe you also have cost settings that don't reflect reality well, which is typically involved in bad estimates. See:

And I see `Heap Fetches: 4085161` for the count, which strikes me as surprisingly high for an index-only scan on 11M rows. Not sure why that is, maybe a `VACUUM ANALYZE` on the table might change things.

All that aside, while `t.id` is defined `NOT NULL`, use `count(*)` instead of `count(t.id)`, which is a bit faster, since Postgres does not have to look into the stored (index) tuple at all. The mere existence of rows is sufficient.

And upgrade to a current version of Postgres, where either variant will be substantially faster. There have been major improvements to big data and parallel queries.

BTW, if the count does not have to be exact, there are much faster options: