Aurora postgres slect is slow

Question

I'm trying to figure out what is causing the Aurora RDS Postgres database to deteriorate in terms of performance over time.

I have a quite big table in a multi tenant architecture. But only one tenant became quite slow to perform a SELECT on this table.

I have tried to run reindex on the table and the schema, I have also done a vacuum on that table but I can't see any improvement.

Strangely if I do a backup and restore of this schema(then same size of tables) it is really fast for few days then it starts to deteriorate.

There are few batch jobs with spark that ingest and populate the schema but I do not know the details, I assume that something happens in the ingestion process.

What I'm trying to understand is what a backup and restore does more the reindex and vacuum? What am I missing?

EXPLAIN (ANALYZE, BUFFERS) SELECT...

"Limit  (cost=0.56..14527.17 rows=1 width=99) (actual time=12056.841..12056.842 rows=1 loops=1)"
"  Buffers: shared hit=62835 read=34334"
"  ->  Index Scan using idx_namekey on delqmst  (cost=0.56..7379519.03 rows=508 width=99) (actual time=12056.840..12056.840 rows=1 loops=1)"
"        Filter: ((upper((dmacctg)::text) ~~ '6'::text) AND (upper((dmacct)::text) ~~ '0000000000025454448'::text))"
"        Rows Removed by Filter: 793350"
"        Buffers: shared hit=62835 read=34334"
"Planning time: 11.435 ms"
"Execution time: 12056.876 ms"```

As you can see it took around 12 seconds if I do backup and restore of the schema it takes around 1 second.