Horton

Thread

  1. Queries take forever on ported database from MSSQL -> Postgresql

    Robert John Shepherd <robert@reviewer.co.uk> — 2002-10-16T13:30:09Z

    Hi all,

I've spent the last two weeks trying to solve this problem, reading all
the documentation I can lay my hands on, and searching the archives, all
to no avail. Hopefully someone who knows way more about postgresql than
me (not hard) can help me out here.

We are migrating our database from MS-SQL to Postgresql, and in the
process from a dual PIII-600 to a Pentium IV-2.4 based system, which to
my mind I'd have thought (bar the odd query needing a minor rewrite)
would lead to at the very least a slight performance improvement.

Alas what took MS-SQL 500ms to do is taking Postgresql about 140
seconds, obviously completely unacceptable. Even the simplest queries
seem to take far longer than I expected them to. :/

Yes I've got a load of indexes (which btw I have tried deleting and
recreating to no avail), in fact every field that uses an integer is
indexed in every table (I only do ORDER BYs and JOINs on integer
fields), run vacuum analyze countless times, etc, etc.

JOINs appear to be the worst offenders, with those on two tables or more
crippling the box for minutes at a time. CPU usage is always 92%+ when
trying to process these queries. Also when I run EXPLAIN on a query the
highest cost items in the execution plan are pretty much always Seq
Scans which don't use indexes.

The db has been transferred (complete with indexes and foreign keys)
using pgAdmin II and the pgMigration tools, both v1.3.82beta. Although
the migration tool claimed to encounter an error, and roll back the
whole migration process, from reading the logs (and viewing the
database) there was no actual error and everything appears to be there.

The database is not what I would consider large, only a few hundred MB.

I've changed the following (only) in the config file to give it more
memory which it seems to use, although none of this effects the speed:

sort_mem = 16384
shared_buffers = 16384

I'm running OpenBSD 3.1-stable, Postgresql 7.1.3 (from the ports
collection, although I've tried installing 7.2.3 and it has all the same
issues, so I went back to 7.1.3 just in case it fixed it), and this is a
completely clean brand new build with nothing else on it.

HELP!


Yours Unwhettedly,
Robert John Shepherd.

Editor
DVD REVIEWER
The UK's BIGGEST Online DVD Magazine
http://www.dvd.reviewer.co.uk

For a copy of my Public PGP key, email: pgp@robertsworld.org.uk

  2. Re: Queries take forever on ported database from MSSQL -> Postgresql

    Doug McNaught <doug@wireboard.com> — 2002-10-16T13:39:34Z

    "Robert John Shepherd" <robert@reviewer.co.uk> writes:

[snip...]

> JOINs appear to be the worst offenders, with those on two tables or more
> crippling the box for minutes at a time. CPU usage is always 92%+ when
> trying to process these queries. Also when I run EXPLAIN on a query the
> highest cost items in the execution plan are pretty much always Seq
> Scans which don't use indexes.

Is it possible that you are being bitten by the data type promotion
issues?  Eg, if you have an indexed int2 column, and a query like

select * from foo where int2col = 45;

the "45" will be treated as an int4 and the index won't be used.
Changing the WHERE clause to "where int2col = '45'" will use the
index.

This is (I think) somewhat improved in the upcoming 7.3 (now in beta)
and is slated to be mostly fixed in 7.4 IIRC.

If this is not the issue, post schemas and EXPLAIN output and someone
will likely try to help.

-Doug

  3. Re: Queries take forever on ported database from MSSQL -> Postgresql

    Shridhar Daithankar <shridhar_daithankar@persistent.co.in> — 2002-10-16T14:16:01Z

    On 16 Oct 2002 at 14:30, Robert John Shepherd wrote:
> Alas what took MS-SQL 500ms to do is taking Postgresql about 140
> seconds, obviously completely unacceptable. Even the simplest queries
> seem to take far longer than I expected them to. :/

Can you post your (audited) schemas and queries related to that, That might 
give a clue..

HTH

Bye
 Shridhar

--
Gordon's Law:	If you think you have the solution, the question was poorly 
phrased.

  4. Re: Queries take forever on ported database from MSSQL -> Postgresql (SOLVED)

    Robert John Shepherd <robert@reviewer.co.uk> — 2002-10-16T15:58:20Z

    > Is it possible that you are being bitten by the data type promotion
> issues?  Eg, if you have an indexed int2 column, and a query like
> 
> select * from foo where int2col = 45;
> 
> the "45" will be treated as an int4 and the index won't be used.
> Changing the WHERE clause to "where int2col = '45'" will use the
> index.

Doug you're my hero. This is kind of what was happening, but not for the
reasons you specified.

When pgAdmin's migration wizard imported all the data, it converted all
int4 fields in the MS-SQL database to int8's, however ALL the primary
keys where kept as int4 fields.

Whenever it came to do a join, it obviously did not use any indexes
since it was always joining on int8 to int4 fields. I reimported the
entire db making altering the int4 type maps int8s which STILL left the
primary keys as int4s and the rest as int8s, so I then did the whole
thing yet again and this time changed all of them to int4s.

Queries are now running faster than they are on the old box (bar the
obviously slow initial execution of a query whilst it loads indexes etc
to memory), and all using indexes as they should be.

Glad after all this it wasn't a glaring error on my part, and what might
be an issue with the migration wizard.


Yours Unwhettedly,
Robert John Shepherd.

Editor
DVD REVIEWER
The UK's BIGGEST Online DVD Magazine
http://www.dvd.reviewer.co.uk

For a copy of my Public PGP key, email: pgp@robertsworld.org.uk