8

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.

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

Improve this question
9
  • 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
    Commented Apr 5, 2019 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
    Commented Apr 5, 2019 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
    Commented Apr 5, 2019 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
    Commented Apr 17, 2019 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
    Commented Apr 18, 2019 at 1:45

1 Answer 1

Reset to default
2

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.

Update:

Following the update to the question, there are two possibilities:

A - Use the attribute table and filter by field. This works well enough especially if the fields are already indexed.

B - Search across all fields, this is not something a relational database can handle well. However, the idea in https://dba.stackexchange.com/questions/175468/search-all-columns-as-a-single-text-per-row should work, just that this method cannot be used in QGIS directly.

To put B in action:

  1. Add a column named full_text
  2. Concat all the columns (except for shape) as text, and populate full_text:
update nys_tax_parcels_public
set full_text = piv
from
(select p.objectid as pid, array_to_string(array_agg(f.value),'|') as piv from nys_tax_parcels_public p, 
 json_each_text(json_strip_nulls((row_to_json(p)::jsonb - 'shape' - 'full_text')::json)) as f
group by p.objectid) As j
where objectid = pid;
  1. Add the pg_trgm extension and create trigram index on full_text
Improve this answer
5
  • 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
    Commented Apr 17, 2019 at 12:30
  • I think you're missing a step (4) for "B" to work in QGIS: "remove most other text columns". I guess you'd want to leave in something to show up in the search results as the title for the feature.
    – Dan Getz
    Commented Oct 6, 2019 at 12:47
  • @DanGetz depends on your use-case, this is assuming the individual attributes are still needed and not just the concatenated string of values
    – prusswan
    Commented Oct 8, 2019 at 3:24
  • But if you leave the attributes on the table being searched by QGIS, the search will take just as long as before. Like I mentioned in the question, I've already tried adding trgm indices, and while they speed up direct SQL queries, they don't speed up QGIS searches for two main reasons: (1) QGIS searches all text columns, not just indexed ones (2) QGIS searches until it finds at least 30 results; if there are less than 30 possible results, the database must check every text column of every row in the table.
    – Dan Getz
    Commented Oct 8, 2019 at 12:44
  • @DanGetz so you have to avoid using "f" search for this layer (or remove the unneeded columns as you mentioned). You can search the indexed columns by setting them as Display Name ("af"), or use the Search Layers plugin.
    – prusswan
    Commented Oct 9, 2019 at 2:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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