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 1fQClz-0004RH-SR for pgsql-performance@arkaria.postgresql.org; Tue, 05 Jun 2018 14:17:20 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1fQCly-0001S4-DW for pgsql-performance@arkaria.postgresql.org; Tue, 05 Jun 2018 14:17:18 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1fQCly-0001Rv-3S for pgsql-performance@lists.postgresql.org; Tue, 05 Jun 2018 14:17:18 +0000 Received: from mail-qt0-x244.google.com ([2607:f8b0:400d:c0d::244]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1fQClr-0000lx-AA for pgsql-performance@lists.postgresql.org; Tue, 05 Jun 2018 14:17:17 +0000 Received: by mail-qt0-x244.google.com with SMTP id i18-v6so2518666qtp.12 for ; Tue, 05 Jun 2018 07:17:11 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=to:from:subject:message-id:date:user-agent:mime-version :content-language; bh=Kf21MZnawc5ii+tbTRF1H3Q6rXG8+B0TWi9KdWGVOZU=; b=k9KfnIUKtiRS6u3LEy1jkPIWX9ubKjDNUOL1H0mwST0/07ujavbY4aqgqo5L/lzGUw PxhJyB+EVUDqxyCpAMONHZ4xVTzwbsptMPwCsftPdzCkFf/fdUaJH2FLbAY0XYrUk+IQ qmKC4SEYgRCqD0lV2+Nn8njwhsq89/teZ0fVlqWNKXoly6NtsDmndP2QbiWMnpg92F5c hmV4v2wVMMjASwcrW+a5y6Pmn3VJffhKIN494Kj+bTd0sm3B1ocaAEciRnfLxhVKZPHD 8yUKzprFJJMlnjJbVAWW4GSMlp6ncb22bN57KmPH8kknv7H27Adhooa0mi70EvG5B84v 7u0A== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:to:from:subject:message-id:date:user-agent :mime-version:content-language; bh=Kf21MZnawc5ii+tbTRF1H3Q6rXG8+B0TWi9KdWGVOZU=; b=n+5O2ZwcM37J+LXwR2hTjuIgilEGGRBgmfFBXU+HSLIKSs6ySTO4DAWej/Q3Ou0aFQ ng5+8Q5SUMWuHS/J99SPv0fURpGe0pz+DrG778WMOLFKmbbBY1jQTU6XuLxrHSl0wF7N izNzip/U7mtq8EjmNjYC/gk/UGh95F8eEq/DVvZDv7FhAosTxmzoTmex5TUedWdXuXbn 1KFQ/97ys1rwZhNbKw87rOv/eZmdS/6S+7/si+nLgol2meikBr85dWggD0J41WIIJ+QA OgF1HSVf3/DwQTUdpcc1jBEADyz/bjv7/aTWrqymdksnJ0cXjHkqDMqDUET78kN1vE4O FtFA== X-Gm-Message-State: APt69E1PaCmS8B+RVx0k1i44kVFbtU2d0oNz9nb5xLQGU+p3VzCqX32M CQzehtnO53+SAfY649zdP4uNyf/C X-Google-Smtp-Source: ADUXVKI4fG8BJdzaIVSWa+Jsw2q5wAfIIN+pPdleLz0a28Ctn2YD1c01K+t0U0LU4O2ajUKvs7Oe6g== X-Received: by 2002:ac8:491:: with SMTP id s17-v6mr24749207qtg.46.1528208228380; Tue, 05 Jun 2018 07:17:08 -0700 (PDT) Received: from [172.25.136.96] ([165.215.94.3]) by smtp.gmail.com with ESMTPSA id z193-v6sm9947243qkb.67.2018.06.05.07.17.07 for (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Tue, 05 Jun 2018 07:17:07 -0700 (PDT) To: pgsql-performance@lists.postgresql.org From: Fred Habash Subject: Simple Query Elapsed Time ~ 3 hrs Using Bitmap Index/Heap Scan Message-ID: <560c24f9-dced-94ae-8e14-fe9451246342@gmail.com> Date: Tue, 5 Jun 2018 10:17:08 -0400 User-Agent: Mozilla/5.0 (Windows NT 10.0; WOW64; rv:52.0) Gecko/20100101 Thunderbird/52.8.0 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="------------9E73F10A9EFD374937145D41" Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------9E73F10A9EFD374937145D41 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit 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) --------------9E73F10A9EFD374937145D41 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit

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)
--------------9E73F10A9EFD374937145D41--