Skip to main content
Geographic Information Systems

Return to Question

Tweeted twitter.com/StackGIS/status/1117940820563451904
added 208 characters in body
Source Link
JaakL
JaakL
  • 2.2k
  • 2
  • 14
  • 21

I try to open big PostGIS table in QGIS, with 500+M rows. I zoom in to small area, and limit zooms, so the query response is expected to be reasonably small. Tested this also directly in DB. The table has indexes, so I can see that raw query is fast. The table has already unique index for two fields ("partition","partition_offset")

Problem: When I add the table as layer, then QGIS hangs. I see in DB a long running query: SELECT count(distinct ("partition","partition_offset"))=count(("partition","partition_offset")) FROM "public"."mytable_geo" and from query planner I see that this does full scan to table, which takes possibly hours in my case. It looks like that QGIS wants to be really sure that my "unique" constraint makes the values really unique. Is there a way to avoid it?

QGIS version: 3.6.1-Noosa

Update: GDAL has parameter checkPrimaryKeyUnicity='1'. If this could be turned to "0" value in QGIS then it should help, but have not figured out how/if this is possible. Cannot find UI option for this.

I try to open big PostGIS table in QGIS, with 500+M rows. I zoom in to small area, and limit zooms, so the query response is expected to be reasonably small. Tested this also directly in DB. The table has indexes, so I can see that raw query is fast. The table has already unique index for two fields ("partition","partition_offset")

Problem: When I add the table as layer, then QGIS hangs. I see in DB a long running query: SELECT count(distinct ("partition","partition_offset"))=count(("partition","partition_offset")) FROM "public"."mytable_geo" and from query planner I see that this does full scan to table, which takes possibly hours in my case. It looks like that QGIS wants to be really sure that my "unique" constraint makes the values really unique. Is there a way to avoid it?

QGIS version: 3.6.1-Noosa

I try to open big PostGIS table in QGIS, with 500+M rows. I zoom in to small area, and limit zooms, so the query response is expected to be reasonably small. Tested this also directly in DB. The table has indexes, so I can see that raw query is fast. The table has already unique index for two fields ("partition","partition_offset")

Problem: When I add the table as layer, then QGIS hangs. I see in DB a long running query: SELECT count(distinct ("partition","partition_offset"))=count(("partition","partition_offset")) FROM "public"."mytable_geo" and from query planner I see that this does full scan to table, which takes possibly hours in my case. It looks like that QGIS wants to be really sure that my "unique" constraint makes the values really unique. Is there a way to avoid it?

QGIS version: 3.6.1-Noosa

Update: GDAL has parameter checkPrimaryKeyUnicity='1'. If this could be turned to "0" value in QGIS then it should help, but have not figured out how/if this is possible. Cannot find UI option for this.

naming
Source Link
Vince
Vince
  • 20.3k
  • 16
  • 48
  • 65

How can I avoid full scan big postgisPostGIS table in QGIS

I try to open big PostGIS table in QGIS, with 500+M rows. I zoom in to small area, and limit zooms, so the query response is expected to be reasonably small. Tested this also directly in DB. The table has indexes, so I can see that raw query is fast. The table has already unique index for two fields ("partition","partition_offset")

Problem: whenWhen I add the table as layer, then QGIS hangs. I see in DB a long running query: SELECT count(distinct ("partition","partition_offset"))=count(("partition","partition_offset")) FROM "public"."mytable_geo" and from query planner I see that this does full scan to table, which takes possibly hours in my case. It looks like that QGIS wants to be really sure that my "unique" constraint makes the values really unique. Is there a way to avoid it?

QGIS version: 3.6.1-Noosa

How can I avoid full scan big postgis table in QGIS

I try to open big PostGIS table in QGIS, with 500+M rows. I zoom in to small area, and limit zooms, so the query response is expected to be reasonably small. Tested this also directly in DB. The table has indexes, so I can see that raw query is fast. The table has already unique index for two fields ("partition","partition_offset")

Problem: when I add the table as layer, then QGIS hangs. I see in DB a long running query: SELECT count(distinct ("partition","partition_offset"))=count(("partition","partition_offset")) FROM "public"."mytable_geo" and from query planner I see that this does full scan to table, which takes possibly hours in my case. It looks like that QGIS wants to be really sure that my "unique" constraint makes the values really unique. Is there a way to avoid it?

QGIS version: 3.6.1-Noosa

How can I avoid full scan big PostGIS table in QGIS

I try to open big PostGIS table in QGIS, with 500+M rows. I zoom in to small area, and limit zooms, so the query response is expected to be reasonably small. Tested this also directly in DB. The table has indexes, so I can see that raw query is fast. The table has already unique index for two fields ("partition","partition_offset")

Problem: When I add the table as layer, then QGIS hangs. I see in DB a long running query: SELECT count(distinct ("partition","partition_offset"))=count(("partition","partition_offset")) FROM "public"."mytable_geo" and from query planner I see that this does full scan to table, which takes possibly hours in my case. It looks like that QGIS wants to be really sure that my "unique" constraint makes the values really unique. Is there a way to avoid it?

QGIS version: 3.6.1-Noosa

Source Link
JaakL
JaakL
  • 2.2k
  • 2
  • 14
  • 21

How can I avoid full scan big postgis table in QGIS

I try to open big PostGIS table in QGIS, with 500+M rows. I zoom in to small area, and limit zooms, so the query response is expected to be reasonably small. Tested this also directly in DB. The table has indexes, so I can see that raw query is fast. The table has already unique index for two fields ("partition","partition_offset")

Problem: when I add the table as layer, then QGIS hangs. I see in DB a long running query: SELECT count(distinct ("partition","partition_offset"))=count(("partition","partition_offset")) FROM "public"."mytable_geo" and from query planner I see that this does full scan to table, which takes possibly hours in my case. It looks like that QGIS wants to be really sure that my "unique" constraint makes the values really unique. Is there a way to avoid it?

QGIS version: 3.6.1-Noosa