> An often used sql query is fast on mysql (indexed) but unbearably horribly > slow on postgresql once dbmail is full of a lot of messages (400,000 and > maybe a lot less.) Particularly easy to feel when using squirrelmail. > Postgresql cannot and does not use indexes on a min() or max() function. > Blazingly fast performance in postgresql is restored by using the current > value of the next sequence instead of max()+1 in the query.
Nextval is a bad idea since it increments the sequence counter. Instead of SELECT max(columnname) FROM tablename; use: SELECT columnname FROM tablename ORDER BY columnname DESC LIMIT 1; similarly SELECT columnname FROM tablename ORDER BY columnname ASC LIMIT 1; for min(). This btw,. is indexable on both MySQL and PostgreSQL. ... John
