Re: SLOOOOOOOW - Mailing list pgsql-general

On Tue, 2005-09-06 at 16:09, Jürgen Rose wrote:
> Sorry, but I better use this email address, I just hate to use Outlook
> for this stuff.
>
> To Peter Eisentraut
>
> Yes, I've read the chapter in the manual.
>
> To Michael Glaesemann
>
> locally I run the database on my laptop (Dell D800) 1 GB Ram, but there
> within VMWARE with 512MB assigned RAM. But the target platform is a dual
> processor machine with 2 GB.
>
> But, and thats the big but here, I don't care. For me a database has to
> work satisfying in the first place without twiddeling some obscure knobs
> or push levers to get just accaptable performance if I only have a small
> set of data. Heck, I'm talking about maybe in the whole 45.000
> records!!! I mean I used Interbase, MySQL, SQLite, SQLServer before, and
> for this project postgres was set, so I had to use  it. Which is fine, I
> wouldn't mind, if I would not have such troubles.

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.

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.

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.

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.

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

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.

OTOH, it coexists well with other software. If you've ever tried to
build a MSSQL or ORacle box that did anything else, you know how those
two database engines just consume memory and CPU without really asking.
Here's an explain analyze on one of the production pgsql servers I work
on:

explain analyze select count(*) from sometable
                                                             QUERY
PLAN

-------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=6209.99..6209.99 rows=1 width=0) (actual
time=339.200..339.201 rows=1 loops=1)
   ->  Seq Scan on sometable  (cost=0.00..5856.19 rows=141519 width=0)
(actual time=0.025..202.636 rows=162427 loops=1)
 Total runtime: 339.262 ms
(3 rows)

Admitted, the rows aren't that big, but that's a seq scan of 160,000
rows.  Not bad really.  And it's quite fast at our more esoteric
reporting generation queries as well.

Now, we can sit here and argue about how nice it would be if PostgreSQL
just configured itself for maximum performance on installation, or you
can tell us what runs slow, and let us help you fix it.  The ball is in
your court.