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 1fQFbI-0000tm-Dj for pgsql-performance@arkaria.postgresql.org; Tue, 05 Jun 2018 17:18:28 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1fQFbG-00069R-UU for pgsql-performance@arkaria.postgresql.org; Tue, 05 Jun 2018 17:18:26 +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 1fQFb4-0004oA-Tj for pgsql-performance@lists.postgresql.org; Tue, 05 Jun 2018 17:18:14 +0000 Received: from mail-it0-x229.google.com ([2607:f8b0:4001:c0b::229]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1fQFb1-0005HY-0c for pgsql-performance@lists.postgresql.org; Tue, 05 Jun 2018 17:18:13 +0000 Received: by mail-it0-x229.google.com with SMTP id e20-v6so4356883itc.1 for ; Tue, 05 Jun 2018 10:18:10 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=message-id:mime-version:to:cc:from:subject:date:importance :in-reply-to:references; bh=4coecmqfBwngKLZUlwiue3ksUZTEkXvM8bS93ID3R7I=; b=hcCqgcLDgkLFX529uJ8sBCoIfEnN+p8ZNgqBpf7ev8/QchU51VMDdvtzkvfT9235nZ jyThP5cvMAjQyS8eQ5rypr4w2hKuJXHtAlPd8UbonHwS8sXP7lMa8bXR9CXsBIz7/4LW QzC/l9rImz999niLIvlbJx0Lcx/CqSKRdfZpik9l8frucxARzokuK0LKnA5mvUjjDNi2 iGzO6sdZ/lu9ByWe0oIdlSxa+yT8VueVsfdKSaOeJ8RqxPGzOcdM3SoJyKJcQP3ejpgT SpRb5CfhFW2VyjjC3Ufb5LvpeAfcEGPyYwZLPHYi5seddGcPQHiT2zeTu+K3je+5NTrC +YKw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:message-id:mime-version:to:cc:from:subject:date :importance:in-reply-to:references; bh=4coecmqfBwngKLZUlwiue3ksUZTEkXvM8bS93ID3R7I=; b=Fi7+N0Go+UsDzqYgRGvwUYFaFR5mY7ZL64/Vjhi9oujXw5JBN2tOhBNPhBVoAqugty sObcYMN3dAUVPSJw5aKIt0GGywNsGSDkGJu7QbuJ3rfdrdmZLKZqaO9qKL5dx6PwmvkC GqSO05jkBIjI70SYf09yJ+D4PLuF3uYCSsJ0T6LlSV2NeDPjENb4o62zNuqhmDP/5eka M4165Gvt4amY6BxZlS0YI58wsDrlqkyTr1+SRJEvzEXScISaMvCajJnIwDY8MM0vRLSH wAuzVZpAYXuDHPIYmJ7uQMFz9ddhKUHfb9Xd+ehusll0IfztyWHBpmAnt7wLacenGtfp znBg== X-Gm-Message-State: APt69E3lvneWAWoMC7Fg2/VAS9rzBX1I+CH+JP67q403jhaSpT7LWRWf dm1Tb4bTz7b1RVHCWmlfAdR8z6h9 X-Google-Smtp-Source: ADUXVKJLQLnP1t9fNU10mMHA2eXvgy6DHaUPXEaiCUWIEBoReeo23+68qi2cMITvWvvF8aE9WTX0bg== X-Received: by 2002:a24:a0c2:: with SMTP id o185-v6mr104679ite.34.1528219089365; Tue, 05 Jun 2018 10:18:09 -0700 (PDT) Received: from ?IPv6:::ffff:172.25.136.96? ([165.215.94.3]) by smtp.gmail.com with ESMTPSA id 69-v6sm6497146iod.52.2018.06.05.10.18.08 (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Tue, 05 Jun 2018 10:18:08 -0700 (PDT) Message-ID: <5b16c5d0.1c69fb81.81abf.5c97@mx.google.com> MIME-Version: 1.0 To: Matthew Hall Cc: "pgsql-performance@lists.postgresql.org" From: Fd Habash Subject: RE: Simple Query Elapsed Time ~ 3 hrs Using Bitmap Index/Heap Scan Date: Tue, 5 Jun 2018 13:18:10 -0400 Importance: normal X-Priority: 3 In-Reply-To: References: <560c24f9-dced-94ae-8e14-fe9451246342@gmail.com> Content-Type: multipart/alternative; boundary="_6379029B-5E11-46F5-B26D-8470255CB515_" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --_6379029B-5E11-46F5-B26D-8470255CB515_ Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset="utf-8" Indexes are being redone as per these insights. Appreciate the great suppor= t. ---------------- Thank you From: Matthew Hall Sent: Tuesday, June 5, 2018 10:42 AM To: Fred Habash Cc: pgsql-performance@lists.postgresql.org Subject: Re: Simple Query Elapsed Time ~ 3 hrs Using Bitmap Index/Heap Scan Probably the cardinality of "vclf_number" is really bad. So the scan on tha= t index is returning many million or billion rows and then you get a rechec= k which takes semi-forever. So you need an index on cl_value or both vclf_n= umber and cl_value. If you know some properties of the values actually stor= ed inside of those that will help.=C2=A0 Matthew Hall On Jun 5, 2018, at 7:17 AM, Fred Habash wrote: Trying to optimize the Elapsed Time (ET) of this query. Currently, it is ho= vering 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.=20 The table is around 4.6B rows,=20 =C2=A0explain select cit_id, cl_value from reflink.citation_locators where = cl_value =3D '1507617681' and vclf_number =3D 1 ; =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0 QUERY PLAN=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=20 ---------------------------------------------------------------------------= -------------- =C2=A0Bitmap Heap Scan on citation_locators=C2=A0 (cost=3D5066559.01..50999= 084.79 rows=3D133 width=3D23) =C2=A0=C2=A0 Recheck Cond: (vclf_number =3D 1) =C2=A0=C2=A0 Filter: (cl_value =3D '1507617681'::text) =C2=A0=C2=A0 ->=C2=A0 Bitmap Index Scan on cl_indx_fk02=C2=A0 (cost=3D0.00.= .5066558.97 rows=3D493984719 width=3D0) =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Index Cond: (vclf_number = =3D 1) (5 rows) reflink.citation_locators=20 Table "reflink.citation_locators" Column | Type | Modifiers | Storage | Stats= 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 VA= LID "cl_cnst_fk02" FOREIGN KEY (vclf_number) REFERENCES valid_cit_locato= r_fields(vclf_number) --_6379029B-5E11-46F5-B26D-8470255CB515_ Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset="utf-8"

Indexes are being redone as per thes= e insights. Appreciate the great support.

&nbs= p;

----------------
Thank you

 

From: Matthew Hall
Sent: Tuesday, Jun= e 5, 2018 10:42 AM
To: Fred= Habash
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: = Simple Query Elapsed Time ~ 3 hrs Using Bitmap Index/Heap Scan

 

Probably the cardinality of "vclf_number" is real= ly 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 o= f 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 'index= only' scan by disabling 'enable_seqscan', still around the 3 hrs.

Th= e table is around 4.6B rows,

 ex= plain select cit_id, cl_value from reflink.citation_locators where cl_value= =3D '1507617681' and vclf_number =3D 1 ;
     =             &nb= sp;            =          QUERY PLAN  &nbs= p;            &= nbsp;           &nbs= p;           
--= ---------------------------------------------------------------------------= ------------
 Bitmap Heap Scan on citation_locators  (cost=3D5= 066559.01..50999084.79 rows=3D133 width=3D23)
   Recheck Cond:= (vclf_number =3D 1)
   Filter: (cl_value =3D '1507617681'::te= xt)
   ->  Bitmap Index Scan on cl_indx_fk02  (co= st=3D0.00..5066558.97 rows=3D493984719 width=3D0)
   &nbs= p;     Index Cond: (vclf_number =3D 1)
(5 rows)= 

reflink.citation_locators 
=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0Table "reflink.citation_l=
ocators"
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Column=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0 |=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0 Type=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0 | Modifiers | Storage=C2=A0 | Stats target | Description 
------------------+--------------------------+-----------=
+----------+--------------+-------------
 c=
l_id=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 | bi=
gint=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 | not null=C2=A0 | plain=C2=A0=C2=A0=C2=A0 |=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0 | 
=C2=A0cl_value=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0 | text=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0 | not null=C2=A0 | extended |=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 | 
=C2=A0vclf=
_number=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 | integer=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
 | not null=C2=A0 | plain=C2=A0=C2=A0=C2=A0 |=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 | 
=
=C2=A0cit_id=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 | =
bigint=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 | not null=C2=A0 | plain=C2=A0=C2=A0=C2=A0 |=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0 | 
=C2=A0cl_date_created=C2=A0 | timesta=
mp with time zone | not null=C2=A0 | plain=C2=A0=C2=A0=C2=A0 |=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 =C2=A0| 
=C2=A0cl_date_modified | timestamp with time zone |=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 | plain=C2=A0=C2=A0=
=C2=A0 |=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0 | 
Indexes:
=C2=A0=C2=A0 =C2=A0"cl_pk" PRIMARY KEY, btree (cl_id)=

=C2=A0=C2=A0 =C2=A0"cl_cnst_uk01"<=
/span> UNIQUE CONSTRAINT, btr=
ee (cit_id, vclf_number, cl_value)
=C2=A0=C2=
=A0 =C2=A0"cl_indx_fk01" btree (cit_id)
=C2=A0=
=C2=A0 =C2=A0"cl_indx_fk02" btree (vclf_number)
Foreign-key constraints:
=C2=A0=C2=A0 =C2=A0"cl_cnst_fk01" FOREIGN KEY (cit_id) REFERENCES citati=
ons(cit_id) NOT VALID=C2=A0=C2=A0 =C2=A0"cl_cnst_fk02"<=
/span> FOREIGN KEY (vclf_numb=
er) REFERENCES valid_cit_locator_fields(vclf_number)
 
= --_6379029B-5E11-46F5-B26D-8470255CB515_--