How to compare two databases?

Question

PostgreSQL

Dear Habr, I believe in the power of your collective mind and hope to get a hint.
There are two almost identical databases with the same structure and data. The same, but not quite. On one of the bases, the query for the selection displays the result in less than 1 second, on the second, the same query takes about 4 seconds. Moreover, the database that works faster is larger in terms of data volume. It's not about the hardware, the databases work the same way on different servers - one is faster, the other is slower. How can you compare the contents of the database, tables, functions to find differences? Or where to dig at all?

UPD.I tried to do this: I deployed the structure from the “slow” base and wanted to deploy data there from the “fast” one - it gave errors. I took the structure from the “fast” database and deployed the data from the slow one there - the query is also slow.

Answer 1

Look at indexes, whether they are present at both in bases.
You can also see the statistics for your schema: www.postgresql.org/docs/9.1/static/monitoring-stats.html
There you can see the number of fullscans, query activity, etc.
Well, explain .

Answer 2

if you need to compare everything except data (structure, _indexes_, keys, relations, constraints), you can dump only structures, something like pg_dump -sxO database_name (s - dump only schema, x - do not dump privileges, O - do not dump owner), and then diff

Answer 3

I don’t know if there are tools to make a diff for everything at once, offhand a couple of narrowly targeted tools
from percona to compare all kinds of configs www.percona.com/doc/percona-toolkit/2.1/pt-config-diff.html
from the muscle - dev.mysql.com /doc/workbench/en/mysqldiff.html

Answer 4

What bases? Format?
In any case, you can’t do without code ... but with classic SQL you can do with “little blood”

Answer 5

I would also look towards VACUUM

Answer 6

In the context of the last clarification, “I took a structure from a “fast” base and deployed data from a slow one there - the query is also slow.” - can look at query plans in a fast database with original and "slow" data? They can change in Postgres depending on the real data and the accumulated statistics.