Re: SLOOOOOOOW

Scott Marlowe wrote:
> 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.

I don't get that argument.

> 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

> 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?

> 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.

> 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.

> 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.

> 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.
>

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.

The following output is for the two main queries. There are some other
views depending on it, but the tuning was left as an exercise for later,
but it seems it would be needed now. The basic idea is just to handle
the stored persons and organisations in the same way, therefore using
the same tables and having the advantage of creating arbitrary
relationships between either persons and/or organisations. There are
more views and tables involved, but these are the ones which are used
most, and in all other views.

QUERY PLAN


------------------------------------------------------------------------------------------------------------------------------------------------------
  Merge Left Join  (cost=15664.00..56623.19 rows=33545 width=241)
    Merge Cond: ("outer".orgr_id = "inner".orgr_id)
    ->  Merge Left Join  (cost=15664.00..46776.22 rows=33545 width=235)
          Merge Cond: ("outer".orgr_id = "inner".orgr_id)
          ->  Merge Left Join  (cost=15664.00..36636.71 rows=31800
width=229)
                Merge Cond: ("outer".orgr_id = "inner".orgr_id)
                ->  Merge Left Join  (cost=15664.00..26521.15 rows=31065
width=223)
                      Merge Cond: ("outer".orgr_id = "inner".orgr_id)
                      ->  Merge Left Join  (cost=15664.00..16414.74
rows=30347 width=217)
                            Merge Cond: ("outer".orgr_id = "inner".orgr_id)
                            ->  Sort  (cost=7524.64..7600.50 rows=30347
width=148)
                                  Sort Key: r.orgr_id
                                  ->  Merge Join  (cost=0.00..3729.72
rows=30347 width=148)
                                        Merge Cond: ("outer".orga_id =
"inner".orga_id)
                                        ->  Index Scan using
pk_org_organisations on org_organisations o  (cost=0.00..1615.77
rows=20175 width=126)
                                              Filter: (orga_type =
'O'::bpchar)
                                        ->  Index Scan using
r_orga_orga_orga_orga_id_index on r_orga_orga r  (cost=0.00..1612.55
rows=59116 width=26)
                            ->  Sort  (cost=8139.36..8287.14 rows=59111
width=73)
                                  Sort Key: a.orgr_id
                                  ->  Seq Scan on org_addresses a
(cost=0.00..1634.11 rows=59111 width=73)
                      ->  Index Scan using org_contacts_orgr_id_index on
org_contacts c1  (cost=0.00..9570.22 rows=60514 width=10)
                            Filter: (cont_type = 'E'::bpchar)
                ->  Index Scan using org_contacts_orgr_id_index on
org_contacts c2  (cost=0.00..9570.22 rows=60514 width=10)
                      Filter: (cont_type = 'P'::bpchar)
          ->  Index Scan using org_contacts_orgr_id_index on
org_contacts c3  (cost=0.00..9570.22 rows=62359 width=10)
                Filter: (cont_type = 'F'::bpchar)
    ->  Index Scan using org_contacts_orgr_id_index on org_contacts c4
(cost=0.00..9570.22 rows=23745 width=10)
          Filter: (cont_type = 'H'::bpchar)
(28 Zeilen)


                   QUERY PLAN


--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  Merge Left Join  (cost=16199.20..88333.55 rows=27660 width=631)
    Merge Cond: ("outer".orgr_id = "inner".orgr_id)
    ->  Merge Left Join  (cost=16199.20..78455.64 rows=27660 width=625)
          Merge Cond: ("outer".orgr_id = "inner".orgr_id)
          ->  Merge Left Join  (cost=16199.20..68589.40 rows=27660
width=619)
                Merge Cond: ("outer".orgr_id = "inner".orgr_id)
                ->  Merge Left Join  (cost=16199.20..58696.51 rows=27660
width=613)
                      Merge Cond: ("outer".orgr_id = "inner".orgr_id)
                      ->  Merge Left Join  (cost=16199.20..48786.43
rows=27660 width=607)
                            Merge Cond: ("outer".orgr_id = "inner".orgr_id)
                            ->  Merge Left Join
(cost=16199.20..38719.72 rows=26221 width=601)
                                  Merge Cond: ("outer".orgr_id =
"inner".orgr_id)
                                  ->  Merge Left Join
(cost=16199.20..28673.57 rows=25615 width=595)
                                        Merge Cond: ("outer".orgr_id =
"inner".orgr_id)
                                        ->  Merge Left Join
(cost=16199.20..18634.97 rows=25023 width=589)
                                              Merge Cond:
("outer".orgr_id = "inner".orgr_id)
                                              ->  Merge Left Join
(cost=16199.20..16289.45 rows=25023 width=520)
                                                    Merge Cond:
("outer".orgr_id = "inner".orgr_id)
                                                    ->  Sort
(cost=15409.07..15471.62 rows=25023 width=487)
                                                          Sort Key:
r.orgr_id
                                                          ->  Merge Left
Join  (cost=1303.40..1941.01 rows=25023 width=487)
                                                                Merge
Cond: ("outer".orga_id = "inner".orga_id)
                                                                ->
Merge Join  (cost=0.00..4558.02 rows=25023 width=226)

Merge Cond: ("outer".orga_id = "inner".orga_id)
                                                                      ->
  Merge Join  (cost=0.00..2455.34 rows=16636 width=175)

     Merge Cond: ("outer".orga_id = "inner".orga_id)

     ->  Index Scan using org_personsprivate_orga_id_index on
org_persons p  (cost=0.00..631.83 rows=16635 width=112)

     ->  Index Scan using pk_org_organisations on org_organisations o
(cost=0.00..1517.52 rows=39301 width=63)
                                                                      ->
  Index Scan using r_orga_orga_orga_orga_id_index on r_orga_orga r
(cost=0.00..1612.55 rows=59116 width=59)
                                                                ->  Sort
  (cost=1303.40..1322.78 rows=7750 width=265)

Sort Key: v.orga_id
                                                                      ->
  Seq Scan on org_personsprivate v  (cost=0.00..227.50 rows=7750 width=265)
                                                    ->  Sort
(cost=790.14..812.97 rows=9133 width=37)
                                                          Sort Key:
cp.orgr_id
                                                          ->  Seq Scan
on org_contactpersons cp  (cost=0.00..189.33 rows=9133 width=37)
                                              ->  Index Scan using
org_addresses_orgr_id_index on org_addresses a  (cost=0.00..1884.97
rows=59111 width=73)
                                        ->  Index Scan using
org_contacts_orgr_id_index on org_contacts c1  (cost=0.00..9570.22
rows=60514 width=10)
                                              Filter: (cont_type =
'E'::bpchar)
                                  ->  Index Scan using
org_contacts_orgr_id_index on org_contacts c2  (cost=0.00..9570.22
rows=60514 width=10)
                                        Filter: (cont_type = 'P'::bpchar)
                            ->  Index Scan using
org_contacts_orgr_id_index on org_contacts c3  (cost=0.00..9570.22
rows=62359 width=10)
                                  Filter: (cont_type = 'F'::bpchar)
                      ->  Index Scan using org_contacts_orgr_id_index on
org_contacts c4  (cost=0.00..9570.22 rows=37923 width=10)
                            Filter: (cont_type = 'M'::bpchar)
                ->  Index Scan using org_contacts_orgr_id_index on
org_contacts c5  (cost=0.00..9570.22 rows=35502 width=10)
                      Filter: (cont_type = 'S'::bpchar)
          ->  Index Scan using org_contacts_orgr_id_index on
org_contacts c6  (cost=0.00..9570.22 rows=31814 width=10)
                Filter: (cont_type = 'N'::bpchar)
    ->  Index Scan using org_contacts_orgr_id_index on org_contacts c7
(cost=0.00..9570.22 rows=33427 width=10)
          Filter: (cont_type = 'X'::bpchar)
(50 Zeilen)