Question

Time between creating indexes and using them effectively

0

Hi, When I add an index on an entity, how much time does it take to be effective. I understand that the size of the table matters, but is the build-index started immediately (Postgres) or maybe postponed for whatever reason? Or do I have to send a REINDEX command manually to Postgres because existing records are never indexed automatically? And building the index might set the table read-only for the time needed. Is that the case here as well? Regards, Paul

asked 2015-03-16

Paul Poetsma

1 answers

Marcel Groeneweg (Synobsys) · Answer 1 · 2015-03-16

During deployment the database is synchronized, during which the index is created. So it is available for use when the application has been started. As fas as I can tell Mendix only uses indices that are maintained during create and update of the table records.

Whether the index is used depends on the requests sent to he database. For small tables the db engine might prefer a table scan (read entire table and pick the record(s) that match the selection) over index access. For thousands or more the index becomes more interesting for the engine. Especially an index that has many different values.