Re: a MySQL to PostgreSQL successful port, but performance is bad...

> I just converted a working app from MySQL to
> PostgreSQL. I used fabForce's DBDesigner 4 to build
> and maintain my data model in MySQL. To port the
> schema, I exported the data model to an SQL file, and
> hand tweaked the file to change datatypes and move
> INDEX statements out of the Create Table statements.
> Everything worked like a charm. I can switch back and
> forth between the two databases by simply changing the
> ODBC connect string.
>
> Here's my problem, though: queries in mySQL are almost
> instantaneous, whereas those same queries in
> PostgreSQL can take four seconds or more - even when
> I'm hitting a table with only one row. I'll bet I have
> something misconfigured, but I'm a newbie FNG, and I
> don't yet know my way around yet.
>
> BTW, I did a search on mySQL in the novice threads,
> and performance seems like a hot-button issue. Please
> be assured that I have no issues or agendas with
> either product, and fully assume that I have something
> misconfigured. I'm slightly overwhelmed by the volume
> of documentation (on-line, and I bought books by
> Douglas&Douglas and Matthew&Stone) and would like a
> few points in the correct direction.

Jim, you might also want to post this email on the preformance list. You will probably get more
assistance that way.

I will try to give you general ideas that you can use to get your answers.

First, It will be helpful to everyone on the list if you post your:

hardware specs
operating systems information
postgresql version
conf files
explain (and if later ask explain analyze) on the offending queries.

The answers that you will be getting will be the following:

advice on maintaining your database i.e. Vaccuming, & analyzing, & clustering for improved
preformance.

advice on query tuning/sql restructuring

advice on seting conf variable and runtime variables

advice on query caeching / transaction on multiple update statements.

and finally advice of hardware restructuring for improved disc i/o.

Also, I should point out that you will be able to determine what you can do with most of these
items on your own by reading the user manual.

hope this is helpful.

Regards,

Richard Broersma