How to optimize search with create index in Postgres

Question

I have a database with 300,000,000 records,
To insert new records, I want to check whether the record is already in the database or not,
I use the following query to confirm:

SELECT student_code
FROM public.student_tbl
WHERE class_type='high_school' AND class_register='true' AND (student_code='F40101197X0' OR student_code='F40101197X1' OR student_code='F40101197X2');

It will take 2 minutes to complete, it takes quite a while.
Therefore, I want to save the time to check data is already in the database or not by using the following query:

CREATE INDEX student_tbl_class_register_index
ON public.student_tbl(class_register)
WHERE class_register IS TRUE;

UPDATE1:
・[student_id] to [student_code]
・class_type, student_code are the values that can be changed, while class_register is deciding whether to insert new records or not, if class_register='false' then can insert new records.

UPDATE2:
As the answer of Gordon Linoff, Laurenz Albe, ex4 suggested (thanks for your support),
I update as below:

Query check whether the data already exists in the database or not:

SELECT student_code
FROM public.student_tbl
WHERE class_type = 'high_school' AND
      class_register = 'true' AND
      student_code IN ('F40101197X0', 'F40101197X1', 'F40101197X2');

Query creates the index:

CREATE UNIQUE INDEX CONCURRENTLY studenttbl_classtype_studentcode_idx
ON public.student_tbl (class_type, student_code)
WHERE class_register = 'true';

ALTER TABLE public.student_tbl
ADD CONSTRAINT studenttbl_classtype_studentcode_idx
UNIQUE USING INDEX studenttbl_classtype_studentcode_idx;

Is it okay with the CREATE INDEX statement above?
Or how can I do well in this case, please tell me!
Any hint will be great. Thanks!

Don't keep modifying the question by adding parts of the answer. You cannot have a constraint with a partial index. Test yourself! — Laurenz Albe
– Laurenz Albe, Commented Mar 4, 2021 at 6:53

Gordon Linoff · Accepted Answer · 2021-03-04 03:00:45Z

1

For this query:

SELECT student_id
FROM public.student_tbl
WHERE class_type = 'high_school' AND
      class_register = 'true' AND
      student_id IN ('F40101197X0', 'F40101197X1', 'F40101197X2');

You want an index on student_tbl(class_type, class_register, student_id).

However, if you want to guarantee that rows are not duplicated, then you should be using a unique index or constraint.

answered Mar 4, 2021 at 3:00

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

3 Comments

Kevin Over a year ago

Thanks for your support! Does this mean that I need to create indexes for all 3 fields as student_tbl(class_type, class_register, student_id), right?

ex4 Over a year ago

I think last note in this answer has best solution. Use unique index if you want something to be unique in your database.

Kevin Over a year ago

Thanks for your support! I have updated the question.

Collectives™ on Stack Overflow

How to optimize search with create index in Postgres

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related