PostgreSQL Expression Compiler for Serverside Filtering

In this news item we will discuss the project "PostgreSQL expression compiler" - a project financed by the Swiss QGIS user group.

In QGIS there are various possiblities and user dialogues for filtering data. In general, one has to distinguish between serverside filtering (e.g. in a database) and clientside filtering (QGIS).

While the "provider feature filter" (layer properties dialogue --> "General" --> "Provider feature filter") filters the data already on the provider level, for PostgreSQL layers directly in the database, all other filters happen clientside within QGIS. The latter concerns for example symbology rules, attribute tables and related tables (e.g. 1:n relations). In extreme cases, QGIS fetches all (potentially tens of thousands of records) from the database, only to ignore most of the features, because the symbology rules or filtered attribute tables only need a few rows.

The project "PostgreSQL expression compiler" has the goal to more intelligently make use of serverside filtering for many of the standardized queries (e.g. smaller/bigger, equal, not equal, IS NULL, IN, NOT IN, AND, etc.). In some situations there will be a considerable performance gain, e.g. for symbology rules in smaller map scales that only need features of a certain class or for narrowed down attribute tables or related tables. Filters that go beyond the above listed basic queries are still filtered on the client, as before. The performance gain is thus only for some filters.

This new option is partially implemented in QGIS 2.10 (without the symbology part) and fully in QGIS master to be released as QGIS 2.12. The compiler is currently limited to PostgreSQL/Postgis data sources, as this is the most popular database used in conjunction with QGIS. However, the technique could be used with other database data sources as well, such as Microsoft SQL Server or Oracle. Someone would, however have to do the work or pay a developer to do the job.

The expression compiler is switched off by default und has to be activated under "Settings" -> "Options" -> "Data sources" -> "Execute expressions on postgres server-side if possible". See also attached screenshot.

You can find more information in the blog entry from the developer of this feature, Matthias Kuhn (OpenGIS).