Re: Optimizing count(), but Explain estimates wildly off

[Chema <chema@xxxxxxxxxxxxx>]

Yours will be different, as I cannot exactly duplicate your schema or data distribution, but give "SELECT 1" a try. This was on Postgres 16, FWIW, with a default_statistics_target of 100.

Select 1 produces a sequential scan, like Select * did before Vacuum Full.  But if I force an index scan with the subquery hack, there's a significant improvement over Select *.  Row estimate is still -50%|200%, so seems it's only accurate for very simple queries indeed.  In conclusion, I'll just keep on count(*)ing with the subquery hack.  Funny thing, Select 1 is slightly faster than Select count(*), so I'm tempted to do Select count(*) From (Select 1...) As iluffsubqueries. xD

(pg_roaringbitmap looks great, but I expect it works with fixed categories, while I have several full text search columns)

-- With previous country,date index
      query x 100       |        avg         |        min        |        q1         |       median       |        q3         |        max
------------------------+--------------------+-------------------+-------------------+--------------------+-------------------+--------------------
 Count Colombia         | 9093.918731212616  | 6334.060907363892 | 7366.191983222961 | 9154.448866844177 | 10276.342272758484 | 13520.153999328613
 Subquery Colombia      | 7926.021897792816  | 5926.224946975708 | 7000.077307224274 | 7531.211018562317 |  8828.327298164368 |  11380.73992729187
 Sel* Colombia          |  8694.387829303741 | 6963.425874710083 | 8149.151265621185 |  8704.618453979492 | 9153.236508369446 | 11787.146806716919
 Sel* Subquery Colombia |  8622.495520114899 | 6959.257125854492 | 8179.068505764008 |  8765.061974525452 |  9159.55775976181 |  10187.61420249939
 Sel1 Colombia          | 22717.704384326935 | 8666.495084762573 | 22885.42276620865 | 23949.790477752686 | 24966.21882915497 | 30625.644207000732

 Sel1 Subquery Colombia |  7529.951772689819 | 6241.269111633301 | 7127.403438091278 |   7577.62348651886 | 7866.843640804291 |   8954.48899269104
 ;

-- After including transaction_id in country,date index
       query x 20       |        avg         |        min         |        q1         |       median       |         q3         |        max
------------------------+--------------------+--------------------+-------------------+--------------------+--------------------+--------------------
 Count Colombia         |  10326.94479227066 |  7079.586982727051 | 8091.441631317139 | 10685.971021652222 | 11660.240888595581 | 16219.580888748169
 Subquery Colombia      |  8345.360279083252 | 6759.0179443359375 | 7150.483548641205 |  7609.055519104004 |  8118.529975414276 | 15819.210052490234
 Sel* Colombia          |  9401.683914661407 |  8350.785970687866 | 8727.016389369965 |  9171.823978424072 |  9705.730974674225 | 12684.055089950562
 Sel* Subquery Colombia | 10874.297595024109 |  7996.103048324585 | 9317.362785339355 |  10767.66049861908 |  12130.92851638794 | 14003.422021865845
 Sel1 Colombia          | 14704.787838459015 |  7033.560991287231 | 8938.009798526764 |  11308.07101726532 |  21711.08090877533 | 25156.877994537354

 Sel1 Subquery Colombia |  7128.487503528595 |  5076.292991638184 | 5678.286790847778 |  6925.720572471619 |  8272.867858409882 | 11430.468082427979

      query x 100       |        avg         |        min         |         q1         |       median       |         q3         |        max
------------------------+--------------------+--------------------+--------------------+--------------------+--------------------+--------------------
 Count Colombia         | 8165.0702357292175 |  5923.334121704102 |  6800.160050392151 | 7435.7980489730835 |  9075.710475444794 | 13613.409042358398
 Subquery Colombia      |  7299.517266750336 |  5389.672040939331 |  6362.253367900848 |   6781.42237663269 |  7978.189289569855 | 11542.781829833984
 Sel* Colombia          | 14157.406282424927 |  8775.223016738892 |  13062.03180551529 | 14233.824968338013 | 15513.144373893738 |  19184.97586250305
 Sel* Subquery Colombia | 13438.675961494446 | 10216.159105300903 | 12183.876752853394 | 13196.363925933838 | 14356.310486793518 | 20111.860036849976
 Sel1 Colombia          | 13753.776743412018 |  7020.914793014526 | 7893.3587074279785 |  9101.168870925903 |  22971.67855501175 | 26913.809061050415
 Sel1 Subquery Colombia |  6757.480027675629 |  5529.844045639038 |  6212.466478347778 |  6777.510046958923 |  7212.876975536346 |  8500.235080718994