Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.92) (envelope-from ) id 1j7LHo-00026c-Oo for pgsql-bugs@arkaria.postgresql.org; Thu, 27 Feb 2020 15:41:16 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1j7LHn-0003VO-Gj for pgsql-bugs@arkaria.postgresql.org; Thu, 27 Feb 2020 15:41:15 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1j7LHn-0003TY-5E for pgsql-bugs@lists.postgresql.org; Thu, 27 Feb 2020 15:41:15 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1j7LHk-0002UP-Tj for pgsql-bugs@lists.postgresql.org; Thu, 27 Feb 2020 15:41:14 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.4/8.14.4) with ESMTP id 01RFf6bD021505; Thu, 27 Feb 2020 10:41:07 -0500 From: Tom Lane To: iserbin@bostonsd.ru cc: pgsql-bugs@lists.postgresql.org Subject: Re: BUG #16280: dead tuples (probably) effect plan and query performance In-reply-to: <16280-e393fbb744eae7aa@postgresql.org> References: <16280-e393fbb744eae7aa@postgresql.org> Comments: In-reply-to PG Bug reporting form message dated "Thu, 27 Feb 2020 11:28:11 +0000" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <21503.1582818066.1@sss.pgh.pa.us> Content-Transfer-Encoding: quoted-printable Date: Thu, 27 Feb 2020 10:41:06 -0500 Message-ID: <21504.1582818066@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk PG Bug reporting form writes: > Issue description: > After some minimal activity in database first plan changes to the second= . > Analyze on table1 do not help (tried with various default_statistics_tar= get > values). > content_idx index recreation helps for some time, but several minutes la= ter > plan degrades back to second one. > The only thing helped (surprisingly) is vacuum. It also helps for some t= ime, > but once number of dead tuples reaches something like 300-500 - plan > changes back to second one. Surely it is *not* a bug that dead tuples affect the plan choice. The density of live tuples is an important factor in the relative costs of different table scan techniques. In the case at hand, I wonder why your rowcount estimate is off by a factor of 50: -> Bitmap Index Scan on content_idx (cost=3D0.00..155.07 rows=3D409 = width=3D0) (actual time=3D4.932..4.932 rows=3D21952 loops=3D1) Index Cond: (content @> '{"anotherjsonkey": {"values": ["13"]}}'::j= sonb) Buffers: shared hit=3D48 If you can't improve that you're not likely to get a good plan, and futzing around with cost factors to make this particular query do "the right thing" anyway is inevitably going to make things worse for other queries. Maybe a larger stats target for the content column would help, but I fear that this @> condition is just beyond the ability of the planner to estimate. You might need to redesign the data representation to make it a bit more SQL-friendly. regards, tom lane