FWD » Core Development » Database

Different database implementations offer various performance tuning configuration settings. For example, PostgreSQL offers seq_page_cost and random_page_cost to nudge the query planner to prefer index vs. table scans. Other database implementations may offer other mechanisms. We need to investigate these options across more applications and see if we can find some settings that will help database performance generally.

For certain work flows in one customer application, we have found that changing seq_page_cost and random_page_cost for a PostgreSQL cluster in a certain way could bias the query planner toward index selection over table scanning and would cause a better performance outcome. However, in the same application, a slightly different tuning of these parameters resulted in a worse query plan. We need further testing to figure out the best balance of these settings, taking into account the hardware environment in which the database will be running, among other factors.

Generally speaking, it seems weighing a query planner more toward using indices than table scans would make sense for an application converted from Progress, where indices are so heavily relied upon for performance. However, this may not be a "one-size-fits-all" solution, so we need to test a variety of applications.

The above example mentions PostgreSQL, but we will want to understand what tuning options are available to us on MariaDB and SQL Server as well.