Re: Serious performance problem - Mailing list pgsql-hackers

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