Queries take forever on ported database from MSSQL -> Postgresql
От | Robert John Shepherd |
---|---|
Тема | Queries take forever on ported database from MSSQL -> Postgresql |
Дата | |
Msg-id | 000001c27518$35f2ba80$f3b0313e@LAIKA обсуждение исходный текст |
Ответы |
Re: Queries take forever on ported database from MSSQL -> Postgresql
|
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: