I'm using QGIS 3 with my data in a PostGIS server on the same local network. I want to quickly search for a feature based on a certain attribute or attributes of my layer. This layer has ±2 million features and 74 columns. I don't want to search in most of the columns and would be happy specifying which columns to search in.
I've added indexes to the columns that I want to search on. Searching via a SQL query returns practically immediately (±100ms).
I've tried a few methods of searching in QGIS, but keep running into performance much slower than it could be:
I've tried the locator bar per this answer. I select the layer I want in the Layer panel, then type
f search string into the locator bar. I see some results quickly when I start typing the search string, but when I type more it stops showing results, and I have to wait 15-30 seconds before it finally finds the match.
Logging SQL statements on my server, I can see why: it creates a query of the type
SELECT [each of the columns]
WHERE ((((textcolumn1) ILIKE '%search string%')
OR ((textcolumn2) ILIKE '%search string%'))
OR ((textcolumn3) ILIKE '%search string%'))
I can speed this up on a table with a small number of attributes, by adding trigram matching indexes with the
trgm module, but with a large number of attributes, this becomes a sequential scan. It seems Postgres doesn't realize that, by limiting to 30 results, it could search the indexed columns first, then only look at the others if it didn't get to 30. Though even if Postgres could be that clever, that wouldn't solve my problem if I enter a search term that matches only one result!
Much better would be if I could specify which attributes to search.
Search Layers plugin
When I use the Search Layers plugin, I get the match(es) practically immediately if I specify both the layer and the attribute. Great! But if it's set to
"<All Layers>", or
"<All Fields>", it takes a long time to return anything, and, if I close the plugin window and open it later, it defaults back to
"<All Layers>". I don't want to have to change the settings every time I open the window to avoid this "gotcha".
I tried the MMQGIS Plugin from this answer to the same question. It uses a panel that remembers the previous settings and asks for what attribute(s) to search. But instead of running a SQL query to search those columns, it runs a SQL query to load the entire table into local memory and then searches the local memory copy. This is unusable for the use case of large tables - takes much longer than Locator bar, and freezes QGIS while it's running.
Question: Is there a current performant solution to easily search a layer, with many features and many attributes, for certain attributes?