Re: 8.2.4 serious slowdown

2008/1/10, Scott Marlowe <scott.marlowe@gmail.com>:
> On Jan 10, 2008 9:50 AM, Clodoaldo <clodoaldo.pinto.neto@gmail.com> wrote:
> > 2008/1/10, Sim Zacks <sim@compulab.co.il>:
> > > I just upgraded my database server from 8.0.1 to 8.2.4
> > > Most things went very well, but I have a couple of queries that really slowed down with the new server.
> > > On 8.0.1 the query took less then 3 seconds to complete. On 8.2.4 the same query
> > > (I vacuumed the database before running the query) takes 60 seconds (10 minutes with Explain Analyze).
> > >
> > > The data is exactly the same on the 2 servers.
> > >
> > > To test for hardware differences, I loaded 8.0.11 onto a test server restored the database and ran the query.
> > > It took about 3 seconds. I then uninstalled postgresql and installed version 8.2.4 and restored the database
> > > and the query took about 60 seconds.
> > >
> > > On the 8.2.4 the CPU usage (as seen from top) goes up to about 97% for most of the 60 seconds of query.
> > > On 8.0.1, it didn't.
> > >
> > > I have the explain from both databases, if someone could help me walk through this, I would much appreciate it.
> >
> > I have seen performance degradation at every new version since 7.3.
>
> Then your experience has been exactly the opposite of mine.

I suspect some developers here make a living from supporting
postgresql and have real world experience with it. I'm not sure who
they are as I don't read the list often. Are you one of them? If yes
can you tell from your clients experience that batch inserts of 800
thousands rows are faster now, especially with 8.3?

> > But now 8.3 is a complete disaster. It could be that my most expensive
> > query is just a corner case, but I don't believe it.
>
> So, what's the other explanation, all queries in 8.3 are slower, and
> everyone who says it's faster is just lieing?

Not all queries, but sure my batch insert queries are slower. I don't
know the explanation. I just don't think that batch insert queries are
irrelevant and could be treated as corner case.

> > Could you try 8.3 and see what happens? Keep the emails in case this
> > thread mysteriously disappears.
>
> Please stop the histrionics.

Yes, that was a big mistake and I apologize for it.

>  If your new query is slower, post the
> information here to help the hackers figure out why its slower and
> help you fix it.

I already did it in the mentioned thread and I did that trying to help
and I don't expect any special treatment. If the developers think it
is not a priority so be it. I can just keep 8.2 until it gets
unsupported and/or I find the time and motivation to migrate to
another db server. As it is now 8.3 performance for my most important
query it totally unacceptable.

Regards, Clodoaldo Pinto Neto