How can I avoid full scan big PostGIS table in QGIS Planned maintenance scheduled April 17/18, 2019 at 00:00UTC (8:00pm US/Eastern) Announcing the arrival of Valued Associate #679: Cesar Manara Unicorn Meta Zoo #1: Why another podcast?Is PostGIS doing a full table scan?How can I prevent ogr2ogr from trying to get the SRID from PostGIS?PostGIS - interfacing with CADCORP 7.1 and QGIS - user 'postgres' can import, but no other roles canDisplaying data from a partitioned PostgreSQL / PostGIS table is unacceptably slow in QGISDuplicate PostGIS view entries in QGISImprove speed of PostGIS query for counting locationsPostGIS (Postgres 9.6) - order bounding box query results by date (large table)QGIS point sample tool faster than in PostGIS - how to improve query?PostGIS Optimising ST_ContainsOptimising a PostGIS table
Do square wave exist?
Compare a given version number in the form major.minor.build.patch and see if one is less than the other
Has negative voting ever been officially implemented in elections, or seriously proposed, or even studied?
What do you call the main part of a joke?
An adverb for when you're not exaggerating
How to find all the available tools in mac terminal?
Closed form of recurrent arithmetic series summation
Where are Serre’s lectures at Collège de France to be found?
What is the meaning of the simile “quick as silk”?
Is it common practice to audition new musicians one-on-one before rehearsing with the entire band?
When the Haste spell ends on a creature, do attackers have advantage against that creature?
What does the "x" in "x86" represent?
When a candle burns, why does the top of wick glow if bottom of flame is hottest?
Is grep documentation wrong?
Is it fair for a professor to grade us on the possession of past papers?
How to react to hostile behavior from a senior developer?
What is the longest distance a player character can jump in one leap?
How could we fake a moon landing now?
2001: A Space Odyssey's use of the song "Daisy Bell" (Bicycle Built for Two); life imitates art or vice-versa?
Can anything be seen from the center of the Boötes void? How dark would it be?
Maximum summed powersets with non-adjacent items
Most bit efficient text communication method?
Withdrew £2800, but only £2000 shows as withdrawn on online banking; what are my obligations?
Should I use a zero-interest credit card for a large one-time purchase?
How can I avoid full scan big PostGIS table in QGIS
Planned maintenance scheduled April 17/18, 2019 at 00:00UTC (8:00pm US/Eastern)
Announcing the arrival of Valued Associate #679: Cesar Manara
Unicorn Meta Zoo #1: Why another podcast?Is PostGIS doing a full table scan?How can I prevent ogr2ogr from trying to get the SRID from PostGIS?PostGIS - interfacing with CADCORP 7.1 and QGIS - user 'postgres' can import, but no other roles canDisplaying data from a partitioned PostgreSQL / PostGIS table is unacceptably slow in QGISDuplicate PostGIS view entries in QGISImprove speed of PostGIS query for counting locationsPostGIS (Postgres 9.6) - order bounding box query results by date (large table)QGIS point sample tool faster than in PostGIS - how to improve query?PostGIS Optimising ST_ContainsOptimising a PostGIS table
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;
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.
qgis postgis
asked Apr 10 at 13:06
JaakLJaakL
1,540916
|
show 1 more comment
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.
qgis postgis
asked Apr 10 at 13:06
JaakLJaakL
1,540916
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
Apr 10 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
Apr 10 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
Apr 11 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 querySELECT 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
Apr 12 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
Apr 12 at 6:32
|
show 1 more comment
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.
qgis postgis
asked Apr 10 at 13:06
JaakLJaakL
1,540916
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.
qgis postgis
qgis postgis
asked Apr 10 at 13:06
JaakLJaakL
1,540916
asked Apr 10 at 13:06
JaakLJaakL
1,540916
edited Apr 11 at 6:48
JaakL
asked Apr 10 at 13:06
JaakLJaakL
1,540916
asked Apr 10 at 13:06
JaakLJaakL
1,540916
asked Apr 10 at 13:06
JaakLJaakL
1,540916
1,540916
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
Apr 10 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
Apr 10 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
Apr 11 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 querySELECT 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
Apr 12 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
Apr 12 at 6:32
|
show 1 more comment
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
Apr 10 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
Apr 10 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
Apr 11 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 querySELECT 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
Apr 12 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
Apr 12 at 6:32
1
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
Apr 10 at 13:14
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
Apr 10 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
Apr 10 at 13:51
@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
Apr 10 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
Apr 11 at 9:18
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
Apr 11 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
Apr 12 at 5:41
@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
Apr 12 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
Apr 12 at 6:32
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
Apr 12 at 6:32
|
show 1 more comment
1 Answer
1
active
oldest
votes
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.
- Assuming you data come from "my_big_table"
- 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 usingrow_number() OVER () - Create a table "my_geo_table" with same colum definition
- Set up all needed pk/index/spatial index on "my_geo_table"
- 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.
answered Apr 12 at 9:51
MarHoffMarHoff
1,155514
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 tableSELECT 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
Apr 12 at 11:12
You’re welcome!
– MarHoff
Apr 12 at 11:21
add a comment |
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
);
);
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%2f318369%2fhow-can-i-avoid-full-scan-big-postgis-table-in-qgis%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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.
- Assuming you data come from "my_big_table"
- 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 usingrow_number() OVER () - Create a table "my_geo_table" with same colum definition
- Set up all needed pk/index/spatial index on "my_geo_table"
- 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.
answered Apr 12 at 9:51
MarHoffMarHoff
1,155514
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 tableSELECT 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
Apr 12 at 11:12
You’re welcome!
– MarHoff
Apr 12 at 11:21
add a comment |
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.
- Assuming you data come from "my_big_table"
- 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 usingrow_number() OVER () - Create a table "my_geo_table" with same colum definition
- Set up all needed pk/index/spatial index on "my_geo_table"
- 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.
answered Apr 12 at 9:51
MarHoffMarHoff
1,155514
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 tableSELECT 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
Apr 12 at 11:12
You’re welcome!
– MarHoff
Apr 12 at 11:21
add a comment |
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.
- Assuming you data come from "my_big_table"
- 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 usingrow_number() OVER () - Create a table "my_geo_table" with same colum definition
- Set up all needed pk/index/spatial index on "my_geo_table"
- 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.
answered Apr 12 at 9:51
MarHoffMarHoff
1,155514
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.
- Assuming you data come from "my_big_table"
- 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 usingrow_number() OVER () - Create a table "my_geo_table" with same colum definition
- Set up all needed pk/index/spatial index on "my_geo_table"
- 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.
answered Apr 12 at 9:51
MarHoffMarHoff
1,155514
edited Apr 12 at 10:00
answered Apr 12 at 9:51
MarHoffMarHoff
1,155514
answered Apr 12 at 9:51
MarHoffMarHoff
1,155514
answered Apr 12 at 9:51
MarHoffMarHoff
1,155514
1,155514
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 tableSELECT 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
Apr 12 at 11:12
You’re welcome!
– MarHoff
Apr 12 at 11:21
add a comment |
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 tableSELECT 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
Apr 12 at 11:12
You’re welcome!
– MarHoff
Apr 12 at 11:21
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
Apr 12 at 11:12
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
Apr 12 at 11:12
You’re welcome!
– MarHoff
Apr 12 at 11:21
You’re welcome!
– MarHoff
Apr 12 at 11:21
add a comment |
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%2f318369%2fhow-can-i-avoid-full-scan-big-postgis-table-in-qgis%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
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
Apr 10 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
Apr 10 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
Apr 11 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
Apr 12 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
Apr 12 at 6:32