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