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.