PostgreSQL insert into too many referenced table
I have a simple table with a primary key ID and other columns that are not so interesting. The data there is not so much, like several thousands records. However there too many constraints on this table. I mean it is referenced by other tables (more than 200) with foreign key to the ID column. And every time I tried to insert something into it, it checks all the constraints and takes around 2-3 secs for every insert to complete. There are btree indexes on all tables, so the query planner uses index scans as well as sequential ones.
My question is there any option or something that I can apply in order to speed up these inserts. I tried to disable the sequential scans and use index scans only, but this didn't help. The partitioning wouldn't be helpful as well, I think. So please advise.
The version of psql is 10.0.
Thank you!
postgresql optimization
asked Nov 10 '18 at 8:23
Jivko Angelov
422
add a comment |
I have a simple table with a primary key ID and other columns that are not so interesting. The data there is not so much, like several thousands records. However there too many constraints on this table. I mean it is referenced by other tables (more than 200) with foreign key to the ID column. And every time I tried to insert something into it, it checks all the constraints and takes around 2-3 secs for every insert to complete. There are btree indexes on all tables, so the query planner uses index scans as well as sequential ones.
My question is there any option or something that I can apply in order to speed up these inserts. I tried to disable the sequential scans and use index scans only, but this didn't help. The partitioning wouldn't be helpful as well, I think. So please advise.
The version of psql is 10.0.
Thank you!
postgresql optimization
asked Nov 10 '18 at 8:23
Jivko Angelov
422
Could you give the definitions for some of the tables and the fkey constraints involved and also the insert query you perform and the explain analyze output for that query. As I currently understand how your tables look there shouldn't be a problem as the constraints would only need checking on delete or update not on insert.
– Eelke
Nov 10 '18 at 8:31
Here is the table in question:
– Jivko Angelov
Nov 10 '18 at 8:43
Constraint are different from references..And what's constraint? is it just foreign key or there are check constraint?
– dwir182
Nov 10 '18 at 22:21
add a comment |
I have a simple table with a primary key ID and other columns that are not so interesting. The data there is not so much, like several thousands records. However there too many constraints on this table. I mean it is referenced by other tables (more than 200) with foreign key to the ID column. And every time I tried to insert something into it, it checks all the constraints and takes around 2-3 secs for every insert to complete. There are btree indexes on all tables, so the query planner uses index scans as well as sequential ones.
My question is there any option or something that I can apply in order to speed up these inserts. I tried to disable the sequential scans and use index scans only, but this didn't help. The partitioning wouldn't be helpful as well, I think. So please advise.
The version of psql is 10.0.
Thank you!
postgresql optimization
asked Nov 10 '18 at 8:23
Jivko Angelov
422
I have a simple table with a primary key ID and other columns that are not so interesting. The data there is not so much, like several thousands records. However there too many constraints on this table. I mean it is referenced by other tables (more than 200) with foreign key to the ID column. And every time I tried to insert something into it, it checks all the constraints and takes around 2-3 secs for every insert to complete. There are btree indexes on all tables, so the query planner uses index scans as well as sequential ones.
My question is there any option or something that I can apply in order to speed up these inserts. I tried to disable the sequential scans and use index scans only, but this didn't help. The partitioning wouldn't be helpful as well, I think. So please advise.
The version of psql is 10.0.
Thank you!
postgresql optimization
postgresql optimization
asked Nov 10 '18 at 8:23
Jivko Angelov
422
asked Nov 10 '18 at 8:23
Jivko Angelov
422
asked Nov 10 '18 at 8:23
Jivko Angelov
422
asked Nov 10 '18 at 8:23
Jivko Angelov
422
asked Nov 10 '18 at 8:23
Jivko Angelov
422
422
Could you give the definitions for some of the tables and the fkey constraints involved and also the insert query you perform and the explain analyze output for that query. As I currently understand how your tables look there shouldn't be a problem as the constraints would only need checking on delete or update not on insert.
– Eelke
Nov 10 '18 at 8:31
Here is the table in question:
– Jivko Angelov
Nov 10 '18 at 8:43
Constraint are different from references..And what's constraint? is it just foreign key or there are check constraint?
– dwir182
Nov 10 '18 at 22:21
add a comment |
Could you give the definitions for some of the tables and the fkey constraints involved and also the insert query you perform and the explain analyze output for that query. As I currently understand how your tables look there shouldn't be a problem as the constraints would only need checking on delete or update not on insert.
– Eelke
Nov 10 '18 at 8:31
Here is the table in question:
– Jivko Angelov
Nov 10 '18 at 8:43
Constraint are different from references..And what's constraint? is it just foreign key or there are check constraint?
– dwir182
Nov 10 '18 at 22:21
Could you give the definitions for some of the tables and the fkey constraints involved and also the insert query you perform and the explain analyze output for that query. As I currently understand how your tables look there shouldn't be a problem as the constraints would only need checking on delete or update not on insert.
– Eelke
Nov 10 '18 at 8:31
Could you give the definitions for some of the tables and the fkey constraints involved and also the insert query you perform and the explain analyze output for that query. As I currently understand how your tables look there shouldn't be a problem as the constraints would only need checking on delete or update not on insert.
– Eelke
Nov 10 '18 at 8:31
Here is the table in question:
– Jivko Angelov
Nov 10 '18 at 8:43
Here is the table in question:
– Jivko Angelov
Nov 10 '18 at 8:43
Constraint are different from references..And what's constraint? is it just foreign key or there are check constraint?
– dwir182
Nov 10 '18 at 22:21
Constraint are different from references..And what's constraint? is it just foreign key or there are check constraint?
– dwir182
Nov 10 '18 at 22:21
add a comment |
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
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: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
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%2fstackoverflow.com%2fquestions%2f53237246%2fpostgresql-insert-into-too-many-referenced-table%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Stack Overflow!
- 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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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%2fstackoverflow.com%2fquestions%2f53237246%2fpostgresql-insert-into-too-many-referenced-table%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
Could you give the definitions for some of the tables and the fkey constraints involved and also the insert query you perform and the explain analyze output for that query. As I currently understand how your tables look there shouldn't be a problem as the constraints would only need checking on delete or update not on insert.
– Eelke
Nov 10 '18 at 8:31
Here is the table in question:
– Jivko Angelov
Nov 10 '18 at 8:43
Constraint are different from references..And what's constraint? is it just foreign key or there are check constraint?
– dwir182
Nov 10 '18 at 22:21