Received: from malur.postgresql.org ([2a02:16a8:dc51::56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1fQDEH-0006on-5p for pgsql-performance@arkaria.postgresql.org; Tue, 05 Jun 2018 14:46:33 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1fQDEG-0003mD-0b for pgsql-performance@arkaria.postgresql.org; Tue, 05 Jun 2018 14:46:32 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1fQDAO-0006IZ-6D for pgsql-performance@lists.postgresql.org; Tue, 05 Jun 2018 14:42:32 +0000 Received: from master.mhcomputing.net ([74.208.228.170] helo=mail.mhcomputing.net) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1fQDAG-0007eT-Fy for pgsql-performance@lists.postgresql.org; Tue, 05 Jun 2018 14:42:30 +0000 Received: from [IPv6:2601:646:8301:88e0:e452:9081:c954:7774] (unknown [IPv6:2601:646:8301:88e0:e452:9081:c954:7774]) by mail.mhcomputing.net (Postfix) with ESMTPSA id 125FEF2; Tue, 5 Jun 2018 07:42:22 -0700 (PDT) Content-Type: multipart/alternative; boundary=Apple-Mail-5835C1EE-EB7E-467C-A46B-6778D906B3C0 Mime-Version: 1.0 (1.0) Subject: Re: Simple Query Elapsed Time ~ 3 hrs Using Bitmap Index/Heap Scan From: Matthew Hall X-Mailer: iPhone Mail (15E216) In-Reply-To: <560c24f9-dced-94ae-8e14-fe9451246342@gmail.com> Date: Tue, 5 Jun 2018 07:42:20 -0700 Cc: pgsql-performance@lists.postgresql.org Content-Transfer-Encoding: 7bit Message-Id: References: <560c24f9-dced-94ae-8e14-fe9451246342@gmail.com> To: Fred Habash List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --Apple-Mail-5835C1EE-EB7E-467C-A46B-6778D906B3C0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: quoted-printable 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 w= hich takes semi-forever. So you need an index on cl_value or both vclf_numbe= r and cl_value. If you know some properties of the values actually stored in= side of those that will help.=20 Matthew Hall > On Jun 5, 2018, at 7:17 AM, Fred Habash wrote: >=20 > Trying to optimize the Elapsed Time (ET) of this query. Currently, it is h= overing around 3 hrs. >=20 > Running a 'vaccum analyse' had no effect on ET. Even forcing an 'indexonly= ' scan by disabling 'enable_seqscan', still around the 3 hrs.=20 > The table is around 4.6B rows,=20 > explain select cit_id, cl_value from reflink.citation_locators where cl_v= alue =3D '1507617681' and vclf_number =3D 1 ; > QUERY PLAN = =20 > --------------------------------------------------------------------------= --------------- > Bitmap Heap Scan on citation_locators (cost=3D5066559.01..50999084.79 ro= ws=3D133 width=3D23) > Recheck Cond: (vclf_number =3D 1) > Filter: (cl_value =3D '1507617681'::text) > -> Bitmap Index Scan on cl_indx_fk02 (cost=3D0.00..5066558.97 rows=3D= 493984719 width=3D0) > Index Cond: (vclf_number =3D 1) > (5 rows) >=20 > reflink.citation_locators=20 > Table "reflink.citation_locators" > Column | Type | Modifiers | Storage | Stat= s target | Description=20 > ------------------+--------------------------+-----------+----------+-----= ---------+------------- > cl_id | bigint | not null | plain | = |=20 > cl_value | text | not null | extended | = |=20 > vclf_number | integer | not null | plain | = |=20 > cit_id | bigint | not null | plain | = |=20 > cl_date_created | timestamp with time zone | not null | plain | = |=20 > cl_date_modified | timestamp with time zone | | plain | = |=20 > 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 V= ALID "cl_cnst_fk02" FOREIGN KEY (vclf_number) REFERENCES valid_cit_locato= r_fields(vclf_number) --Apple-Mail-5835C1EE-EB7E-467C-A46B-6778D906B3C0 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 7bit 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

On Jun 5, 2018, at 7:17 AM, Fred Habash <fmhabash@gmail.com> wrote:

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)
--Apple-Mail-5835C1EE-EB7E-467C-A46B-6778D906B3C0--