From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Fred Habash <fmhabash(at)gmail(dot)com> |
Cc: | pgsql-performance(at)lists(dot)postgresql(dot)org |
Subject: | Re: Simple Query Elapsed Time ~ 3 hrs Using Bitmap Index/Heap Scan |
Date: | 2018-06-05 14:42:22 |
Message-ID: | 28448.1528209742@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Fred Habash <fmhabash(at)gmail(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Fd Habash | 2018-06-05 17:18:10 | RE: Simple Query Elapsed Time ~ 3 hrs Using Bitmap Index/Heap Scan |
Previous Message | Matthew Hall | 2018-06-05 14:42:20 | Re: Simple Query Elapsed Time ~ 3 hrs Using Bitmap Index/Heap Scan |