Re: performance problem aftrer update from 7.1 to 7.4.2 - Mailing list pgsql-general

hello again,

thanks for help :)

i re-dumped the database (called mcms09) from postgres 7.1 with the pg_dump command from 7.4.2 successfully and restored it to 7.4.2 (database called mcms, postgres 7.4.2 running on a different port)

i compared some querys from 7.1 to 7.4.2 and put all the output of the explain command to a website, because i think its too much for posting it here.
i just see that most time is spent in sorting the tables before (?) creating the filter.

7.1:
mcms09=> explain select * from newsletter where site_id='m000000-970' order by date desc,id desc limit 10;
NOTICE:  QUERY PLAN:
Limit  (cost=9.26..9.26 rows=7 width=84) ->  Sort  (cost=9.26..9.26 rows=8 width=84)       ->  Index Scan using site_id_newsletter_key on newsletter  (cost=0.00..9.14 rows=8 width=84)
EXPLAIN
7.4.2:
mcms=# explain select * from newsletter where site_id='m000000-970' order by date desc,id desc limit 10;                                              QUERY PLAN
---------------------------------------------------------------------------------------------------------Limit  (cost=17.78..17.81 rows=10 width=610)  ->  Sort  (cost=17.78..17.81 rows=11 width=610)        Sort Key: date, id        ->  Index Scan using site_id_newsletter_key on newsletter  (cost=0.00..17.59 rows=11 width=610)              Index Cond: ((site_id)::text = 'm000000-970'::text)
(5 rows)

but this query is the first one, done only once, then another subset of querys follow 10 times , the others depend on this first one, in my opinion this cant be the big factor which slows down the dbs

i also added new indexes to both databases on news, newsletter and newsletter_send table, speeding up the query by factor 10 :-) but 7.1 is always faster ....

you can see all the stuff , query, table layout and explains on the website http://www.erdtrabant.de/index.php?i=60500

thanks in advance
volker

Joshua D. Drake wrote:

Hello,

You are probably missing a step some where... I know you got your data imported but you might try
using the pg_dump from 7.4.2 to grab the 7.1 database and import from there. It sounds to me like you
are missing an index or something.

Also it would help if we new your data structure, if you could post an explain from 7.1 and from 7.4 and
possibly the queries that you are running your tests against.

Sincerely,

Joshua D. Drake


Development - multi.art.studio wrote:

Hello everyone,

i just upgraded my old postgres-database from version 7.1 to 7.4.2.
i dumped out my 7.1 database (with pg_dump from 7.1) as an sql-file with copy-commands and to one file using insert-statements.

after initalizing and starting postgres 7.4 on a different port and datadirectory, i tried to import the sql-dump with the copy statements.
this import fails, but importing the dump-file with inserts took a long time but was successfully.
ok, at first i do a vacuum and analyze and fire up the database again.
now i changed my php-scripts to use the new database as datasource.

ok, things look good, but as i was testing some php-sciripts, i recognized that the querys took about 2 or 5 times longer :(
a test a script which reads a lot of stuff from database, normaly with pgsql 7.1 it tooks about 4 seconds to display the data, but with postgres 7.4 it tooks about 25 seconds.
i start the 7.4 pgsql the same way as 7.1 with postmaster -D /xxx/xxx/...  -N512 -S -F -B2048 -i
i also tried to kill some indexes and recreate them, but this doesnt matter and the query took the same long time to execute. i also tested some simple select querys, the will take longer time.

if somebody could help me, and hints on how i can speed up my 'new' databse would be very nice :)

thanks in advance

yours
volker


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
     subscribe-nomail command to majordomo@postgresql.org so that your
     message can get through to the mailing list cleanly