Searches on layers with many attributes are slow despite having indexes on the attributes I want to search. How can I speed this up? The 2019 Stack Overflow Developer Survey Results Are InSearching feature in QGIS?Full Text Search in ArcSDEQGIS 2.10.1 & SQL Server 2014How to refresh or update a vrt layer in Qgis?Cannot activate the Feature Search in ArcGIS Online?Should the QGIS “DB Manager” have Microsoft SQL Server as an option?Search MXDs with specific dataSource layer using PythonLimit ST_DWithin() to first candidate foundMS SQL Layer - Losing LineStrings (Polyline) from SQL Server to QGISHow to configure the search in a qgis2web map?Search feature property in a leaflet layerGroup not producing a result
How much of the clove should I use when using big garlic heads?
When should I buy a clipper card after flying to Oakland?
Can withdrawing asylum be illegal?
Is "plugging out" electronic devices an American expression?
How to translate "being like"?
What is the meaning of Triage in Cybersec world?
Is patent abuse being used to block high-lumen headlamps that run on standard batteries?
A word that means fill it to the required quantity
How can I define good in a religion that claims no moral authority?
Portfast on Trunk port
Why are there uneven bright areas in this photo of black hole?
Pokemon Turn Based battle (Python)
Why is ParallelDo slower than Do?
Is it ok to offer lower paid work as a trial period before negotiating for a full-time job?
Can an undergraduate be advised by a professor who is very far away?
Worn-tile Scrabble
Why doesn't shell automatically fix "useless use of cat"?
Old scifi movie from the 50s or 60s with men in solid red uniforms who interrogate a spy from the past
Merge two greps into single one
Can we generate random numbers using irrational numbers like π and e?
What to do when moving next to a bird sanctuary with a loosely-domesticated cat?
Sums of normal random variables
Why does the nucleus not repel itself?
"as much details as you can remember"
Searches on layers with many attributes are slow despite having indexes on the attributes I want to search. How can I speed this up?
The 2019 Stack Overflow Developer Survey Results Are InSearching feature in QGIS?Full Text Search in ArcSDEQGIS 2.10.1 & SQL Server 2014How to refresh or update a vrt layer in Qgis?Cannot activate the Feature Search in ArcGIS Online?Should the QGIS “DB Manager” have Microsoft SQL Server as an option?Search MXDs with specific dataSource layer using PythonLimit ST_DWithin() to first candidate foundMS SQL Layer - Losing LineStrings (Polyline) from SQL Server to QGISHow to configure the search in a qgis2web map?Search feature property in a leaflet layerGroup not producing a result
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;
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. I've tried a couple methods, but keep running into performance much slower than it could be.
When I use the locator bar per this answer, I see some results quickly when I start typing the value, but when I type more it stops showing results, and I have to wait ±30 seconds before it finally finds the match.
When I use the Search Layers plugin, I get the match(es) practically immediately if I specify both the layer and the attribute. If it's set to "<All Layers>", or "<All Fields>", it takes a long time to return anything. However, if I close the plugin window and open it later, it defaults back to "<All Layers>".
This layer has ±2 million features, and 74 columns. I've added indexes to the columns that I want to search on. Searching via a SQL query returns practically immediately (±100ms).
I wonder if it's slowing down because it's trying to search irrelevant attributes? The SQL server is processing during the whole time the search is ongoing. What can I do to speed this up? Is there some way to limit which attributes get searched, or ensure that the attributes I usually intend to search get checked first? Or is there another search method or plugin that will let me quickly search relevant attributes, without defaulting to searching all 74?
qgis postgis qgis-3 search
asked Apr 5 at 17:56
Dan GetzDan Getz
1165
New contributor
Dan Getz is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
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. I've tried a couple methods, but keep running into performance much slower than it could be.
When I use the locator bar per this answer, I see some results quickly when I start typing the value, but when I type more it stops showing results, and I have to wait ±30 seconds before it finally finds the match.
When I use the Search Layers plugin, I get the match(es) practically immediately if I specify both the layer and the attribute. If it's set to "<All Layers>", or "<All Fields>", it takes a long time to return anything. However, if I close the plugin window and open it later, it defaults back to "<All Layers>".
This layer has ±2 million features, and 74 columns. I've added indexes to the columns that I want to search on. Searching via a SQL query returns practically immediately (±100ms).
I wonder if it's slowing down because it's trying to search irrelevant attributes? The SQL server is processing during the whole time the search is ongoing. What can I do to speed this up? Is there some way to limit which attributes get searched, or ensure that the attributes I usually intend to search get checked first? Or is there another search method or plugin that will let me quickly search relevant attributes, without defaulting to searching all 74?
qgis postgis qgis-3 search
asked Apr 5 at 17:56
Dan GetzDan Getz
1165
New contributor
Dan Getz is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
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
@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
add a comment |
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. I've tried a couple methods, but keep running into performance much slower than it could be.
When I use the locator bar per this answer, I see some results quickly when I start typing the value, but when I type more it stops showing results, and I have to wait ±30 seconds before it finally finds the match.
When I use the Search Layers plugin, I get the match(es) practically immediately if I specify both the layer and the attribute. If it's set to "<All Layers>", or "<All Fields>", it takes a long time to return anything. However, if I close the plugin window and open it later, it defaults back to "<All Layers>".
This layer has ±2 million features, and 74 columns. I've added indexes to the columns that I want to search on. Searching via a SQL query returns practically immediately (±100ms).
I wonder if it's slowing down because it's trying to search irrelevant attributes? The SQL server is processing during the whole time the search is ongoing. What can I do to speed this up? Is there some way to limit which attributes get searched, or ensure that the attributes I usually intend to search get checked first? Or is there another search method or plugin that will let me quickly search relevant attributes, without defaulting to searching all 74?
qgis postgis qgis-3 search
asked Apr 5 at 17:56
Dan GetzDan Getz
1165
New contributor
Dan Getz is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
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. I've tried a couple methods, but keep running into performance much slower than it could be.
When I use the locator bar per this answer, I see some results quickly when I start typing the value, but when I type more it stops showing results, and I have to wait ±30 seconds before it finally finds the match.
When I use the Search Layers plugin, I get the match(es) practically immediately if I specify both the layer and the attribute. If it's set to "<All Layers>", or "<All Fields>", it takes a long time to return anything. However, if I close the plugin window and open it later, it defaults back to "<All Layers>".
This layer has ±2 million features, and 74 columns. I've added indexes to the columns that I want to search on. Searching via a SQL query returns practically immediately (±100ms).
I wonder if it's slowing down because it's trying to search irrelevant attributes? The SQL server is processing during the whole time the search is ongoing. What can I do to speed this up? Is there some way to limit which attributes get searched, or ensure that the attributes I usually intend to search get checked first? Or is there another search method or plugin that will let me quickly search relevant attributes, without defaulting to searching all 74?
qgis postgis qgis-3 search
qgis postgis qgis-3 search
asked Apr 5 at 17:56
Dan GetzDan Getz
1165
New contributor
Dan Getz is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked Apr 5 at 17:56
Dan GetzDan Getz
1165
New contributor
Dan Getz is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
edited Apr 5 at 20:43
Dan Getz
asked Apr 5 at 17:56
Dan GetzDan Getz
1165
New contributor
Dan Getz is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked Apr 5 at 17:56
Dan GetzDan Getz
1165
asked Apr 5 at 17:56
Dan GetzDan Getz
1165
1165
New contributor
Dan Getz is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
Dan Getz is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Dan Getz is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
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
@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
add a comment |
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
@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
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
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
@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
@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
@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
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "79"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Dan Getz is a new contributor. Be nice, and check out our Code of Conduct.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fgis.stackexchange.com%2fquestions%2f317927%2fsearches-on-layers-with-many-attributes-are-slow-despite-having-indexes-on-the-a%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Dan Getz is a new contributor. Be nice, and check out our Code of Conduct.
Dan Getz is a new contributor. Be nice, and check out our Code of Conduct.
Dan Getz is a new contributor. Be nice, and check out our Code of Conduct.
Dan Getz is a new contributor. Be nice, and check out our Code of Conduct.
Thanks for contributing an answer to Geographic Information Systems Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fgis.stackexchange.com%2fquestions%2f317927%2fsearches-on-layers-with-many-attributes-are-slow-despite-having-indexes-on-the-a%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
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
@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