Re: Performance question (stripped down the problem)

On Thu, 20 Sep 2001, Herbert Liechti wrote:

> I tried it. See my actions below. The main performance boost is
> reached by creating an index and disabling the sequential scan:
Thanks. I tried this and it helps in dead (see below).

> -------------------------------------------------------
> create index ix_1 on hauptdaten_fall(meldekategorie);
I did so before for in other tests.
> -------------------------------------------------------
>
> Same statement
>
> real 0m18.259s
> user 0m0.020s
> sys 0m0.010s
The same on my machine:

real 0m18.128s
user 0m0.070s
sys 0m0.010s

> -------------------------------------------------------
> now disable seqscan:
> -------------------------------------------------------
> time psql tt <<END
> set enable_seqscan = off;
> 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;
> END
>
>
> real 0m3.701s
> user 0m0.010s
> sys 0m0.000s

real 0m5.905s
user 0m0.060s
sys 0m0.030s

I have no real explanation why I have the same result in the first
case but significant more time for the second. but it helps for the
first step.

On the other hand if I test my *real* database:

real 0m20.539s
user 0m0.060s
sys 0m0.060s

and with "set enable_seqscan = off;"

real 0m10.133s
user 0m0.040s
sys 0m0.020s

I get in fact an increase of speed by factor 2, but anyway it is even
far to slow for our application. If I start a slightly more complex
query (not to mention that we are far from the amount of data we will
get after a year, I get the following stats:

-- default -- set enable_seqscan = off; MS-SQL server
real 0m30.891s real 0m27.165s about 1s
user 0m0.050s user 0m0.080s
sys 0m0.070s sys 0m0.050s

<other query example>

real 0m53.698s real 0m54.481s about 2.5s
user 0m0.190s user 0m0.180s
sys 0m0.040s sys 0m0.040s

This is about factor 20 compared to the MS-SQL server and I have
real hard arguing for PostgreSQL. In fact the MS-SQL server times
are estimated from inside Access - the plain server would be even
faster.

By the way - the last example shows that enforcing index scan don´t
necessarily makes the thing faster - perhaps it could even slow down
for other queries??

I would be happy to forward the exact queries which lead to this
measures if someone is interested.

Kind regards

Andreas.