Thread: 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
Attachment
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