An interesting postgres oddity I ran into a few years ago that isn't about counting but is in a similar arena is how under very specific circumstances, "SELECT *" is significantly faster than "SELECT one_column" despite the conventional and normally well advised advice that you should only select the columns you need.

See https://www.postgresql.org/message-id/CAN1FPGN1ynBj3m1DMszc9... and Tom Lane's followup for details.