Обсуждение: Postgresql performance

Hi everybody.

I am a newbie to Postgresql, trying to migrate an application from MSAccess.

I am quite dissapointed with the problems I am facing with some queries
containing multiple joins. I confess it has been hard for someone that is
not a DBA to figure out which are the problems. Just to ilustrate, I have
some queries that provide a reasonable query plan (at least from my point of
view), but that return no result: keep running on and on.

My system description is: Postgresql 7.1.3, Linux RedHat 7.1 (all patches
applied), 160Mb RAM.

Is the performance of the mentioned Postgresql version much slower than the
7.3.1?

All advice will be more than appeciated.

Regards.

Fabio


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.461 / Virus Database: 260 - Release Date: 10/03/2003

There are a lot of things that can be done to speed up your queries.
Especially if you are using the kludge that Access puts out.  Post some of
them and we can help.

As far as the difference between 7.1.3 and 7.3.2 there are a lot of
optimizations as well as bug fixes, its always a good idea to upgrade.

HTH
Chad
----- Original Message -----
From: "Enix Empreendimentos e Construções Ltda." <contato@enix.com.br>
To: <pgsql-performance@postgresql.org>
Sent: Wednesday, March 12, 2003 1:47 PM
Subject: [PERFORM] Postgresql performance


> Hi everybody.
>
> I am a newbie to Postgresql, trying to migrate an application from
MSAccess.
>
> I am quite dissapointed with the problems I am facing with some queries
> containing multiple joins. I confess it has been hard for someone that is
> not a DBA to figure out which are the problems. Just to ilustrate, I have
> some queries that provide a reasonable query plan (at least from my point
of
> view), but that return no result: keep running on and on.
>
> My system description is: Postgresql 7.1.3, Linux RedHat 7.1 (all patches
> applied), 160Mb RAM.
>
> Is the performance of the mentioned Postgresql version much slower than
the
> 7.3.1?
>
> All advice will be more than appeciated.
>
> Regards.
>
> Fabio
>
>
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.461 / Virus Database: 260 - Release Date: 10/03/2003
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>

> Is the performance of the mentioned Postgresql version much slower than the
> 7.3.1?

Somewhat, but not significantly.

Standard questions:

Have you run VACUUM?
Have you run ANALYZE?
What does EXPLAIN ANALYZE <query> output for the slow queries?

If performance is still poor after the first 2, send the results of
EXPLAIN here and we'll tell you which index you're missing ;)

--
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc

Enix Empreendimentos e Construções Ltda. kirjutas K, 12.03.2003 kell
22:47:
> Hi everybody.
>
> I am a newbie to Postgresql, trying to migrate an application from MSAccess.
>
> I am quite dissapointed with the problems I am facing with some queries
> containing multiple joins.

Postgres currently does *not* optimize join order for explicit joins
(this is currently left as a cludge for users to hand-optimize query
plans).

To get the benefits form optimiser you have to rewrite

FROM TA JOIN TB ON TA.CB=TB.CB

to

FROM A,B
WHERE TA.CB=TB.CB

> I confess it has been hard for someone that is
> not a DBA to figure out which are the problems. Just to ilustrate, I have
> some queries that provide a reasonable query plan (at least from my point of
> view), but that return no result: keep running on and on.

Could you try to explain it in other words (or give an example). I am
not native english speaker and I can read your text in at least 5
different ways ;(

> Is the performance of the mentioned Postgresql version much slower than the
> 7.3.1?

It can be slower. It may also be a little faster in some very specific
cases ;)

--------------
Hannu