From: Tom Lane <tgl@sss.pgh.pa.us>
To: Fred Habash <fmhabash@gmail.com>
cc: pgsql-performance@lists.postgresql.org
Subject: Re: Simple Query Elapsed Time ~ 3 hrs Using Bitmap Index/Heap Scan
In-reply-to: <560c24f9-dced-94ae-8e14-fe9451246342@gmail.com>
References: <560c24f9-dced-94ae-8e14-fe9451246342@gmail.com>
Comments: In-reply-to Fred Habash <fmhabash@gmail.com>
	message dated "Tue, 05 Jun 2018 10:17:08 -0400"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <28447.1528209742.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Tue, 05 Jun 2018 10:42:22 -0400
Message-ID: <28448.1528209742@sss.pgh.pa.us>
Precedence: bulk

Fred Habash <fmhabash@gmail.com> writes:
> Indexes:
>      "cl_pk"  PRIMARY KEY, btree (cl_id)
>      "cl_cnst_uk01"  UNIQUE CONSTRAINT, btree (cit_id, vclf_number, cl_v=
alue)
>      "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