Slow query (using indexes) when no results found
0
I'm having some weird issues with an index/query.
this is the query:
select * from "emails" where status not in( 'QUEUED', 'SCHEDULED', 'REJECTED') and lower(recipient->>'emailAddress') = 'some@email.com' order by "sentDate" desc limit 1
and here is the explain analyze for an email address that exists in the emails table:
"Limit (cost=0.09..33.16 rows=1 width=981) (actual time=13.091..13.092 rows=1 loops=1)"
" -> Index Scan using emails_ordered_sentdate on emails (cost=0.09..190360.31 rows=5756 width=981) (actual time=13.090..13.090 rows=1 loops=1)"
" Filter: ((status <> ALL ('{QUEUED,SCHEDULED,REJECTED}'::text)) AND (lower((recipient ->> 'emailAddress'::text)) = 'some@email.com'::text))"
" Rows Removed by Filter: 2925"
"Planning time: 0.142 ms"
"Execution time: 13.122 ms"
this takes 13ms. which is just fine for a 1mil record table
however, when I use this same query for an email address that doesn't exist in my records, this query takes WAY longer
"Limit (cost=0.09..33.16 rows=1 width=981) (actual time=2945.785..2945.785 rows=0 loops=1)"
" -> Index Scan using emails_ordered_sentdate on emails (cost=0.09..190360.31 rows=5756 width=981) (actual time=2945.783..2945.783 rows=0 loops=1)"
" Filter: ((status <> ALL ('{QUEUED,SCHEDULED,REJECTED}'::text)) AND (lower((recipient ->> 'emailAddress'::text)) = 'nonexistingaddress@test.com'::text))"
" Rows Removed by Filter: 1181089"
"Planning time: 0.148 ms"
"Execution time: 2945.818 ms"
I'm using a btree index on the lower((recipient ->> 'emailAddress'::text)
any advice?
postgresql
asked 13 mins ago
StefanvdsStefanvds
1011
New contributor
Stefanvds is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
0
I'm having some weird issues with an index/query.
this is the query:
select * from "emails" where status not in( 'QUEUED', 'SCHEDULED', 'REJECTED') and lower(recipient->>'emailAddress') = 'some@email.com' order by "sentDate" desc limit 1
and here is the explain analyze for an email address that exists in the emails table:
"Limit (cost=0.09..33.16 rows=1 width=981) (actual time=13.091..13.092 rows=1 loops=1)"
" -> Index Scan using emails_ordered_sentdate on emails (cost=0.09..190360.31 rows=5756 width=981) (actual time=13.090..13.090 rows=1 loops=1)"
" Filter: ((status <> ALL ('{QUEUED,SCHEDULED,REJECTED}'::text)) AND (lower((recipient ->> 'emailAddress'::text)) = 'some@email.com'::text))"
" Rows Removed by Filter: 2925"
"Planning time: 0.142 ms"
"Execution time: 13.122 ms"
this takes 13ms. which is just fine for a 1mil record table
however, when I use this same query for an email address that doesn't exist in my records, this query takes WAY longer
"Limit (cost=0.09..33.16 rows=1 width=981) (actual time=2945.785..2945.785 rows=0 loops=1)"
" -> Index Scan using emails_ordered_sentdate on emails (cost=0.09..190360.31 rows=5756 width=981) (actual time=2945.783..2945.783 rows=0 loops=1)"
" Filter: ((status <> ALL ('{QUEUED,SCHEDULED,REJECTED}'::text)) AND (lower((recipient ->> 'emailAddress'::text)) = 'nonexistingaddress@test.com'::text))"
" Rows Removed by Filter: 1181089"
"Planning time: 0.148 ms"
"Execution time: 2945.818 ms"
I'm using a btree index on the lower((recipient ->> 'emailAddress'::text)
any advice?
postgresql
asked 13 mins ago
StefanvdsStefanvds
1011
New contributor
Stefanvds is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
0
0
0
I'm having some weird issues with an index/query.
this is the query:
select * from "emails" where status not in( 'QUEUED', 'SCHEDULED', 'REJECTED') and lower(recipient->>'emailAddress') = 'some@email.com' order by "sentDate" desc limit 1
and here is the explain analyze for an email address that exists in the emails table:
"Limit (cost=0.09..33.16 rows=1 width=981) (actual time=13.091..13.092 rows=1 loops=1)"
" -> Index Scan using emails_ordered_sentdate on emails (cost=0.09..190360.31 rows=5756 width=981) (actual time=13.090..13.090 rows=1 loops=1)"
" Filter: ((status <> ALL ('{QUEUED,SCHEDULED,REJECTED}'::text)) AND (lower((recipient ->> 'emailAddress'::text)) = 'some@email.com'::text))"
" Rows Removed by Filter: 2925"
"Planning time: 0.142 ms"
"Execution time: 13.122 ms"
this takes 13ms. which is just fine for a 1mil record table
however, when I use this same query for an email address that doesn't exist in my records, this query takes WAY longer
"Limit (cost=0.09..33.16 rows=1 width=981) (actual time=2945.785..2945.785 rows=0 loops=1)"
" -> Index Scan using emails_ordered_sentdate on emails (cost=0.09..190360.31 rows=5756 width=981) (actual time=2945.783..2945.783 rows=0 loops=1)"
" Filter: ((status <> ALL ('{QUEUED,SCHEDULED,REJECTED}'::text)) AND (lower((recipient ->> 'emailAddress'::text)) = 'nonexistingaddress@test.com'::text))"
" Rows Removed by Filter: 1181089"
"Planning time: 0.148 ms"
"Execution time: 2945.818 ms"
I'm using a btree index on the lower((recipient ->> 'emailAddress'::text)
any advice?
postgresql
asked 13 mins ago
StefanvdsStefanvds
1011
New contributor
Stefanvds is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
I'm having some weird issues with an index/query.
this is the query:
select * from "emails" where status not in( 'QUEUED', 'SCHEDULED', 'REJECTED') and lower(recipient->>'emailAddress') = 'some@email.com' order by "sentDate" desc limit 1
and here is the explain analyze for an email address that exists in the emails table:
"Limit (cost=0.09..33.16 rows=1 width=981) (actual time=13.091..13.092 rows=1 loops=1)"
" -> Index Scan using emails_ordered_sentdate on emails (cost=0.09..190360.31 rows=5756 width=981) (actual time=13.090..13.090 rows=1 loops=1)"
" Filter: ((status <> ALL ('{QUEUED,SCHEDULED,REJECTED}'::text)) AND (lower((recipient ->> 'emailAddress'::text)) = 'some@email.com'::text))"
" Rows Removed by Filter: 2925"
"Planning time: 0.142 ms"
"Execution time: 13.122 ms"
this takes 13ms. which is just fine for a 1mil record table
however, when I use this same query for an email address that doesn't exist in my records, this query takes WAY longer
"Limit (cost=0.09..33.16 rows=1 width=981) (actual time=2945.785..2945.785 rows=0 loops=1)"
" -> Index Scan using emails_ordered_sentdate on emails (cost=0.09..190360.31 rows=5756 width=981) (actual time=2945.783..2945.783 rows=0 loops=1)"
" Filter: ((status <> ALL ('{QUEUED,SCHEDULED,REJECTED}'::text)) AND (lower((recipient ->> 'emailAddress'::text)) = 'nonexistingaddress@test.com'::text))"
" Rows Removed by Filter: 1181089"
"Planning time: 0.148 ms"
"Execution time: 2945.818 ms"
I'm using a btree index on the lower((recipient ->> 'emailAddress'::text)
any advice?
postgresql
postgresql
asked 13 mins ago
StefanvdsStefanvds
1011
New contributor
Stefanvds is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 13 mins ago
StefanvdsStefanvds
1011
New contributor
Stefanvds is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 13 mins ago
StefanvdsStefanvds
1011
New contributor
Stefanvds is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 13 mins ago
StefanvdsStefanvds
1011
asked 13 mins ago
StefanvdsStefanvds
1011
1011
New contributor
Stefanvds is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
Stefanvds is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Stefanvds is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
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
});
}
});
Stefanvds is a new contributor. Be nice, and check out our Code of Conduct.
draft saved
draft discarded
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%2fdba.stackexchange.com%2fquestions%2f227627%2fslow-query-using-indexes-when-no-results-found%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
Stefanvds is a new contributor. Be nice, and check out our Code of Conduct.
draft saved
draft discarded
Stefanvds is a new contributor. Be nice, and check out our Code of Conduct.
Stefanvds is a new contributor. Be nice, and check out our Code of Conduct.
Stefanvds is a new contributor. Be nice, and check out our Code of Conduct.
Thanks for contributing an answer to Database Administrators 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.
draft saved
draft discarded
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%2fdba.stackexchange.com%2fquestions%2f227627%2fslow-query-using-indexes-when-no-results-found%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
