Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1mVIkR-0003ml-A1 for pgsql-performance@arkaria.postgresql.org; Tue, 28 Sep 2021 19:26:39 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1mVIkP-0004IJ-U8 for pgsql-performance@arkaria.postgresql.org; Tue, 28 Sep 2021 19:26:37 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1mVIkP-0004IA-FR for pgsql-performance@lists.postgresql.org; Tue, 28 Sep 2021 19:26:37 +0000 Received: from outbound5d.ore.mailhop.org ([100.20.105.3]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1mVIkM-0002CD-Ji for pgsql-performance@lists.postgresql.org; Tue, 28 Sep 2021 19:26:36 +0000 ARC-Seal: i=1; a=rsa-sha256; t=1632857193; cv=none; d=outbound.mailhop.org; s=arc-outbound20181012; b=kH3OqiJRNA1TPVp9JJo7qIgBR2k+2xO8bnT2/qa7/pxC6PBtCW15RVBks2hGGs2444kPgsUruRYVF fgesnsmdAPHxvWfSbon/iu2PaIOcmmWuzqPHi9WtT/22EB/T9dwowjMqGvoiNw1X6h9futxxhreLrj pQt6dgoZZ/nOAe2C7pOrWp6PrTjVnK+6Fx9FYfTr5Xi5YPyhAdTioxjp56ZK/UtE60IYGK/QcNy7iB ENZ8iorJMr0b0dAW4IgvUxd/W4+f6RhwQjbvsEXFSFXTzhXZ1qnqTIcC3aOtyhH0yaps85XiFVorlG 6+TfGs8hR9C66B7eBCu5KE1dBUUiYHA== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=outbound.mailhop.org; s=arc-outbound20181012; h=mime-version:content-type:references:in-reply-to:date:to:from:subject: message-id:dkim-signature:dkim-signature:dkim-signature:from; bh=z3WPtxDGITLaI17yYQJplEMUwxjRySC7C+EhYzavyR4=; b=oXnUrmWqMCuytZvxNtPASsGcx+Wa1di8znUGd1N6w+606QRVBHvQfYaB1rgOQqNxviv0RIxfuoL8N WFlpN8kxJOGjrC7rrycB5Xeh3fYhAmQ/jQYeNyvfGZHBL9EgFAs1pFO/BlLBOeCBlX/zqeWmyYbhxf pWFqxg7ymylGoE468NDAs7nDFXhPWc+g2CNbr66DzQub1xyVwo9x8mKiWxD6qZ8KQ5kPvE3dQ7T/I5 tqMGPUOQfWicBFGPMj3oRwknLrWlBCb4BidS/JNan2mDKoghrxHLVtjbdQ83oDVChfaWt4ADoZKQkD l63n2ii/4g1vwC3JvO7IjEC7I1+UVTA== ARC-Authentication-Results: i=1; outbound3.ore.mailhop.org; spf=pass smtp.mailfrom=lists.simkin.ca smtp.remote-ip=74.50.227.220; dkim=pass header.d=lists.simkin.ca header.s=athena header.a=rsa-sha256 header.b=NyXWj5nE; dmarc=pass header.from=lists.simkin.ca; arc=none header.oldest-pass=0; DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=lists.simkin.ca; s=duo-1610662143981-d3c4c825; h=mime-version:content-type:references:in-reply-to:date:to:from:subject: message-id:from; bh=z3WPtxDGITLaI17yYQJplEMUwxjRySC7C+EhYzavyR4=; b=F27OXm8EJQYVl3NgO611Xag0Fcjlns+UDUEtp2IlOYjyhLgTRUh0wQ/rvqYEuH/K+wBAsnnntqwdT bl1CbR7xo4hAy/DRJXhmOr+umI8Hi45QZxUIvTBBGhVmDW5SQuHYND1sfbQSG9/gFn/E7qWwk+jYqj 3SNlxiG5C6k69wGs= DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=outbound.mailhop.org; s=dkim-high; h=mime-version:content-type:references:in-reply-to:date:to:from:subject: message-id:from; bh=z3WPtxDGITLaI17yYQJplEMUwxjRySC7C+EhYzavyR4=; b=ayM3+Oun6XtqREyiJ5HpDb7UaNozXOVqWFrKWTfKJtcWeHZBkgSBnlezE3xyINT8wVnfS/VMpE/ZT pZel8vhpcpEDorNCKFPponaqvdnJEc/dBKY5Ii7e2qCjuQ2YBS+Hj1YCFeRZhaqsTgs9l+wr/hVjD3 Sm+qU0JJlgrdINv2YIh3fIzAZaxNRxrkN8uVIEQSUT/qOuBX810MrBKdyyzEUxC4oDYpFGDufNe77R AUKn0L9lz+OqJguvhwCzuNvpe+sYkT4B1clujl2/eqDQ0aAI9vWaDkNP76pS28XXXWzJu41kaTGsKS w2cWdtHQaSy3TzqXMpRBCwOE8E2XjOg== X-Originating-IP: 74.50.227.220 X-MHO-RoutePath: c2lta2lu X-MHO-User: fb6c1343-2091-11ec-9662-bf9d68d023b6 X-Report-Abuse-To: https://support.duocircle.com/support/solutions/articles/5000540958-duocircle-standard-smtp-abuse-information X-Mail-Handler: DuoCircle Outbound SMTP Received: from athena.simkin.ca (athena.simkin.ca [74.50.227.220]) by outbound3.ore.mailhop.org (Halon) with ESMTPSA id fb6c1343-2091-11ec-9662-bf9d68d023b6; Tue, 28 Sep 2021 19:26:31 +0000 (UTC) Received: from skynet.simkin.ca (node-1w7jr9sr73xr4ze19xwgjcteu.ipv6.telus.net [IPv6:2001:569:bce0:6400:fa75:a4ff:fe2f:92e6]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange X25519 server-signature ECDSA (P-384) server-digest SHA384) (No client certificate requested) by athena.simkin.ca (Postfix) with ESMTPSA id F1BCFDC6970C for ; Tue, 28 Sep 2021 12:26:29 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=lists.simkin.ca; s=athena; t=1632857189; bh=JUM0LqULS8+dcieQsiT+OxmbfzYyPdOWWzwx+CyCPSY=; h=Subject:From:To:Date:From:To:Subject:Date:Reply-To; b=NyXWj5nEfdvI9qcrHqYpTeLyY1IBDsQxbvxYYJVpl17Tm8fbOLo+8jsEglTK8jMHZ EFYZb5bawIhclF9onYuzSSNRCrm0bcG3xSiCFyV6woFMfgchBbWuImoMuKXHaPSk+w iPOc7BTSfh0golTxkdWG74j7UPsvtQOMLPXaGNXT6emrX6G0o2elaN8197EeZWlCZy i/DUi0/BhAuvtu6IIh0QnBWYCqutDOFxW/R6dPFWi4MOCy33EzcQmXHoLOAI8T9Ae7 n5c+bHzn+HkGA10TuO8LCNrwIwtcufYwgwSt6UVtq6yM37eXZyw/Fy3CGveV4SiZ/Q E3z7l2rZ65DWg== Received: from skynet.simkin.ca (skynet.simkin.ca [IPv6:::1]) by skynet.simkin.ca (Postfix) with ESMTP id E96AE1065EB65 for ; Tue, 28 Sep 2021 12:26:23 -0700 (PDT) Message-ID: Subject: Re: Problem with indices from 10 to 13 From: Alan Hodgson To: pgsql-performance@lists.postgresql.org Date: Tue, 28 Sep 2021 12:26:23 -0700 In-Reply-To: References: <8d2ba21d-2318-4f02-a8fd-04c7d97e22ab.e854021a-9da0-4eba-a51f-e5ff782985ef.17ca0caa-f521-44ce-817a-17cbc713d135@emailsignatures365.codetwo.com> Content-Type: multipart/alternative; boundary="=-hg74IHiD4z6RmfbNbe9A" X-PGP-Key: fp="EF82 6F8D A3BA A096 FFA3 05F2 A7A0 40A5 34E5 AA0B"; id="0x34E5AA0B"; get= User-Agent: Evolution 3.40.3 MIME-Version: 1.0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --=-hg74IHiD4z6RmfbNbe9A Content-Type: text/plain; charset="ISO-8859-15" Content-Transfer-Encoding: 8bit Em ter., 28 de set. de 2021 às 12:40, Daniel Diniz escreveu: > > Hello I migrated from postgres 10 to 13 and I noticed that there was a big > > increase in a querie that I use, I did explain in 10 and 13 and the > > difference is absurd, the indices and data are the same in 2. I've re- > > created and re-indexed but I don't know what changed from 10 to 13 which > > made the performance so bad, I don't know if it needs some extra parameter > > in some conf on 13. > > > > Postgres 13 > > > > "QUERY PLAN" > > "Limit  (cost=1.13..26855.48 rows=30 width=137) (actual > > time=10886.585..429803.463 rows=4 loops=1)" > > "  ->  Nested Loop  (cost=1.13..19531164.71 rows=21819 width=137) (actual > > time=10886.584..429803.457 rows=4 loops=1)" > > "        Join Filter: (h.ult_eve_id = ev.evento_id)" > > "        Rows Removed by Join Filter: 252" > > "        ->  Nested Loop  (cost=1.13..19457514.32 rows=21819 width=62) > > (actual time=10886.326..429803.027 rows=4 loops=1)" > > "              ->  Nested Loop  (cost=0.85..19450780.70 rows=21819 > > width=55) (actual time=10886.259..429802.908 rows=4 loops=1)" > > "                    ->  Index Scan Backward using hawbs_pkey on hawbs h > >  (cost=0.57..19444209.67 rows=21819 width=46) (actual > > time=10886.119..429802.676 rows=4 loops=1)" > > "                          Filter: ((tipo_hawb_id = ANY > > ('{1,10,3}'::integer[])) AND ((nome_des)::text ~~* > > convert_from('\x255354455048414e592053544f4557204c45414e44524f25'::bytea, > > 'LATIN1'::name)))" > > "                          Rows Removed by Filter: 239188096" > > Index Scan Backward looks suspicious to me. > 239,188,096  rows removed by filter it's a lot of work. > > Do you, run analyze? PostgreSQL has an unfortunate love of scanning the pkey index backwards when you use LIMIT. Try pushing your actual query into a subquery (with an offset 0 to prevent it being optimized out) and then do the LIMIT outside it. --=-hg74IHiD4z6RmfbNbe9A Content-Type: text/html; charset="utf-8" Content-Transfer-Encoding: quoted-printable Em ter., 28 de set. de 2021 =C3=A0s 12:40, Dani= el Diniz <danie= l@flashcourier.com.br> escreveu:
Hello I migrated from = postgres 10 to 13 and I noticed that there was a big increase in a querie t= hat I use, I did explain in 10 and 13 and the difference is absurd, the ind= ices and data are the same in 2. I've re-created and re-indexed but I don't= know what changed from 10 to 13 which made the performance so bad, I don't= know if it needs some extra parameter in some conf on 13.

Postgres 13
"QUERY PLAN"
"Limit  (cost=3D1.13..26855.48 rows= =3D30 width=3D137) (actual time=3D10886.585..429803.463 rows=3D4 loops=3D1)= "
"  ->  Nested Loop  (cost=3D1.13..19531164.71= rows=3D21819 width=3D137) (actual time=3D10886.584..429803.457 rows=3D4 lo= ops=3D1)"
"        Join Filter: (h.ult_eve_id= =3D ev.evento_id)"
"        Rows Removed by = Join Filter: 252"
"        ->  Nested= Loop  (cost=3D1.13..19457514.32 rows=3D21819 width=3D62) (actual time= =3D10886.326..429803.027 rows=3D4 loops=3D1)"
"     &nb= sp;        ->  Nested Loop  (cost=3D0.85..= 19450780.70 rows=3D21819 width=3D55) (actual time=3D10886.259..429802.908 r= ows=3D4 loops=3D1)"
"             &= nbsp;      ->  Index Scan Backward using hawbs_pkey = on hawbs h  (cost=3D0.57..19444209.67 rows=3D21819 width=3D46) (actual= time=3D10886.119..429802.676 rows=3D4 loops=3D1)"
"    = ;                     &nb= sp;Filter: ((tipo_hawb_id =3D ANY ('{1,10,3}'::integer[])) AND ((nome_des):= :text ~~* convert_from('\x255354455048414e592053544f4557204c45414e44524f25'= ::bytea, 'LATIN1'::name)))"
"           =                Rows Removed by Filt= er: 239188096"
Index Scan Backward looks= suspicious to me.
239,188,096  rows removed by filter it's = a lot of work.

Do you, run analyze?

PostgreSQL has an unfortunate lov= e of scanning the pkey index backwards when you use LIMIT.

Try pushing your actual query into a subquery (with an offset 0 to= prevent it being optimized out) and then do the LIMIT outside it.

--=-hg74IHiD4z6RmfbNbe9A--