Phoenix Kiula wrote: > Ever tried this crap on a table of 10 million records on a live > website, where this query is happening at 3000 times per second? No > such function schtick will match the raw speed of a simpler indexed > query. Or did you mean my index should contain the COALESCE already?
Hmm. My previous response may have been overly grumpy. The point I was *trying* to make is that shoving a username/id and an IP address into a single field is probably not ideal. At least in my experience you pay for this sort of optimisation (if it even works out as an optimisation in the first place) down the track. I have the misfortunate to have to administrate a system full of such multi-use fields, and have developed a real loathing for the approach. If you don't want to store IPs for registered users, I'd use: user_id INTEGER, ip cidr, CONSTRAINT must_have_userstamp CHECK ( user_id IS NOT NULL OR ip IS NOT NULL) ... and yes, I'd use a functional index to look it up, or even a trigger-maintained cache of the text representation if I had to. Then again, I guess I'm lucky enough to work in environments where data integrity and correctness is a priority and the resources available are a good fit to the tasks the database needs to do. -- Craig Ringe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
