Re: Queries take forever on ported database from MSSQL -> Postgresql (SOLVED)
От | Robert John Shepherd |
---|---|
Тема | Re: Queries take forever on ported database from MSSQL -> Postgresql (SOLVED) |
Дата | |
Msg-id | 000a01c2752c$e8d095f0$f3b0313e@LAIKA обсуждение исходный текст |
Ответ на | Re: Queries take forever on ported database from MSSQL -> Postgresql (Doug McNaught <doug@wireboard.com>) |
Список | pgsql-general |
> 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
В списке pgsql-general по дате отправления: