Seems like there is an issue with reltuples showing twice the number of rows

From: Ranjith Ramachandra <ranjith(at)mammoth(dot)io>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Seems like there is an issue with reltuples showing twice the number of rows
Date: 2018-04-03 09:14:27
Message-ID: CAFWmNu8SfSgBWcMCaWJfDLbcFUN3riC9jDuzOd08QsJgAqv+4A@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I am relying on reltuples on my web app to get fast row counts.

This was recommended by this article to get fast approx row counts:
https://wiki.postgresql.org/wiki/Count_estimate

However for some table I am getting twice as many values when I try to do
this. I did some more research and came up with this query.

select reltuples, n_live_tup, n_dead_tup
from pg_stat_user_tables join pg_class using (relname)
where relname =
'main_csv_0f247511f5c247c8813ba3cec90c6ae1_yf34fbb38d';

it returns

reltuples | n_live_tup | n_dead_tup
-------------+------------+------------
2.7209e+06 | 1360448 | 1360448

If I run analyze main_csv_0f247511f5c247c8813ba3cec90c6ae1_yf34fbb38d

and I run the same query again,

reltuples | n_live_tup | n_dead_tup
-------------+------------+------------
1.36045e+06 | 1360448 | 1360448

But after some time the value goes back to being double the value. This is
causing me a lot of problems since this inaccuracy does not make any sense
to me.

Any help would be appreciated.

FYI, also asked the same question on stackoverflow since I am new to
postgres mail lists.

https://stackoverflow.com/questions/49625259/postgres-reltuples-seems-to-return-twice-the-number-of-values

--
~Ranjith

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Laurenz Albe 2018-04-03 09:28:11 Re: dblink: could not send query: another command is already in progress
Previous Message Achilleas Mantzios 2018-04-03 08:39:12 Re: Autovacuum Problem