How to create an Index on a boolean column

Question

My DB table has one primary key and a number of integer columns and one boolean column called paused which is not in the primary key. This table will only ever hold a few hundred rows but I need to query the boolean column very regularly. I need to know if any row in the boolean paused column is true, if one row is true I will return true if all are false I will return false.

Should I create an index on the boolean column and what would that syntax look like or is there any other way to optimize that query?

CREATE TABLE IF NOT EXISTS pause_metrics (
    consumer TEXT NOT NULL,
    timstamp TIMESTAMP NOT NULL,
    idle_counter INTEGER NOT NULL,
    paused BOOLEAN DEFAULT FALSE NOT NULL,
    PRIMARY KEY(consumer)
);

Please edit your question and add the create table statements for the tables in question (including the existing indexes) and, the query you are trying to optimize. formatted text please, no screen shots — user330315
– user330315, Commented Feb 15, 2019 at 9:38
For "just a few hundred rows" and index might likely never be used to begin with - especially if the true/false values have an even distribution. If there are a lot of rows with one of the values (e.g. most of them have paused = false), then a filtered index might make sense e.g. create index on the_table (some_column) where not paused — user330315
– user330315, Commented Feb 15, 2019 at 9:40
I added the CREATE TABLE statement. I am really interested in the quickest way of calling a query like this. SELECT paused from purge_consumer_metrics where paused = true limit 1; — mrmannione
– mrmannione, Commented Feb 15, 2019 at 10:35

score 4 · Accepted Answer · 2019-02-15 11:39:53Z

4

To support the following query:

SELECT paused 
from pause_metrics
where paused  
limit 1;

A filtered index would be the most efficient thing:

create index idx_paused on pause_metrics(paused)
where paused;

The actual column in the index doesn't really matter, the important part is the where paused which only indexes the rows that have paused = true.

To find out if all rows have paused = false, you can use an exists query:

select not exists (SELECT 1 from pause_metrics where paused limit 1) as all_active

This will make use of the filtered query and should be quite quick.

edited Feb 15, 2019 at 11:39

answered Feb 15, 2019 at 10:42

user330315

Sign up to request clarification or add additional context in comments.

4 Comments

mrmannione Over a year ago

I only need am index on the paused column. mostly all rows will be false. is there a way to quickly select that all rows are false? Does the index still help here?

user330315 Over a year ago

@mrmannione. if the query for pause = true returns no row then you know that all rows are false

mrmannione Over a year ago

So correct me if I am wrong. 1) I don't need an Index for this ? 2) My best query is: SELECT paused from purge_consumer_metrics where paused = true limit 1;

user330315 Over a year ago

The index is used for the query I have shown. If you need it depends on your performance requirements and your hardware. Just test the query with and without the index, e.g. using explain (analyze, buffers) to do the testing

Collectives™ on Stack Overflow

How to create an Index on a boolean column

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related