Fred Habash <fmhab...@gmail.com> writes: > Indexes: > "cl_pk" PRIMARY KEY, btree (cl_id) > "cl_cnst_uk01" UNIQUE CONSTRAINT, btree (cit_id, vclf_number, cl_value) > "cl_indx_fk01" btree (cit_id) > "cl_indx_fk02" btree (vclf_number)
This is pretty inefficient index design. Your query is slow because the only selective condition it has is on cl_value, but you have no index that can be searched with cl_value as the leading condition. Moreover, you have two indexes that can be searched with cit_id as the leading condition, which is just wasteful. I'd try reorganizing the cl_cnst_uk01 index as (cl_value, vclf_number, cit_id) so that it can serve for searches on cl_value, while still enforcing the same uniqueness condition. This particular column ordering would also let your query use the vclf_number constraint as a secondary search condition, which would help even more. There's relevant advice about index design in the manual, https://www.postgresql.org/docs/current/static/indexes.html (see 11.3 and 11.5 particularly) regards, tom lane
