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

jimbrown32rb@yahoo.com (Jim Brown) writes:
> 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.
>
> Help is absolutely appreciated.

First thing to do...

Connect to that database, and run the command:

  VACUUM ANALYZE VERBOSE;

This will run for a while, listing the various tables that it
processes.

It is highly likely that this will have a significant impact on many
of the badly-performing queries that you are encountering, either
because:
 a) It cleans out some outdated data, or
 b) It provides the query optimizer legitimate statistics to
    correctly evaluate a fast way of running the queries.

That's about the first place to go.

If there are queries that are unexpectedly slow, posting query plans
on the .performance list may allow people to provide some help.

You can get details about the query plan via prefacing your query with
the keywords:

  EXPLAIN ANALYZE

Thus...

  EXPLAIN ANALYZE select this, that, other from thistab, thattab
   where thistab.trans_on between '2006-01-01' and '2006-02-01' and
   thattab.id = thistab.id;
--
let name="cbbrowne" and tld="acm.org" in String.concat "@" [name;tld];;
http://cbbrowne.com/info/nonrdbms.html
Rules  of the  Evil Overlord  #202. "All  crones with  the  ability to
prophesy  will be  given  free facelifts,  permanents, manicures,  and
Donna  Karan   wardrobes.  That  should  pretty   well  destroy  their
credibility." <http://www.eviloverlord.com/>