Postgresql query containing a constant in a logical-or leads to full table scan
I have a query that contains a clause of the form CONSTANT OR t.foo=:foo, where CONSTANT is some expression that evaluates to a constant boolean value. Then the query is executed as a full table scan, even when there is an index on column foo and the simple t.foo=:foo would use the index. The example query that I analyzed had the form :foo IS NOT NULL OR t.foo=:foo. Here is a simple example:
CREATE TABLE tst (
id serial,
name VARCHAR NOT NULL,
age INTEGER NOT NULL,
CONSTRAINT PK_ID PRIMARY KEY(ID));
EXPLAIN SELECT * FROM tst WHERE (23 IS NOT NULL OR id=23);
QUERY PLAN
--------------------------------------------------------
Seq Scan on tst (cost=0.00..22.00 rows=1200 width=40)
Is there any reason for this? Is there a way to tell the optimizer to logically evaluate/simplify the query?
postgresql query-optimization boolean-logic
asked Nov 12 '18 at 19:09
EPSG31468EPSG31468
668416
add a comment |
I have a query that contains a clause of the form CONSTANT OR t.foo=:foo, where CONSTANT is some expression that evaluates to a constant boolean value. Then the query is executed as a full table scan, even when there is an index on column foo and the simple t.foo=:foo would use the index. The example query that I analyzed had the form :foo IS NOT NULL OR t.foo=:foo. Here is a simple example:
CREATE TABLE tst (
id serial,
name VARCHAR NOT NULL,
age INTEGER NOT NULL,
CONSTRAINT PK_ID PRIMARY KEY(ID));
EXPLAIN SELECT * FROM tst WHERE (23 IS NOT NULL OR id=23);
QUERY PLAN
--------------------------------------------------------
Seq Scan on tst (cost=0.00..22.00 rows=1200 width=40)
Is there any reason for this? Is there a way to tell the optimizer to logically evaluate/simplify the query?
postgresql query-optimization boolean-logic
asked Nov 12 '18 at 19:09
EPSG31468EPSG31468
668416
3
where (23 IS NOT NULL OR id=23)is the same aswhere (true or id = 23)which is the same aswhere truewhich is the same as not having awherecondition at all. So you request all rows of the table, and the most efficient way to do that is a Seq Scan
– a_horse_with_no_name
Nov 12 '18 at 19:38
You are right, this was totally non-sense. I tried to simplify a more complex example, but this is of cause a full-table scan because the WHERE-part is true.
– EPSG31468
Nov 17 '18 at 18:44
add a comment |
I have a query that contains a clause of the form CONSTANT OR t.foo=:foo, where CONSTANT is some expression that evaluates to a constant boolean value. Then the query is executed as a full table scan, even when there is an index on column foo and the simple t.foo=:foo would use the index. The example query that I analyzed had the form :foo IS NOT NULL OR t.foo=:foo. Here is a simple example:
CREATE TABLE tst (
id serial,
name VARCHAR NOT NULL,
age INTEGER NOT NULL,
CONSTRAINT PK_ID PRIMARY KEY(ID));
EXPLAIN SELECT * FROM tst WHERE (23 IS NOT NULL OR id=23);
QUERY PLAN
--------------------------------------------------------
Seq Scan on tst (cost=0.00..22.00 rows=1200 width=40)
Is there any reason for this? Is there a way to tell the optimizer to logically evaluate/simplify the query?
postgresql query-optimization boolean-logic
asked Nov 12 '18 at 19:09
EPSG31468EPSG31468
668416
I have a query that contains a clause of the form CONSTANT OR t.foo=:foo, where CONSTANT is some expression that evaluates to a constant boolean value. Then the query is executed as a full table scan, even when there is an index on column foo and the simple t.foo=:foo would use the index. The example query that I analyzed had the form :foo IS NOT NULL OR t.foo=:foo. Here is a simple example:
CREATE TABLE tst (
id serial,
name VARCHAR NOT NULL,
age INTEGER NOT NULL,
CONSTRAINT PK_ID PRIMARY KEY(ID));
EXPLAIN SELECT * FROM tst WHERE (23 IS NOT NULL OR id=23);
QUERY PLAN
--------------------------------------------------------
Seq Scan on tst (cost=0.00..22.00 rows=1200 width=40)
Is there any reason for this? Is there a way to tell the optimizer to logically evaluate/simplify the query?
postgresql query-optimization boolean-logic
postgresql query-optimization boolean-logic
asked Nov 12 '18 at 19:09
EPSG31468EPSG31468
668416
asked Nov 12 '18 at 19:09
EPSG31468EPSG31468
668416
asked Nov 12 '18 at 19:09
EPSG31468EPSG31468
668416
asked Nov 12 '18 at 19:09
EPSG31468EPSG31468
668416
asked Nov 12 '18 at 19:09
EPSG31468EPSG31468
668416
668416
3
where (23 IS NOT NULL OR id=23)is the same aswhere (true or id = 23)which is the same aswhere truewhich is the same as not having awherecondition at all. So you request all rows of the table, and the most efficient way to do that is a Seq Scan
– a_horse_with_no_name
Nov 12 '18 at 19:38
You are right, this was totally non-sense. I tried to simplify a more complex example, but this is of cause a full-table scan because the WHERE-part is true.
– EPSG31468
Nov 17 '18 at 18:44
add a comment |
3
where (23 IS NOT NULL OR id=23)is the same aswhere (true or id = 23)which is the same aswhere truewhich is the same as not having awherecondition at all. So you request all rows of the table, and the most efficient way to do that is a Seq Scan
– a_horse_with_no_name
Nov 12 '18 at 19:38
You are right, this was totally non-sense. I tried to simplify a more complex example, but this is of cause a full-table scan because the WHERE-part is true.
– EPSG31468
Nov 17 '18 at 18:44
3
3
where (23 IS NOT NULL OR id=23) is the same as where (true or id = 23) which is the same as where true which is the same as not having a where condition at all. So you request all rows of the table, and the most efficient way to do that is a Seq Scan– a_horse_with_no_name
Nov 12 '18 at 19:38
where (23 IS NOT NULL OR id=23) is the same as where (true or id = 23) which is the same as where true which is the same as not having a where condition at all. So you request all rows of the table, and the most efficient way to do that is a Seq Scan– a_horse_with_no_name
Nov 12 '18 at 19:38
You are right, this was totally non-sense. I tried to simplify a more complex example, but this is of cause a full-table scan because the WHERE-part is true.
– EPSG31468
Nov 17 '18 at 18:44
You are right, this was totally non-sense. I tried to simplify a more complex example, but this is of cause a full-table scan because the WHERE-part is true.
– EPSG31468
Nov 17 '18 at 18:44
add a comment |
0
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%2f53268595%2fpostgresql-query-containing-a-constant-in-a-logical-or-leads-to-full-table-scan%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
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%2f53268595%2fpostgresql-query-containing-a-constant-in-a-logical-or-leads-to-full-table-scan%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
3
where (23 IS NOT NULL OR id=23)is the same aswhere (true or id = 23)which is the same aswhere truewhich is the same as not having awherecondition at all. So you request all rows of the table, and the most efficient way to do that is a Seq Scan– a_horse_with_no_name
Nov 12 '18 at 19:38
You are right, this was totally non-sense. I tried to simplify a more complex example, but this is of cause a full-table scan because the WHERE-part is true.
– EPSG31468
Nov 17 '18 at 18:44