I gave a presentation about transactional metadata queries at last BeeCon which included considerations for performance (video recording). In Alfresco 5.1.g there are actually two sets of indices for TMQ - one originates from Alfresco 4.2 (the one you referenced) and another was introduced with 5.1. For adequate performance both must be enabled and their indices must be added to the database schema. For very specific use cases additional, business-oriented indices can be useful. I cannot see anything close to a 1000x worse performance in transactional metadata queries when they are used correctly. It is true that there are some scaling issues (due to the design) when you attempt to retrieve results for a very unselective query or attempt to retrieve page 20 or later.
Your PostgreSQL server may need to be tuned if you are (still) using the default installation done by Alfresco because that will have the configuration for memory-related parameters (e.g. shared_buffers) set to the factory default which is not suitable for scaling production use.
Thanks Axel for your response. I've already watched your (great) presentation about TMQ.
When you say "both must be enabled and their indices must be added to the database schema" does it means that I've some action to perform on a 5.1.g default setup ? (As indexes are already defined in DB)
As I mentioned in my presentation, by default, none of the indices are added in an Alfresco installation and action must be taken. That applies to 5.1.g as well. I don't know why you have one set of indices added - I would assume because at some point you had actually enabled that patch for at least one start of the system.
Yes, it looks like the recent version now include these patches by default in the first installation and the runtime check has also been adapted. That is good to know and I had not noticed that before.