1

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??

Improve this question
7
  • The two databases are running in the same machine or you have an individual machine with each database and if this is the case the two machines are also the same configuration? Commented Nov 14, 2013 at 14:53
  • 1
    Also execute an explain in both DBs and post it here: EXPLAIN SELECT * FROM MEDICINE_CASE Commented Nov 14, 2013 at 14:54
  • I've the two databases on different machines. But I don't think is an hw problem Commented Nov 14, 2013 at 14:56
  • You would be amazed to see how much difference what one more processor/core or some more giga of memory could do. Commented Nov 14, 2013 at 14:59
  • Do you really care about the performance on version 8.1? It's EOL for a long time (November 2010), you shouldn't use it anymore. Commented Nov 14, 2013 at 15:31

1 Answer 1

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.