Re: Performance question (stripped down the problem)

Hi Andreas,

Sorry, I haven't seen the history of this thread.  One question which
might be relevant is, have you adjusted the postgresql.conf file from
the default memory settings to be something better?

If these are the times you're getting from a default configuration, you
might be able to get far better results by doing performance tuning of
PostgreSQL and/or the server.

What do you think?

Regards and best wishes,

Justin Clift


"Tille, Andreas" wrote:
>
> 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.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html

--
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
     - Indira Gandhi