Question 177657 in PostgreSQL Question posted in
The official documentation can be found here.

Why is Postgresql slow when using two boolean columns in the WHERE condition?

CREATE TABLE IF NOT EXISTS schema1.table1
(
ID bigint NOT NULL,
eid bigint NOT NULL,
sID bigint,
oname character varying COLLATE pg_catalog."default" NOT NULL,
obtype integer NOT NULL,
parentid bigint,
pageorder integer,
obsubtype integer NOT NULL DEFAULT 0,
pstate integer NOT NULL DEFAULT 1,
lastmodifieddate timestamp without time zone,
lastmodifiedbyid bigint,
vstype integer,
opath character varying COLLATE pg_catalog."default",
arc boolean,
folder boolean,
fcategory bigint NOT NULL DEFAULT 915,
imotype boolean,
iostype boolean,
isemailmsg boolean NOT NULL DEFAULT false,
igtype boolean,
extension character varying COLLATE pg_catalog."default",
iher character varying COLLATE pg_catalog."default",
realpathoffset integer,
carvers0 bigint,
carvers1 bigint,
arctype boolean,
icont boolean,
email boolean,
emailattach boolean,
etype boolean,
hofamilyid bigint,
indstate integer NOT NULL DEFAULT 0,
logicalsize bigint,
addescription character varying COLLATE pg_catalog."default",
ouid uuid DEFAULT uuid_generate_v1(),
hperror boolean,
pdescrip character varying COLLATE pg_catalog."default",
CONSTRAINT pk_table1 PRIMARY KEY (ID),
CONSTRAINT omp_o_omp_e FOREIGN KEY (eid)
REFERENCES schema1.table2 (eid) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE CASCADE
)

```WITH (```
```    FILLFACTOR = 30,```
```    autovacuum_analyze_scale_factor = 0,```
```    autovacuum_analyze_threshold = 10000,```
```    autovacuum_vacuum_scale_factor = 0,```
```    autovacuum_vacuum_threshold = 10000```
```)```
```TABLESPACE schema1_ts;```

ALTER TABLE IF EXISTS schema1.table1
OWNER to schema1;

```ALTER TABLE IF EXISTS schema1.table1```
```    ALTER COLUMN fcategory SET STATISTICS 10000;```
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    After creation of composite index I can see the difference in the query performance. Thanks for your assistance.


  2. 0

    Yes, you can create a composite or bitmap index or even a covering index for such scenarios.

    1. Composite Index:

      CREATE INDEX IF NOT EXISTS composite_index
      ON schema1.table1 (folder, email);

    2. Bitmap Index:

      CREATE BITMAP INDEX IF NOT EXISTS bitmap_index
      ON schema1.table1 (folder, email);

    There are several advantages to using a composite index over a bitmap index in this scenario:

    1. Performance: Composite indexes are generally more efficient than bitmap indexes for multi-column queries. This is because composite indexes can be used to find rows that meet the criteria of all the columns in the index. Bitmap indexes, on the other hand, can only be used to find rows that match the criteria of a single column.

    2. Space: Composite indexes take up less space than bitmap indexes. This is because composite indexes only store the values of the columns in the index, whereas bitmap indexes store the values of all the columns in the table.

    3. Ease of use: Composite indexes are easier to use than bitmap indexes. This is because composite indexes can be used with standard SQL queries, whereas bitmap indexes require special queries.

    4. In your case, since you have a large table with 10 million records and the two boolean columns have 50% true and 50% false values, I would recommend using a composite index. This will improve the performance of your queries and make your database more efficient.

    However, there are some disadvantages to using a composite index:

    1. Complexity: Composite indexes can be more complex to create and maintain than single-column indexes.
    2. Space: Composite indexes can take up more space than single-column indexes.
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search