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
|
