PostgreSQL insert into too many referenced table
up vote
0
down vote
favorite
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 at 8:23
Jivko Angelov
422
add a comment |
up vote
0
down vote
favorite
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 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 at 8:31
Here is the table in question:
– Jivko Angelov
Nov 10 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 at 22:21
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
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 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 at 8:23
Jivko Angelov
422
asked Nov 10 at 8:23
Jivko Angelov
422
asked Nov 10 at 8:23
Jivko Angelov
422
asked Nov 10 at 8:23
Jivko Angelov
422
asked Nov 10 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 at 8:31
Here is the table in question:
– Jivko Angelov
Nov 10 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 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 at 8:31
Here is the table in question:
– Jivko Angelov
Nov 10 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 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 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 at 8:31
Here is the table in question:
– Jivko Angelov
Nov 10 at 8:43
Here is the table in question:
– Jivko Angelov
Nov 10 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 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 at 22:21
add a comment |
active
oldest
votes
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 at 8:31
Here is the table in question:
– Jivko Angelov
Nov 10 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 at 22:21