1

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.

6
  • Do you run the same Postgres version locally and remotely? What is it (they are)? Do you have the same parameter settings in both servers? How often do you vacuum analyze the table on the remote server?
    – mustaccio
    Feb 2 at 21:02
  • Hi @mustaccio locally it's version 13.4 and remotely version 13.13. I think I've never ran vacuum analyze manually. I don't know how often it is run by the hoster. Feb 2 at 21:11
  • Well, you'll have to systematically compare your two environments and find what is different between them that's causing the difference in the explain plans (and run time). For one, judging by the difference in the read row count, the remote table might have significant bloat.
    – mustaccio
    Feb 2 at 21:37
  • Your edit show the results of unknown queries. Without knowing the queries we don't know what they mean.
    – jjanes
    Feb 3 at 13:04
  • Hey @jjanes they are built-in tools from Heroku here: devcenter.heroku.com/articles/… Feb 3 at 13:10

1 Answer 1

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.

3
  • Hey @jjanes thanks for your answer. Should I run a vacuum or vacuum full or vacuum analze ? Is there anything else I would need to run regarding the indexes? Feb 4 at 7:04
  • I also just checked: The value is set to 0.2 on my database. Feb 4 at 9:33
  • Not vacuum full. It is both overkill (locks the table for s long time) and doesn't actually do what needs doing. The analyze i would add out of habit. It might help and at worst should do no harm other than make vacuum take very slightly longer.
    – jjanes
    Feb 4 at 11:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.