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

"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