Re: performance problem aftrer update from 7.1 to 7.4.2

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(at)postgresql(dot)org so that your
>> message can get through to the mailing list cleanly
>
>
>
>