0

I have a moderately large PostgreSql table (~20M rows) with one index, receiving a small amount of traffic (1-2 inserts/sec). When I try:

SELECT Count(*) FROM MyTable

The query never returns. With an index on the table, shouldn't it be fulfillable from stats?

What could be the problem, and/or how can I diagnose what's going on? Am running 9.6.15.

Endymio
  • 35
  • 6
  • 2
    Unrelated to your problem, but: Postgres 9.6 is [no longer supported](https://www.postgresql.org/support/versioning/) you should plan an upgrade as soon as possible. –  May 04 '22 at 10:06
  • It certainly feels like Postgres is doing the actual count query. Check the duplicate link for some good workarounds. – Tim Biegeleisen May 04 '22 at 10:07
  • @TimBiegeleisen: it doesn't "feel" like it. The query requires to scan the entire table to get an accurate count. An index won't really help there. But counting 20 million rows should only take a few seconds at most on modern hardware. –  May 04 '22 at 10:08
  • I would *at least* add a semicolon to the query. – wildplasser May 04 '22 at 10:09
  • "shouldn't it be fulfillable from stats" No, count means "count", not "guess". You can derive an estimate from the stats if you want, but count won't do that for you (other than as part of the planning) – jjanes May 04 '22 at 13:17
  • Thanks for the help, all. Using the estimation method, I got a reasonable count. Still unsure why it refuses to finish a count, no matter how long I give it. Oh, and to whomever marked this a duplicate question -- it isn't. The question wasn't how to estimate the # of rows, but rather how to diagnose why the count wouldn't complete. – Endymio May 04 '22 at 22:51

0 Answers0