Probably the cardinality of "vclf_number" is really bad. So the scan on that index is returning many million or billion rows and then you get a recheck which takes semi-forever. So you need an index on cl_value or both vclf_number and cl_value. If you know some properties of the values actually stored inside of those that will help.
Matthew Hall
Trying to optimize the Elapsed Time (ET) of this query. Currently, it is hovering around 3 hrs.
Running a 'vaccum analyse' had no effect on ET. Even forcing an 'indexonly' scan by disabling 'enable_seqscan', still around the 3 hrs.
The table is around 4.6B rows,
explain select cit_id, cl_value from reflink.citation_locators where cl_value = '1507617681' and vclf_number = 1 ;
QUERY PLAN
-----------------------------------------------------------------------------------------
Bitmap Heap Scan on citation_locators (cost=5066559.01..50999084.79 rows=133 width=23)
Recheck Cond: (vclf_number = 1)
Filter: (cl_value = '1507617681'::text)
-> Bitmap Index Scan on cl_indx_fk02 (cost=0.00..5066558.97 rows=493984719 width=0)
Index Cond: (vclf_number = 1)
(5 rows)
reflink.citation_locators Table "reflink.citation_locators" Column | Type | Modifiers | Storage | Stats target | Description
------------------+--------------------------+-----------+----------+--------------+-------------cl_id | bigint | not null | plain | | cl_value | text | not null | extended | | vclf_number | integer | not null | plain | | cit_id | bigint | not null | plain | | cl_date_created | timestamp with time zone | not null | plain | | cl_date_modified | timestamp with time zone | | plain | |
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)
Foreign-key constraints: "cl_cnst_fk01" FOREIGN KEY (cit_id) REFERENCES citations(cit_id) NOT VALID "cl_cnst_fk02" FOREIGN KEY (vclf_number) REFERENCES valid_cit_locator_fields(vclf_number)