2

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 I/O Timings: read=1978.454 write=0.205 -> 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 I/O Timings: read=1978.454 write=0.205 -> 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 I/O Timings: read=1977.930 write=0.205 -> 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 I/O Timings: read=1977.930 write=0.205 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) Buffers: shared hit=6902688 read=372054 dirtied=32 I/O Timings: read=3067.841 -> 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 Buffers: shared hit=6902688 read=372054 dirtied=32 I/O Timings: read=3067.841 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?

Improve this question
5
  • Amd how much times takes select count(*) from table t;? Commented Dec 6, 2018 at 13:24
  • @Akina for select count(*) result is : Planning time: 0.964 ms Execution time: 10891.418 ms Commented Dec 6, 2018 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... Commented Dec 6, 2018 at 13:59
  • Table definition might also help shed some light on this. Commented Dec 6, 2018 at 14:03
  • 1
    @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 Commented Dec 6, 2018 at 14:25

1 Answer 1

Reset to default
1

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:

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.