Re: SLOOOOOOOW

On Wed, 2005-09-07 at 01:50, Jürgen Rose wrote:
> Scott Marlowe wrote:
> >
> > A couple of points:
> >
> > 1:  You wouldn't buy the QE II (a big luxery liner) and complain that it
> > doesn't work well for water skiing and is too complex.  It's the QE II.
>
> I don't get that argument.

The real point is that database performance isn't very interesting from
the point of view of a single user.

I've seen plenty of database driven apps that were quite snappy for a
single user bog down and fall over dead when a dozen or a hundred users
started accessing it.

PostgreSQL's strength is in how it handles dozens, hundreds, and
sometimes thousands of users.  It's not a dog at the single user
situation either, mind you, but not a development priority like multiple
user performance is.  Right along side that is performance of things
like schematic changes while in use and under load.  PostgreSQL also
excels at that.

> > 2:  You've given us absolutely nothing we can go on to help you make
> > postgresql work better for you.  Nothing.  Just one explain analyze
> > output.
>
> It is a bit complex, and I have now added two eplains from the main
> queries to this mail

Thanks, however an explain ANALYZE would tell us much more.  explain
tells us what the query planner thinks is gonna happen, explain analyze
tells us both what the query planner expects, and then what really
happens.  Far more useful for troubleshooting.

> > 3:  If you're running one OS on top of another, and then a database on
> > top of that, and all you've got is 512 Meg of RAM, don't expect stellar
> > performance, especially from a database that uses shared memory like
> > postgresql does.
>
> I don't expect mega performance, this is just a devel system, I mean do
> you develop your database on the productive system?

Well, I develop it on a system as close as possible to a production
system, within reason.  We deploy to production on linux, so I develop
on linux.  My workstation hardware is actually pretty good, considering
it's only got a single IDE hard drive.

> > 4:  Don't compare PostgreSQL to those other databases unless you're
> > going to give it a chance.  So far, you haven't done so, you've only
> > complained.
>
> I used it now for over half a year, and as I said in one of my previous
> mails, it is quite powerful and flexible, but I'm not impressed with the
> performance and it has its quirks.

But you've used it without any tuning of any kind.  That means you
haven't really had a chance to see it at its best yet.

> > 5:  I have tested a properly tuned PostgreSQL server that was on about
> > 1/2 the hardware (CPU speed, memory, RAID array) as a MSSQL server and
> > easily out ran it.  But, I took my time, read the docs, and tuned the
> > server OS and PostgreSQL
>
> As I said before, I don't want to tune my system (my devel!), in my
> opinion it has to run with ok performance out of the box.

Wait, you're not willing to invest 2 minutes to make one or two minor
changes that might double or even treble the speed of your development
machine?  I can't help you then.  Not on that.  no one's going to do
what it takes to make your development machine run postgresql faster out
of the box if it means postgresql can't run out of the box on half the
machines it now runs on.  There has been some work on auto-tuning it.
But since you're running under an emulator, I can't imagine performance
is all that important.  Are you running Windows with linux on top in
vmware, then postgresql?  If that's the only reason you're running
vmware, then get the windows native port and run it, it should be MUCH
faster than the emulated one you're running now.

> > 6:  Databases may appear simple, they are not, and the more complex they
> > are, the more you'll have to do to make full use of them.
> >
> > So, have you been running vacuum and analyze, do you have the right
> > indexes, are you using queries that can use those indexes, have you
> > turned up sort_mem and a few other easily tweakable settings.
> >
> > PostgreSQL's use of shared memory, combined with many older Operating
> > systems have VERY conservative settings for such, combined further with
> > the need for PostgreSQL to run on dang near anything, mean that, often,
> > out of the box, it's not as fast as some other servers.
>
> I can set the sort_mem to what I want, but postgres doesn't care. It
> just consumes my CPU time but no memory.

Again, have you been running vacuum and analyze regularly, do you know
the difference between a regular and full vacuum and which you may need
(right now a full vacuum may help a bit, if you haven't been vacuuming
up til now, then schedule regular vacuums)

sort_mem is just one setting, and unless the database is in the act of
sorting, it won't use that memory.  Have you read the tuning guide on
the varlena site?

> I'm sorry but it is a bit complicated to explain the whole structure.
>
> And my main point is, that with each row I insert, it becomes slower,
> and I will try to reproduce that behaviour, but I don't have so much
> time for it right now.

That sounds like index and / or table bloat.  Are you doing any updates
or deletes on this table as well? Dead tuples will slow you down over
time if they aren't reclaimed.

Could you repost both the query and the explain ANALYZE output for your
queries?  Thanks.

Also, feel free to post your postgresql.conf file.

Note that you're not likely to ever get performance equivalent to
"peppy" or something like that on a laptop running under vmware.

Looking at your current query plans, it would seem that increasing both
shared memory and sort memory would help, as shared memory being
increase might let the planner choose a hash join method that fits in
memory and is much faster than a merge or loop join.

The sort memory would help with all those sorts, of course.