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 jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
