I'm no postgres wizard, but aren't the type cast performance issues like this resolved with postgres 8? Running dbmail 1.2, we turned on imap and it performed horribly with squirrelmail, for precicesly that reason (ie. the queries sent didn't themselves do a typecast). After a little looking into the problem, we needed to either "fix" dbmail queries or we found postgres 8 solved that issue; we decided to upgrade postgres (from 7.4.x) and it did indeed give a tremendous performance improvement (because it could then use indexes).
Maybe it's a different issue, or maybe the int8 vs. int4 thing is in addition to the above, but it sure sounds similar... Jesse On Mon, 2007-08-06 at 10:35 +0200, Marc Dirix wrote: > Op 5-aug-2007, om 23:14 heeft Aaron Stone het volgende geschreven: > > > The folks at IC&S coded for 64-bit id numbers very early on. *shrug* > > Never considered it to be a problem. Why would it be a severe penalty? > > > > It is because both indexes work better with normal INT, and tables > are better optimized. (In Postgres that is). > > This text explains about indexing (copied from http:// > www.thescripts.com/forum/thread400290.html) > > Postgresql has a really need feature that allows users to define their > own types. Yeah, cool. But, it means that the parser is not much > smarter about coercing an int4 to an int8 than it is about coercing a > custom type (hex, foobar, etc...) from one to another. What this means > too you, the user, is that: > > create table test (id int8, info text); > <insert 10,000 rows> > select * from test where id=456; > > will result in a sequential scan. Why? Because the default integer type > is int4, and your id field is int8. Cast the value to int8, and watch it > use an index scan: > > select * From test where id=cast(456 as int8); > > However changing user_id and mailbox_id to INT4 will have the same > resullt! > > Marc > _______________________________________________ > Dbmail-dev mailing list > [email protected] > http://twister.fastxs.net/mailman/listinfo/dbmail-dev -- Jesse Norell Kentec Communications, Inc. [EMAIL PROTECTED] _______________________________________________ Dbmail-dev mailing list [email protected] http://twister.fastxs.net/mailman/listinfo/dbmail-dev
