0

I wonder why total runtime of count(*) is smaller than count(primary_key) of the same table?

Query plan (using explain analyze) shows that every time Postgresql is doing Seq Scan instead of using indexes? Why is that happening? and why indexes aren't used in this case?

Edited: count(1) doesn't make any difference.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Borys
  • 2,676
  • 2
  • 24
  • 37

1 Answers1

0

When you use SELECT COUNT(*) or SELECT COUNT(), you are just counting rows in table, ignoring columns data.

When you use SELECT COUNT(primary_key), column primary_key must also be fetched from row and - depending on SQL engine, I think - only non-null values are considered counted.

LS_ᴅᴇᴠ
  • 10,823
  • 1
  • 23
  • 46
  • yes, i know the difference between those 2, but my question was about usage of indexes. why seq scan if indexes could be use here? – Borys Jul 12 '13 at 13:00
  • Indexes are useful for when locating specific rows. You are counting, all of them matters. Indexes speed up lookups. – LS_ᴅᴇᴠ Jul 12 '13 at 13:04
  • I cannot agree with you, look here https://wiki.postgresql.org/wiki/Index-only_scans#Is_.22count.28.2A.29.22_much_faster_now.3F – Borys Jul 12 '13 at 13:13
  • 1
    ok, just found the answer to my question http://wiki.postgresql.org/wiki/Slow_Counting mvcc is the reason. – Borys Jul 12 '13 at 13:23