4

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:

Locator bar

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]
FROM thetable
WHERE ((((textcolumn1) ILIKE '%search string%')
OR ((textcolumn2) ILIKE '%search string%'))
OR ((textcolumn3) ILIKE '%search string%'))
LIMIT 30

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".

MMQGIS Search

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.


Is there a current performant solution to easily search a layer, with many features and many attributes, for certain attributes?

  • Which version of QGIS3? I'm on 3.4.5 and just tried a similar size/complexity of table and it worked quite quickly. The code seems to be this:- github.com/qgis/QGIS/blob/… . It seems to query all fields, matching feature at first match, and stops at 30 rows. The code looks as if it doesn't go back to the back-end, but it may be having to fetch from the database if you can't fit the whole table in RAM? Can you see any back-end queries arriving at postgres at all (e.g. using PGAdmin?) – Steven Kay Apr 5 at 18:57
  • @StevenKay 3.4.5 also. I should hope it goes to the backend, the whole table would take a while to transfer over the network. (I recently ran into the opposite problem with a different layer--QGIS reloads all PostGIS features when you pan the map.) Watching the PGAdmin dashboard, a session is "active" while the search is ongoing, and stops when the search is finished. Thanks for the link to the source code, that helps explain what it's doing. – Dan Getz Apr 5 at 19:26
  • 1
    @StevenKay unfortunately I haven't been able to spy on the queries themselves. I'm logging long queries but it's just showing the "FETCH FORWARD FROM" cursor instead of the query for that cursor. I wrote a query similar to what that code appears to be writing, and I can see that the planner won't want to use indexes if any of the string columns (of which this table has many!) doesn't have a trigram index on it. However, the query still only takes a few seconds, so I guess it's not the whole story. – Dan Getz Apr 5 at 19:44
  • 1
    @prusswan It's the "NYS Tax Parcels" file from this link. I'm using a PostGIS server on my local network purposefully to avoid storing it as a local layer. Querying the server directly with a more limited query takes milliseconds, as I mentioned. – Dan Getz Apr 17 at 12:35
  • 1
    @DanGetz okay, then enabling all query logging on Postgres side will tell you exactly how QGIS is interacting with the database. Whether you can speed up this interaction would be another issue – prusswan Apr 18 at 1:45
0

You can define the layers available to the locator widget for searching, via Project -> Settings -> Data Sources -> de-select layers from Searchable column. Not sure if this configuration is respected by Search Layers plugin however.

  • Thanks, but I already knew that and was already searching only on the layer when using the locator, by selecting the layer desired and using "f" instead of "af". The problem isn't the number of layers, it's the number of attributes on the layer I've selected. – Dan Getz Apr 17 at 12:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.