Simple Query Elapsed Time ~ 3 hrs Using Bitmap Index/Heap Scan

From: Fred Habash <fmhabash(at)gmail(dot)com>
To: pgsql-performance(at)lists(dot)postgresql(dot)org
Subject: Simple Query Elapsed Time ~ 3 hrs Using Bitmap Index/Heap Scan
Date: 2018-06-05 14:17:08
Message-ID: 560c24f9-dced-94ae-8e14-fe9451246342@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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 | notnull | plain | |
cl_value | text | notnull | extended | |
vclf_number | integer | notnull | plain | |
cit_id | bigint | notnull | plain | |
cl_date_created | timestamp with time zone | notnull | 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)

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Sergei Kornilov 2018-06-05 14:26:33 Re: Simple Query Elapsed Time ~ 3 hrs Using Bitmap Index/Heap Scan
Previous Message Nicolas Seinlet 2018-05-31 13:16:17 Re: Sort is generating rows