Re: Serious performance problem

"Tille, Andreas" 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 tried some more on optimizing the query on my work computer
(AMD ATHLON 850, 512MB, PostgreSQL 7.1.3 with default memory settings)

SELECT MeldeKategorie,
Count(ID) AS Anz
FROM Hauptdaten_Fall
WHERE IstAktuell=20
GROUP BY MeldeKategorie
ORDER BY MeldeKategorie;

real 0m9.675s

create index i1 on Hauptdaten_Fall(IstAktuell,MeldeKategorie);

----------------------------
set enable_seqscan = off;
SELECT MeldeKategorie,
Count(ID) AS Anz
FROM Hauptdaten_Fall
WHERE IstAktuell=20
GROUP BY MeldeKategorie
ORDER BY MeldeKategorie;

Aggregate (cost=4497.30..4510.18 rows=258 width=16)
-> Group (cost=4497.30..4503.74 rows=2575 width=16)
-> Sort (cost=4497.30..4497.30 rows=2575 width=16)
-> Index Scan using i1 on hauptdaten_fall
(cost=0.00..4351.40 rows=2575 width=16)

real 0m7.131s

---------------------------

set enable_seqscan = off;
SELECT MeldeKategorie,
Count(ID) AS Anz
FROM Hauptdaten_Fall
WHERE IstAktuell=20
GROUP BY IstAktuell,MeldeKategorie
ORDER BY IstAktuell,MeldeKategorie;

Aggregate (cost=4497.30..4510.18 rows=258 width=16)
-> Group (cost=4497.30..4503.74 rows=2575 width=16)
-> Index Scan using i1 on hauptdaten_fall (cost=0.00..4351.40
rows=2575 width=16)

real 0m3.223s

-- same after doing

cluster i1 on Hauptdaten_Fall;

real 1.590 -- 1.600

select count(*) from Hauptdaten_Fall;

real 0m0.630s

---------------------------

The following query is marginally (about 0.1 sec) faster, though the
plan looks the same down to cost estimates.

SET ENABLE_SEQSCAN = OFF;
SELECT MeldeKategorie,
Count(*) AS Anz
FROM (select IstAktuell,MeldeKategorie from Hauptdaten_Fall where
IstAktuell=20) sub
GROUP BY IstAktuell,MeldeKategorie
ORDER BY IstAktuell,MeldeKategorie;

Aggregate (cost=0.00..4370.72 rows=258 width=16)
-> Group (cost=0.00..4364.28 rows=2575 width=16)
-> Index Scan using i1 on hauptdaten_fall (cost=0.00..4351.40
rows=2575 width=16)

real 0m1.438s - 1.506s

---------------------------

now I make the dataset bigger keeping the number of rows returned by
query the same

insert into hauptdaten_fall (istaktuell, meldekategorie)
select istaktuell + 20, meldekategorie
from hauptdaten_fall ;

INSERT 0 257530

insert into hauptdaten_fall (istaktuell, meldekategorie)
select istaktuell + 40, meldekategorie
from hauptdaten_fall ;

INSERT 0 515060
ifsgtest=# select count(*) from hauptdaten_fall;
count
---------
1030120
(1 row)

cluster i1 on Hauptdaten_Fall;
vacuum analyze;

-- The query time is still the same 1.44 - 1.5 sec

SET ENABLE_SEQSCAN = OFF;
SELECT MeldeKategorie,
Count(*) AS Anz
FROM (select IstAktuell,MeldeKategorie from Hauptdaten_Fall where
IstAktuell=20) sub
GROUP BY IstAktuell,MeldeKategorie
ORDER BY IstAktuell,MeldeKategorie;

Aggregate (cost=0.00..4370.72 rows=258 width=16)
-> Group (cost=0.00..4364.28 rows=2575 width=16)
-> Index Scan using i1 on hauptdaten_fall (cost=0.00..4351.40
rows=2575 width=16)

real 0m1.438s - 1.506s

----------------------------

now back to original data distribution, just 4 times bigger

ifsgtest=# update hauptdaten_fall
ifsgtest-# set istaktuell = case when istaktuell % 20 = 0 then 20 else
10 end
ifsgtest-# ;
UPDATE 1030120
ifsgtest=# vacuum analyze;
VACUUM

SET ENABLE_SEQSCAN = OFF;
SELECT MeldeKategorie,
Count(*) AS Anz
FROM (select IstAktuell,MeldeKategorie from Hauptdaten_Fall where
IstAktuell=20) sub
GROUP BY IstAktuell,MeldeKategorie
ORDER BY IstAktuell,MeldeKategorie;

real 0m6.077 -- 6.606s

and after clustering:
cluster i1 on Hauptdaten_Fall;

real 0m5.683 - 5.750s

so it's linear growth here

----------------------------

Hannu