The question is published on by Tutorial Guruji team.
My question is general, but I’ve also aparticulr example to explain my problem. I developed a webapp with persistence layer based on Openjpa and used two databases to test it. One is Postgres 8.1 and the other Postgres 9.2. They have the same content, same data!
When I deployed my webapp all worked well but when I use PostgreSql 8.1 I’ve a great and very strange problem: a select query of the type
SELECT * FROM MEDICINE_CASE takes max 10 secs on Postgres 9.2 and over than 40-50 secs on Postgres. This could waste a lot of good work on my webapp. MEDICINE_CASE table is an huge table with 20 columns and over than 5000 entries, and it has 7-8 of foreign key relationships with other tables. Can You help me to discover why this problem happens and how to solve it on Postgres 8.1??
Normally you’d do an EXPLAIN ANALYZE of the query, but for a simple
SELECT * FROM tablename it’s not going to reveal much. Also EXPLAIN in 8.1 doesn’t have the BUFFERS option.
You say it’s a huge table with 5000 entries, but it’s generally considered very small by today’s standards in terms of number of rows. Yet 40 seconds for a table scan is long and suggests it has lots of contents.
So either the table contain huge contents like columns with megabytes of text or bytea contents, or it’s bloated due to lack of maintenance. Or both.
If it’s possible for you to just dump and reload your 8.1 database (pg_dump to sql file/ dropdb / createdb / reload sql file) you’ll know easily whether the performance difference really depends on the version (doubtful) or on the bloat and fragmentation.
Also, what you really should do anyway is give up on 8.1 and use a supported version instead.