0

I ran a SQL-Statement with the explain prefix on two databases.

Testsystem:

          ->  Nested Loop  (cost=0.42..4841.28 rows=68 width=271)
           ->  Parallel Seq Scan on table1_aud table1_  (cost=0.00..70318.43 rows=68 width=242)
                      Filter: ((table1_id= 500) AND (revtype = 1))

Production:

          ->  Nested Loop  (cost=0.42..70868.85 rows=68 width=271)
           ->  Parallel Seq Scan on table1_aud table1_  (cost=0.00..70318.43 rows=68 width=242)
                      Filter: ((table1_id= 500) AND (revtype = 1))

The Query on the production system took way longer then it should have. What could be the reson for that?

Backgroundinfo: We have two Databases a production and a test. We did a backup and restore via pgAdmin from production to test so that both have the same data. (Minus the "temporary data" witch does not seem to be included in the backup and i dont know how). The production Database has a size of 12 gb. The testsystem 3 gb after restore. There is no missing data.

We already tryed to "clean" the production database with vacuum (verbose, analyze); reindex (verbose) database concurrently schema;

After we did that the production system lost 1gb in size but nothing more.

2
  • 4
    I am guessing bloat. But first, please show the full output from EXPLAIN (ANALYZE, BUFFERS, SETTINGS) including the query. And always your version of Postgres. Feb 12 at 14:58
  • Ar you going to update your question, or can we just delete it? Feb 15 at 4:15

0

Browse other questions tagged or ask your own question.