Quickly figure out the MAX of the LOWER-bound of a TSTZRANGE column
0
On Postgres 9.6 is there a way to optimize the following query on a TSTZRANGE col?
SELECT MAX(LOWER(date_range)) FROM tbl;
which takes ~5 seconds on a 26M rows table.
I've tried adding an expression index:
CREATE INDEX tbl_expr_idx ON tbl((LOWER(date_range)) timestamptz_ops);
But that does not seem to get used:
Aggregate (cost=775505.00..775505.01 rows=1 width=8)
-> Seq Scan on tbl (cost=0.00..644984.00 rows=26104200 width=22)
postgresql
asked Nov 13 '18 at 11:20
salientsalient
774721
|
show 3 more comments
0
On Postgres 9.6 is there a way to optimize the following query on a TSTZRANGE col?
SELECT MAX(LOWER(date_range)) FROM tbl;
which takes ~5 seconds on a 26M rows table.
I've tried adding an expression index:
CREATE INDEX tbl_expr_idx ON tbl((LOWER(date_range)) timestamptz_ops);
But that does not seem to get used:
Aggregate (cost=775505.00..775505.01 rows=1 width=8)
-> Seq Scan on tbl (cost=0.00..644984.00 rows=26104200 width=22)
postgresql
asked Nov 13 '18 at 11:20
salientsalient
774721
Do you have an index ondate_range? If yes you could try:select lower(date_range) from tbl order by date_range desc limit 1- not sure how that works out with overlapping ranges though
– a_horse_with_no_name
Nov 13 '18 at 11:27
@a_horse_with_no_name — yup, a GIST one. That does also result in a sequential scan of the table, however.
– salient
Nov 13 '18 at 11:39
I find that hard to believe. What if youSET enable_seqscan = offbefore the query? Can you come up with a complete reproducible test case?
– Laurenz Albe
Nov 13 '18 at 11:43
With a regular btree Index Postgres uses an "Index Scan Backwards" for me: explain.depesz.com/s/89kC (5 million rows)
– a_horse_with_no_name
Nov 13 '18 at 11:46
Hm. When I run withSET enable_seqscan = offit uses another composite index that consists of two other columns — which ends up slower.
– salient
Nov 13 '18 at 11:48
|
show 3 more comments
0
0
0
On Postgres 9.6 is there a way to optimize the following query on a TSTZRANGE col?
SELECT MAX(LOWER(date_range)) FROM tbl;
which takes ~5 seconds on a 26M rows table.
I've tried adding an expression index:
CREATE INDEX tbl_expr_idx ON tbl((LOWER(date_range)) timestamptz_ops);
But that does not seem to get used:
Aggregate (cost=775505.00..775505.01 rows=1 width=8)
-> Seq Scan on tbl (cost=0.00..644984.00 rows=26104200 width=22)
postgresql
asked Nov 13 '18 at 11:20
salientsalient
774721
On Postgres 9.6 is there a way to optimize the following query on a TSTZRANGE col?
SELECT MAX(LOWER(date_range)) FROM tbl;
which takes ~5 seconds on a 26M rows table.
I've tried adding an expression index:
CREATE INDEX tbl_expr_idx ON tbl((LOWER(date_range)) timestamptz_ops);
But that does not seem to get used:
Aggregate (cost=775505.00..775505.01 rows=1 width=8)
-> Seq Scan on tbl (cost=0.00..644984.00 rows=26104200 width=22)
postgresql
postgresql
asked Nov 13 '18 at 11:20
salientsalient
774721
asked Nov 13 '18 at 11:20
salientsalient
774721
asked Nov 13 '18 at 11:20
salientsalient
774721
asked Nov 13 '18 at 11:20
salientsalient
774721
asked Nov 13 '18 at 11:20
salientsalient
774721
774721
Do you have an index ondate_range? If yes you could try:select lower(date_range) from tbl order by date_range desc limit 1- not sure how that works out with overlapping ranges though
– a_horse_with_no_name
Nov 13 '18 at 11:27
@a_horse_with_no_name — yup, a GIST one. That does also result in a sequential scan of the table, however.
– salient
Nov 13 '18 at 11:39
I find that hard to believe. What if youSET enable_seqscan = offbefore the query? Can you come up with a complete reproducible test case?
– Laurenz Albe
Nov 13 '18 at 11:43
With a regular btree Index Postgres uses an "Index Scan Backwards" for me: explain.depesz.com/s/89kC (5 million rows)
– a_horse_with_no_name
Nov 13 '18 at 11:46
Hm. When I run withSET enable_seqscan = offit uses another composite index that consists of two other columns — which ends up slower.
– salient
Nov 13 '18 at 11:48
|
show 3 more comments
Do you have an index ondate_range? If yes you could try:select lower(date_range) from tbl order by date_range desc limit 1- not sure how that works out with overlapping ranges though
– a_horse_with_no_name
Nov 13 '18 at 11:27
@a_horse_with_no_name — yup, a GIST one. That does also result in a sequential scan of the table, however.
– salient
Nov 13 '18 at 11:39
I find that hard to believe. What if youSET enable_seqscan = offbefore the query? Can you come up with a complete reproducible test case?
– Laurenz Albe
Nov 13 '18 at 11:43
With a regular btree Index Postgres uses an "Index Scan Backwards" for me: explain.depesz.com/s/89kC (5 million rows)
– a_horse_with_no_name
Nov 13 '18 at 11:46
Hm. When I run withSET enable_seqscan = offit uses another composite index that consists of two other columns — which ends up slower.
– salient
Nov 13 '18 at 11:48
Do you have an index on
date_range? If yes you could try: select lower(date_range) from tbl order by date_range desc limit 1 - not sure how that works out with overlapping ranges though– a_horse_with_no_name
Nov 13 '18 at 11:27
Do you have an index on
date_range? If yes you could try: select lower(date_range) from tbl order by date_range desc limit 1 - not sure how that works out with overlapping ranges though– a_horse_with_no_name
Nov 13 '18 at 11:27
@a_horse_with_no_name — yup, a GIST one. That does also result in a sequential scan of the table, however.
– salient
Nov 13 '18 at 11:39
@a_horse_with_no_name — yup, a GIST one. That does also result in a sequential scan of the table, however.
– salient
Nov 13 '18 at 11:39
I find that hard to believe. What if you
SET enable_seqscan = off before the query? Can you come up with a complete reproducible test case?– Laurenz Albe
Nov 13 '18 at 11:43
I find that hard to believe. What if you
SET enable_seqscan = off before the query? Can you come up with a complete reproducible test case?– Laurenz Albe
Nov 13 '18 at 11:43
With a regular btree Index Postgres uses an "Index Scan Backwards" for me: explain.depesz.com/s/89kC (5 million rows)
– a_horse_with_no_name
Nov 13 '18 at 11:46
With a regular btree Index Postgres uses an "Index Scan Backwards" for me: explain.depesz.com/s/89kC (5 million rows)
– a_horse_with_no_name
Nov 13 '18 at 11:46
Hm. When I run with
SET enable_seqscan = off it uses another composite index that consists of two other columns — which ends up slower.– salient
Nov 13 '18 at 11:48
Hm. When I run with
SET enable_seqscan = off it uses another composite index that consists of two other columns — which ends up slower.– salient
Nov 13 '18 at 11:48
|
show 3 more comments
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
);
);
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%2fstackoverflow.com%2fquestions%2f53279917%2fquickly-figure-out-the-max-of-the-lower-bound-of-a-tstzrange-column%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
draft saved
draft discarded
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.
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%2fstackoverflow.com%2fquestions%2f53279917%2fquickly-figure-out-the-max-of-the-lower-bound-of-a-tstzrange-column%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
Do you have an index on
date_range? If yes you could try:select lower(date_range) from tbl order by date_range desc limit 1- not sure how that works out with overlapping ranges though– a_horse_with_no_name
Nov 13 '18 at 11:27
@a_horse_with_no_name — yup, a GIST one. That does also result in a sequential scan of the table, however.
– salient
Nov 13 '18 at 11:39
I find that hard to believe. What if you
SET enable_seqscan = offbefore the query? Can you come up with a complete reproducible test case?– Laurenz Albe
Nov 13 '18 at 11:43
With a regular btree Index Postgres uses an "Index Scan Backwards" for me: explain.depesz.com/s/89kC (5 million rows)
– a_horse_with_no_name
Nov 13 '18 at 11:46
Hm. When I run with
SET enable_seqscan = offit uses another composite index that consists of two other columns — which ends up slower.– salient
Nov 13 '18 at 11:48