Hello Andreas,

A possible solution would be:
CREATE TABLE foo AS
SELECT Hauptdaten_Fall.MeldeKategorie, Count(Hauptdaten_Fall.ID) AS Anz 
FROM Hauptdaten_Fall WHERE (((Hauptdaten_Fall.IstAktuell)=20))
GROUP BY Hauptdaten_Fall.MeldeKategorie ORDER BY 
Hauptdaten_Fall.MeldeKategorie;

This is suitable if your data does not change often. To get automatic updates:
1) Define iPrecision, the precision that you need (integer).
2) Create a trigger which increases a counter when a record is updated or 
inserted. When the counter reaches iPrecision, do a DROP TABLE foo + CREATE 
TABLE foo AS SELECT Hauptdaten_Fall.MeldeKategorie, 
Count(Hauptdaten_Fall.ID)... This will take a few seconds but only once. 
Run a batch script within a time frame (1 hour, 4 hours, 1 day ?) so a 
human user has very little chance to reach iPrecision.

On 300.000 records, you will get instant results. There are plenty of 
tricks like this one. If you employ them, you will ***never*** reach the 
limits of a double Pentium III computer with U3W discs.

If you need to answer this message, please reply on 
pgsql-general@postgresql.org.

Cheers,
Jean-Michel POURE


At 13:43 29/10/01 +0100, you wrote:
>Hello,
>
>I discussed a problem concerning the speed of PostgreSQL compared to
>MS SQL server heavily on postgres-general list.  The thread starts with
>message
>
>     http://fts.postgresql.org/db/mw/msg.html?mid=1035557
>
>Now I tried a snapshot of version 7.2 and got an increase of speed of
>about factor 2.  But sorry this is really not enough.  The very simple
>test I pointed to in my mail is even much to slow and the issue would
>probably spoil down the whole project which should be a complete open
>source solution and would perhaps and in any M$ stuff.  Iï¿½ve got under
>heavy preasur from my employer who was talking about the nice world
>of MS .net (while he is using MS-SQL exclusively).  To make the thing
>clear the issue is the gal database of infectious diseases in Germany
>runned by the Robert Koch-Institute.  So the beast could be of some
>importance for increasing the acceptance of PostgreSQL and Open Source
>in the field of medicine which is generally known for the money which
>is involved in.  So I really hope that some skilled programmers would
>be able to find a good way to solve the performance issue perhaps by
>just profiling the simple query
>
>    SELECT Hauptdaten_Fall.MeldeKategorie, Count(Hauptdaten_Fall.ID) AS 
> Anz FROM Hauptdaten_Fall WHERE (((Hauptdaten_Fall.IstAktuell)=20))
>GROUP BY Hauptdaten_Fall.MeldeKategorie ORDER BY 
>Hauptdaten_Fall.MeldeKategorie;
>
>to the data set I put on
>
>    http://www.foodborne-net.de/~tillea/ifsg/ifsgtest.dump.bz2
>
>If this should take less than half a second on a modern PC I could
>continue to try mo realistic queries.
>
>I really hope that I could readjust the image of PostgreSQL in the
>eyes of my M$-centered colleagues.
>
>Kind regards
>
>        Andreas.
>
>---------------------------(end of broadcast)---------------------------
>TIP 5: Have you checked our extensive FAQ?
>
>http://www.postgresql.org/users-lounge/docs/faq.html


Mon, 29 Oct 2001 20:10:32 +0100
Jean-Michel POURE <jm.poure@freesurf.fr>
Re: Serious performance problem
On Mon, 29 Oct 2001, Jean-Michel POURE wrote:

> A possible solution would be:
> CREATE TABLE foo AS
> SELECT Hauptdaten_Fall.MeldeKategorie, Count(Hauptdaten_Fall.ID) AS Anz
> FROM Hauptdaten_Fall WHERE (((Hauptdaten_Fall.IstAktuell)=20))
> GROUP BY Hauptdaten_Fall.MeldeKategorie ORDER BY
> Hauptdaten_Fall.MeldeKategorie;
Sorry, this is NO solution of my problem.

> On 300.000 records, you will get instant results. There are plenty of
> tricks like this one. If you employ them, you will ***never*** reach the
> limits of a double Pentium III computer with U3W discs.
It is really no help if I solve the speed issue of this *very simple,
zeroth order try*.  I repeat a hava a plenty of queries which do much
more complicated stuff than this.  This is just a rude strip down of the
problem fit for debugging/profg issues of the database *server*.  Simple
tricks on a simple example do not help.

> If you need to answer this message, please reply on
> pgsql-general@postgresql.org.
No, because ...

> >I discussed a problem concerning the speed of PostgreSQL compared to
> >MS SQL server heavily on postgres-general list.  The thread starts with
> >message
> >
> >     http://fts.postgresql.org/db/mw/msg.html?mid=1035557
I did so and got the explicit advise of Tom to ask here.

Consider the problem as a benchmark.  I would love to see postgresql
as the winner.

Kind regards

        Andreas.

Tue, 30 Oct 2001 10:42:16 +0100 (CET)
"Tille, Andreas" <TilleA@rki.de>
Re: Serious performance problem

> > CREATE TABLE foo AS
> > SELECT Hauptdaten_Fall.MeldeKategorie, Count(Hauptdaten_Fall.ID) AS Anz
> > FROM Hauptdaten_Fall WHERE (((Hauptdaten_Fall.IstAktuell)=20))
> > GROUP BY Hauptdaten_Fall.MeldeKategorie ORDER BY
> > Hauptdaten_Fall.MeldeKategorie;
>Sorry, this is NO solution of my problem.

Allo Andreas,

For every problem there is a solution. That is what software optimization 
is all about. Do you make use of PL/pgSQL stored queries in your database? 
If not, you will probably end up with terrible nested queries that will 
eat-up server time and power.

Best regards,
Jean-Michel POURE

Tue, 30 Oct 2001 11:17:51 +0100
Jean-Michel POURE <jm.poure@freesurf.fr>
Re: [HACKERS] Serious performance problem
On Tue, 30 Oct 2001, Jean-Michel POURE wrote:

> >Sorry, this is NO solution of my problem.
          ^^^^
> For every problem there is a solution. That is what software optimization
For sure and as I said Iï¿½m really sure that it is a server side programming
issue (and others thought this as well on the hackers list).
But your proposal was not the solution.

> is all about. Do you make use of PL/pgSQL stored queries in your database?
> If not, you will probably end up with terrible nested queries that will
> eat-up server time and power.
Also stored procedures are not the solution and I see no reason to discuss
this topic here on general list (moreover you leave people unclear about
the original question).  It was discussed here in two long threads and
belongs to the PostgreSQL programmers.  I hope not to violate Tom Lanes
privacy if I quote here one sentence from him in a private posting:
  "Performance issues can be discussed in -hackers."
We do not talk about the performance of the datapase application but
about the server itself.

Kind regards

        Andreas.

Tue, 30 Oct 2001 13:17:18 +0100 (CET)
"Tille, Andreas" <TilleA@rki.de>
Re: [HACKERS] Serious performance problem