If you have a large table with a lot of nulls in a field then max() will perform poorly, presumably because it will backward-scan past all the nulls. The same is true of the old max hack (SELECT ... WHERE field IS NOT NULL ORDER BY field DESC LIMIT 1). However, if you
CREATE INDEX indexname ON table(field) WHERE field IS NOT NULL the hack will use that index, but max() won't. ISTM that's a bug. Also, isn't there some way to expedite the backwards scan in this case? If I add a constraint such as field < 1000, it seems that the database will go directly to wherever it needs to start the reverse scan, but it can't do this with IS NOT NULL. Is this due to NOT NULL not being an operator? -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
