Best index for column where values are mostly the sameHow do databases store index key values (on-disk) for...
Tikz - highlight text in an image
Is there a technology capable of disabling the whole of Earth's satellitle network?
Idea behind "[...] makes pointer from integer without a cast"
How bad is a Computer Science course that doesn't teach Design Patterns?
How respect the space?
How to write pow math?
Why don't hotels offer ≥ 1 kitchen that must be booked?
Would life expectancy increase if we replaced healthy organs with artificial ones?
Can you make a Spell Glyph of a spell that has the potential to target more than one creature?
I hate taking lectures, can I still survive in academia?
Sing Baby Shark
Why does finding small effects in large studies indicate publication bias?
Someone wants me to use my credit card at a card-only gas/petrol pump in return for cash
Simple Question About Conservation of Angular Momentum
Was Opportunity's last message to Earth "My battery is low and it's getting dark"?
Who, if anyone, was the first astronaut to return to earth in a different vessel?
What happens when the last remaining players refuse to kill each other?
Is it possible to detect 100% of SQLi with a simple regex?
Why are recumbent bicycles and velomobiles illegal in UCI bicycle racing?
Why is it a problem for Freddie if the guys from Munich did what he wanted?
Pictures from Mars
Identical projects by students at two different colleges: still plagiarism?
Found a major flaw in paper from home university – to which I would like to return
Substitute ./ and ../ directories by actual names
Best index for column where values are mostly the same
How do databases store index key values (on-disk) for variable length fields?Select rows, where 3 columns have the same valuesnumbering rows consecutively for a number of tablesIndexes: integer vs string performance if the number of nodes is the sameAre two logically equal indices physically the same index?Are PostgreSQL clusters and servers the same thing?How to make value in a row equal to now() in postgresMultiple COUNTs over the same columnWhat is the data type of the ‘ctid’ system column in Postgres?Multi-column and single-column index on the same column
We have an integer column that currently consists only of 0 or 1 values. This column has now been used by a developer to store a unique 32-bit identifier on some occasions, and we need to be able to efficiently pull out rows containing any one of these identifiers.
Given the value will be 0 or 1 say (I don't have figures yet) 99% of the time, how might it best be indexed to query against the minority case? Am I even right in thinking the volume of common values will be an issue?
Column | Type | Modifiers
----------------------------+---------+--------------------
event_value | integer | not null
There are currently no indexes on this column. And I don't envisage the need to regularly select just the 0 or 1 values.
The table is of a reasonable size, currently 30 million rows and growing fast.
I appreciate this isn't the best use of the column, but that can't change in the short term.
postgresql index postgresql-9.6
edited 4 hours ago
MDCCL
6,75731745
asked 7 hours ago
whoaskedwhoasked
193
|
show 1 more comment
We have an integer column that currently consists only of 0 or 1 values. This column has now been used by a developer to store a unique 32-bit identifier on some occasions, and we need to be able to efficiently pull out rows containing any one of these identifiers.
Given the value will be 0 or 1 say (I don't have figures yet) 99% of the time, how might it best be indexed to query against the minority case? Am I even right in thinking the volume of common values will be an issue?
Column | Type | Modifiers
----------------------------+---------+--------------------
event_value | integer | not null
There are currently no indexes on this column. And I don't envisage the need to regularly select just the 0 or 1 values.
The table is of a reasonable size, currently 30 million rows and growing fast.
I appreciate this isn't the best use of the column, but that can't change in the short term.
postgresql index postgresql-9.6
edited 4 hours ago
MDCCL
6,75731745
asked 7 hours ago
whoaskedwhoasked
193
3
Maybe a filtered index?create index on the_table (...) where event_value > 1;
– a_horse_with_no_name
7 hours ago
3
What @a_horse_with_no_name suggests. But you'll then should be using modified filters:where (event_value > 1) and event_value = @XYZ, so the filtered index is used.
– ypercubeᵀᴹ
6 hours ago
@a_horse_with_no_name Ah, ok. I didn't even realise you could have a where clause attached to the index. Thanks.
– whoasked
6 hours ago
1
Yes. The benefit compared to a common index is that it has a much smaller size (1% in your case) as it will store only the rows with values that differ from 0,1. @a_horse_with_no_name, add an answer?
– ypercubeᵀᴹ
6 hours ago
@ypercubeᵀᴹ You should not even need the modified filters. I can construct situations in which you do, but they are unlikely to occur in practice in this context.
– jjanes
4 hours ago
|
show 1 more comment
We have an integer column that currently consists only of 0 or 1 values. This column has now been used by a developer to store a unique 32-bit identifier on some occasions, and we need to be able to efficiently pull out rows containing any one of these identifiers.
Given the value will be 0 or 1 say (I don't have figures yet) 99% of the time, how might it best be indexed to query against the minority case? Am I even right in thinking the volume of common values will be an issue?
Column | Type | Modifiers
----------------------------+---------+--------------------
event_value | integer | not null
There are currently no indexes on this column. And I don't envisage the need to regularly select just the 0 or 1 values.
The table is of a reasonable size, currently 30 million rows and growing fast.
I appreciate this isn't the best use of the column, but that can't change in the short term.
postgresql index postgresql-9.6
edited 4 hours ago
MDCCL
6,75731745
asked 7 hours ago
whoaskedwhoasked
193
We have an integer column that currently consists only of 0 or 1 values. This column has now been used by a developer to store a unique 32-bit identifier on some occasions, and we need to be able to efficiently pull out rows containing any one of these identifiers.
Given the value will be 0 or 1 say (I don't have figures yet) 99% of the time, how might it best be indexed to query against the minority case? Am I even right in thinking the volume of common values will be an issue?
Column | Type | Modifiers
----------------------------+---------+--------------------
event_value | integer | not null
There are currently no indexes on this column. And I don't envisage the need to regularly select just the 0 or 1 values.
The table is of a reasonable size, currently 30 million rows and growing fast.
I appreciate this isn't the best use of the column, but that can't change in the short term.
postgresql index postgresql-9.6
postgresql index postgresql-9.6
edited 4 hours ago
MDCCL
6,75731745
asked 7 hours ago
whoaskedwhoasked
193
edited 4 hours ago
MDCCL
6,75731745
asked 7 hours ago
whoaskedwhoasked
193
edited 4 hours ago
MDCCL
6,75731745
edited 4 hours ago
MDCCL
6,75731745
edited 4 hours ago
MDCCL
6,75731745
6,75731745
asked 7 hours ago
whoaskedwhoasked
193
asked 7 hours ago
whoaskedwhoasked
193
asked 7 hours ago
whoaskedwhoasked
193
193
3
Maybe a filtered index?create index on the_table (...) where event_value > 1;
– a_horse_with_no_name
7 hours ago
3
What @a_horse_with_no_name suggests. But you'll then should be using modified filters:where (event_value > 1) and event_value = @XYZ, so the filtered index is used.
– ypercubeᵀᴹ
6 hours ago
@a_horse_with_no_name Ah, ok. I didn't even realise you could have a where clause attached to the index. Thanks.
– whoasked
6 hours ago
1
Yes. The benefit compared to a common index is that it has a much smaller size (1% in your case) as it will store only the rows with values that differ from 0,1. @a_horse_with_no_name, add an answer?
– ypercubeᵀᴹ
6 hours ago
@ypercubeᵀᴹ You should not even need the modified filters. I can construct situations in which you do, but they are unlikely to occur in practice in this context.
– jjanes
4 hours ago
|
show 1 more comment
3
Maybe a filtered index?create index on the_table (...) where event_value > 1;
– a_horse_with_no_name
7 hours ago
3
What @a_horse_with_no_name suggests. But you'll then should be using modified filters:where (event_value > 1) and event_value = @XYZ, so the filtered index is used.
– ypercubeᵀᴹ
6 hours ago
@a_horse_with_no_name Ah, ok. I didn't even realise you could have a where clause attached to the index. Thanks.
– whoasked
6 hours ago
1
Yes. The benefit compared to a common index is that it has a much smaller size (1% in your case) as it will store only the rows with values that differ from 0,1. @a_horse_with_no_name, add an answer?
– ypercubeᵀᴹ
6 hours ago
@ypercubeᵀᴹ You should not even need the modified filters. I can construct situations in which you do, but they are unlikely to occur in practice in this context.
– jjanes
4 hours ago
3
3
Maybe a filtered index?
create index on the_table (...) where event_value > 1;– a_horse_with_no_name
7 hours ago
Maybe a filtered index?
create index on the_table (...) where event_value > 1;– a_horse_with_no_name
7 hours ago
3
3
What @a_horse_with_no_name suggests. But you'll then should be using modified filters:
where (event_value > 1) and event_value = @XYZ, so the filtered index is used.– ypercubeᵀᴹ
6 hours ago
What @a_horse_with_no_name suggests. But you'll then should be using modified filters:
where (event_value > 1) and event_value = @XYZ, so the filtered index is used.– ypercubeᵀᴹ
6 hours ago
@a_horse_with_no_name Ah, ok. I didn't even realise you could have a where clause attached to the index. Thanks.
– whoasked
6 hours ago
@a_horse_with_no_name Ah, ok. I didn't even realise you could have a where clause attached to the index. Thanks.
– whoasked
6 hours ago
1
1
Yes. The benefit compared to a common index is that it has a much smaller size (1% in your case) as it will store only the rows with values that differ from 0,1. @a_horse_with_no_name, add an answer?
– ypercubeᵀᴹ
6 hours ago
Yes. The benefit compared to a common index is that it has a much smaller size (1% in your case) as it will store only the rows with values that differ from 0,1. @a_horse_with_no_name, add an answer?
– ypercubeᵀᴹ
6 hours ago
@ypercubeᵀᴹ You should not even need the modified filters. I can construct situations in which you do, but they are unlikely to occur in practice in this context.
– jjanes
4 hours ago
@ypercubeᵀᴹ You should not even need the modified filters. I can construct situations in which you do, but they are unlikely to occur in practice in this context.
– jjanes
4 hours ago
|
show 1 more 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
});
}
});
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%2f230135%2fbest-index-for-column-where-values-are-mostly-the-same%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 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.
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%2f230135%2fbest-index-for-column-where-values-are-mostly-the-same%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
Maybe a filtered index?
create index on the_table (...) where event_value > 1;– a_horse_with_no_name
7 hours ago
3
What @a_horse_with_no_name suggests. But you'll then should be using modified filters:
where (event_value > 1) and event_value = @XYZ, so the filtered index is used.– ypercubeᵀᴹ
6 hours ago
@a_horse_with_no_name Ah, ok. I didn't even realise you could have a where clause attached to the index. Thanks.
– whoasked
6 hours ago
1
Yes. The benefit compared to a common index is that it has a much smaller size (1% in your case) as it will store only the rows with values that differ from 0,1. @a_horse_with_no_name, add an answer?
– ypercubeᵀᴹ
6 hours ago
@ypercubeᵀᴹ You should not even need the modified filters. I can construct situations in which you do, but they are unlikely to occur in practice in this context.
– jjanes
4 hours ago