Postgresql super slow on server - super fast locally?

Question

I am hosting a Postgresql database on Heroku with a standard-0 plan.

My database has a table called transactions which contains ~18 million rows:

SELECT COUNT(*) FROM transactions;
  count   
----------
 17927768
(1 row)

Over the past months I've been noticing that the database is getting slower and slower. I am now at the point where I receive time-outs from my applications because (even simple) queries take longer than 30 seconds.

While trying to find out what is going on I noticed something weird:

On the hosted server a simple query like:

EXPLAIN ANALYZE SELECT COUNT(*) FROM transactions WHERE partner_id = 1;
---------------------------------------------------------------------------------
Finalize Aggregate  (cost=405691.73..405691.74 rows=1 width=8) (actual time=34941.061..34961.256 
rows=1 loops=1)
   ->  Gather  (cost=405691.63..405691.73 rows=1 width=8) (actual time=34940.913..34961.247 rows=2
 loops=1)
         Workers Planned: 1
         Workers Launched: 1
         ->  Partial Aggregate  (cost=404691.63..404691.63 rows=1 width=8) (actual time=34924.080.
.34924.081 rows=1 loops=2)
               ->  Parallel Seq Scan on transactions  (cost=0.00..400083.56 rows=9216145 width=0) 
(actual time=77.981..34179.970 rows=7801236 loops=2)
                     Filter: (partner_id = 1)
                     Rows Removed by Filter: 1164606
 Planning Time: 0.755 ms
 JIT:
   Functions: 10
   Options: Inlining false, Optimization false, Expressions true, Deforming true
   Timing: Generation 1.912 ms, Inlining 0.000 ms, Optimization 30.606 ms, Emission 119.538 ms, To
tal 152.057 ms
 Execution Time: 35190.328 ms
(14 rows)

takes up to 35 seconds.

But when I download the production dump to my machine (an older thinkpad) the query takes less than a second:

EXPLAIN ANALYZE SELECT COUNT(*) FROM transactions WHERE partner_id = 1;
---------------------------------------------------------------------------------
 Finalize Aggregate  (cost=251757.89..251757.90 rows=1 width=8) 
(actual time=669.234..674.362 rows=1 loops=1)
   ->  Gather  (cost=251757.67..251757.88 rows=2 width=8) (actua
l time=669.008..674.348 rows=3 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Partial Aggregate  (cost=250757.67..250757.68 rows=
1 width=8) (actual time=638.447..638.448 rows=1 loops=3)
               ->  Parallel Index Only Scan using index_transact
ions_on_partner_id on transactions  (cost=0.44..234528.06 rows=6
491844 width=0) (actual time=0.061..405.148 rows=5199597 loops=3
)
                     Index Cond: (partner_id = 1)
                     Heap Fetches: 0
 Planning Time: 0.231 ms
 JIT:
   Functions: 11
   Options: Inlining false, Optimization false, Expressions true, Deforming true
   Timing: Generation 3.109 ms, Inlining 0.000 ms, Optimization 0.826 ms, Emission 11.406 ms, Total 15.342 ms
 Execution Time: 676.047 ms
(14 rows)

One can also see that the hosted Postgresql uses a parallel seq scan, while the local instance uses a parallel index scan.

How is this possible? What do I need to do to get somewhere near this performance on the hosted server?

Edit 1: More information regarding 'bloat'

I tried to investigate the possible bloat and I received this for the transaction table:

type   | schemaname |  object_name  | bloat |   waste    
-------+------------+--------------+-------+------------
 table | public     | transactions |   1.3 | 571 MB

And this:

 schema |             table              | last_vacuum | last_autovacuum  |    rowcount    | dead_rowcount  | autovacuum_threshold | expect_autovacuum 
--------+--------------------------------+-------------+------------------+----------------+----------------+----------------------+-------------------

 public | transactions                   |             |                  |     17,949,072 |            600 |      3,589,864       | 

These queries are generated by Herokus built-in tools to analyze bloat as described here.

A dead rowcount of 600 in comparison to the 17 million rows looks neglectable - but why is the waste so high (570MB)? Could this be the source of the problem? It seems that a vacuum was never performed.

Answer 1

The "waste" looks like only 32 bytes per tuple, which doesn't seem particularly egregious. I wouldn't take any action or reach any conclusion based on that. Their own documentation says thing over 10 are worth looking into, you are only at 1.3.

Their script for vacuum-stats seems obsolete. It doesn't include references to autovacuum_vacuum_insert_scale_factor, which is what should be driving vacuuming for your table. That parameter was implemented in v13, specifically to serve the needs of insert-mostly tables, which seems to be what you have. In addition to excluding this from their script, perhaps they have overridden the default setting of 0.2 with something unhelpful, which would explain the lack of vacuuming which in turns explain the lack of index-only scan, which probably in turn explains the slowness.

Manually vacuum the table to see if that actually fixes the problem. Also, check their default setting of autovacuum_vacuum_insert_scale_factor to see if that explains why autovacuum didn't process the table and so a manual vacuum was needed.