First I would like to thanks everybody that helped me so far.
Mr. Wiek, you are right, the stored procedure I've been using has a lot of
problems related with concurrency control. You gave me valuable information
and it's gonna help me making good arguments against this technique.
> Your VACUUM theory is only partial correct. A frequent VACUUM will
> prevent the key table from growing. You'd have to do so very often since
> the excess number of obsolete index entries pointing to dead tuples also
> degrades your performance. Additionally if there is a very low number of
> keys (sequences) in that table, an ANALYZE run might cause the planner
> to go for a sequential scan and ignore the index on the table at which
> point your function will actually cause "two" sequential scan over all
> live and dead tuples of all sequences per call.
About vacuum, when does analyze make planner to go for sequencial scan
instead indexed scan? How can I prevent planner from make this mistake? I
have some tables that have this problem when a SQL is running against their
primaries keys. For these tables, at creation time they are all "Index Scan"
from explain but after first vacuum is ran, they will be "Sequencial Scan"
forever. They are tables with few data, but there are data enough to slow
down the whole application when a sequencial scan is used. Where can I find
more information about it wrong sequencial scan prevention? This is one of
hardier to understand itens about PostgreSQL. Is there some tech docs
available around that you guys would like to suggest?
> If you cannot convice your boss to use sequences, he is a good example
> for why people having difficulties understanding technical issues should
> not assume leadership positions in IT projects.
That's my point of view. But sometimes is very hard to fight against people
that have no arguments but only influence as weapon.
Best Regards,
Vilson