"select count from %s" % tableName" is really slow with a billion row.
But it appears that's not really necessary to do it every single tables.

However it's required for reply_raw, and reply_hdr, in that case, i'm just taking the last row_id, and it seems ok.

https://github.com/Subaru-PFS/ics_archiver/commit/ea01a7421cba16da3054e7ac3d0d3316191ca266

I'll merge later, if it's fine.

Or if it is int/bigint type, you could use MAX() instead of??

1. In pgsql, count could be quite slow if index only scan is not enabled, due to MVCC... (I don't have clear idea that MAX() can solve this,,, sorry.)

I agree with using MAX():

archiver=# explain select count(id) from reply_raw;
                                 QUERY PLAN
----------------------------------------------------------------------------
 Aggregate  (cost=1695406.31..1695406.32 rows=1 width=8)
   ->  Seq Scan on reply_raw  (cost=0.00..1533464.05 rows=64776905 width=8)
(2 rows)

versus:

archiver=# explain select max(id) from reply_raw;
                                                       QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
 Result  (cost=0.61..0.62 rows=1 width=0)
   InitPlan 1 (returns $0)
     ->  Limit  (cost=0.56..0.61 rows=1 width=8)
           ->  Index Only Scan Backward using reply_raw_pkey on reply_raw  (cost=0.56..2732366.40 rows=64776905 width=8)
                 Index Cond: (id IS NOT NULL)
(5 rows)

Note that we should perhaps be using postgresql's `BIGSERIAL` type for the ids. Probably does not matter for single-writer tables. But it does make the monotonically increasing integer constraint explicit.

Due to MVCC used by pgsql, you need to use some trick or configuration to get rid from seq scan into index only scan for count(xxx). For just getting max number from auto_increment-ish column, the easiest way in pgsql is max()...

Yes, I should have used max(), what I did is equivalent but not very clean.

arnaud.lefur close this?

This has been fixed for a year.