Re: Performance question (stripped down the problem)

On Wed, 19 Sep 2001, Peter Eisentraut wrote:

> > 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;
>
> > (which should just measure the time needed for this task). It took my
> > E250 (2x400MHz, 2GB) server 20 seconds and this is definitely much to
> > long for our application.
>
> I loaded this into 7.2 development sources and it ran 35 seconds
> wall-clock time on a much smaller machine. After I ran what in 7.1 would
> be VACUUM ANALYZE it took about 22 seconds. The difference was that it
> was using the index on hauptdaten_fall.istaktuell when it shouldn't.
> (Try EXPLAIN to see what it does in your case. If it's still using the
> index you might want to force enable_indexscan = off.)
Well, I tried several things to avoid/enforce index scans. The problem
remains every time. We don´t talk about 20 or 30 seconds here.
We talk about "slow" (i.e. > 10 seconds) for PostgreSQL against "fast"
(i.e. feeling like immediately, not to measure exactly) on MS-SQL server.

> Besides that, I don't see anything blatantly obvious to speed this up.
This fact is quite hard for me. I´ve got some hints about changes in
table structure (use of an index for MeldeKategorie) and I could perhaps
track down this way even if I´m relatively stong bound to the given
stucture because the data should be "replicated" from MS-SQL server to
PostgreSQL.

Moreover ths problem is not this "quite simple" query (compared to other
more complicated ones). This was just a simple test. My colleague
posted me another (more practical) query which took him under
Access querying the MS-SQL server (so there is an additional bottleneck)
2,5 seconds. PostgreSQL took 92 seconds for the same task. This is
way to slow and would stop our PostgreSQL efforts completely, which
means I would have to search for OS alternatives (bad) or even use
the MS-SQL server (even worse if I look at the current NT-Worms).

Perhaps this test could be an example which helps developers to
optimize such kind of queries. I´m afraid the GROUP BY / ORDER BY
combination which causes expensive sorting could be the reason for
this slow down. Perhaps they can find a more sophisticated sorting
algorithm. There must be a reason for this speed difference by the
order of magnitude for this special case and I would be more than
happy if I could help increasing PostgreSQL by providing this example
(to make PostgreSQL better in general and fit for my own task).

Kind regards

Andreas.