Neil Conway <[EMAIL PROTECTED]> writes: > GetDomainConstraints() looks fairly expensive, so it would be nice to do > some caching. What would the best way to implement this be? I had > thought that perhaps the typcache would work, but there seems to be no > method to flush stale typcache data. Perhaps we could add support for > typcache invalidation (via a new sinval message), and then add the > domain constraint information to the typcache. Or is there an easier > way?
Yeah, I had been thinking of exactly the same thing a few months ago after noting that GetDomainConstraints() can be pretty dang slow --- it seemed to be a major bottleneck for Kevin Grittner here: http://archives.postgresql.org/pgsql-hackers/2005-09/msg01135.php Unfortunately the rest of that conversation was unintentionally off-list, but we identified heavy use of pg_constraint_contypid_index as the source of his issue, and I said : Hmm. The only commonly-used code path I can see that would touch : pg_constraint_contypid_index is GetDomainConstraints(), which would be : called (once) during startup of a command that involves a CoerceToDomain : expression node. So if the heavily-hit table has any domain-type : columns, it's possible that a steady stream of inserts or updates : could have kept that index tied up. : : It might be worth introducing a system cache that could be used to : extract the constraints for a domain without going to the catalog for : every single command. There's been very little work done to date on : optimizing operations on domains :-( The lack of typcache invalidation is something that will eventually bite us in other ways, so we need to add the facility anyway. We don't really need a "new sinval message", as an inval on the pg_type row will serve perfectly well --- what we need is something comparable to CacheInvalidateRelcache() to cause such a message to be sent when we haven't actually changed the pg_type row itself. Do you want to work on this? I can if you don't. BTW, in connection with the lookup_rowtype_tupdesc fiasco, it's pretty obvious that any data structure returned by this function will need to be either copied or reference-counted. regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
