3

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.

Improve this question
6
  • 1
    Not sure if it is the issue or not, but a unique index can still have repeated values if one of them is null (2 nulls are not equal). Try to mark the columns as not null, or declare a composite primary key (which cannot contains null by definition)
    – JGH
    Commented Apr 10, 2019 at 13:14
  • @JGH is it possible to mark columns as not-null or have primary keys for materialized view? my 500M view is really a subselection of another 1+B table.
    – JaakL
    Commented Apr 10, 2019 at 13:51
  • Do you have similar issue when connecting via db_manager using a raw query? It's a workaround but usually db_manager skip some tests and don't require unique id (at the cost of being in read-only mode).
    – MarHoff
    Commented Apr 11, 2019 at 9:18
  • @MarHoff - yes, similar issues. When I select table in db manager then it hangs, and it is explainable as see in database admin expensive query SELECT COUNT(*) FROM "public"."mytable" running. It will take hours in this case. I always use fast table stats, even if it is not accurate to the row.
    – JaakL
    Commented Apr 12, 2019 at 5:41
  • Wait a second it’s now a little bit unclear after re-reading your question. Is mytable_geo a table, a view or a materialized view? It’s transparent from a query standpoint but change a lot of thing for optimization.
    – MarHoff
    Commented Apr 12, 2019 at 6:32

1 Answer 1

Reset to default
2

Ugly quickfix, inspired by my support partner which always point out that materialized view are no yet as mature as we'd like too and old fashion workaround are still useful.

Instead of a materialized view, create a real table refreshed by a view.

  1. Assuming you data come from "my_big_table"
  2. Create a view "my_geo_table_refresh" that contain the actual query/filter from "my_big_table"
    PS: and maybe create a NO NULL id column using row_number() OVER ()
  3. Create a table "my_geo_table" with same colum definition
  4. Set up all needed pk/index/spatial index on "my_geo_table"
  5. Everytime you need to refresh that "old school materialized view" just run that SQL in a single transaction: 
    TRUNCATE TABLE "my_geo_table";
INSERT INTO "my_geo_table" SELECT * FROM "my_geo_table_refresh";
    Of course this is assuming a read only usage and no reference pointing to "my_geo_table" otherwise you would have to be more careful and/or use complex triggers.

Alternatively you could also write FUNCTION/PROCEDURE to refresh the "old school materialized view"
That might be more elegant as no intermediary "my_geo_table_refresh" will show up in the catalog.

Improve this answer
1
  • thanks, this seemed to work. Two commands were needed to make it quick, and avoid one more full scan to get full extent: ANALYZE my_geo_table; -- quite quick, <1 min for 500M+ row table SELECT ST_EstimatedExtent('my_geo_table', 'geom'); -- just check if it gives some extent, very fast, also qis uses this; but only if analyze is done.
    – JaakL
    Commented Apr 12, 2019 at 11:12

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.