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 1j7JpX-0007fh-2c for pgsql-bugs@arkaria.postgresql.org; Thu, 27 Feb 2020 14:07:59 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1j7JpV-000059-PY for pgsql-bugs@arkaria.postgresql.org; Thu, 27 Feb 2020 14:07:57 +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_SHA1:256) (Exim 4.89) (envelope-from ) id 1j7JpV-000051-D4 for pgsql-bugs@lists.postgresql.org; Thu, 27 Feb 2020 14:07:57 +0000 Received: from mail-lf1-x144.google.com ([2a00:1450:4864:20::144]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1j7JpT-0004iH-9Y for pgsql-bugs@lists.postgresql.org; Thu, 27 Feb 2020 14:07:57 +0000 Received: by mail-lf1-x144.google.com with SMTP id b15so2183085lfc.4 for ; Thu, 27 Feb 2020 06:07:55 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=dataegret-com.20150623.gappssmtp.com; s=20150623; h=message-id:date:from:user-agent:mime-version:to:subject:references :in-reply-to:content-transfer-encoding; bh=4/aEKg3oeliZOcNeSmu8dEaJvfwDTT+Dpg032jqBrLg=; b=k5ZbBVUTxe3omvOFTuPwCI0oYd4Dd+MzThJS9U3YAxlcVwpwOQmlrJvRTmpYwDQZWq nxxsecHxTB3nZKTAsw7U+/hAf5vWr6fkyHa+QuGwuvsEqRwWb/HTp2IzGAwqwTVtA+7v DJDkazefbpx2PX+J00EETFUQhMaF+DQbJKCbZ0NNzkCe7yneKnzgjfanO5+ymfPSaTqk 3tQz/gPs7t77tJEpM+mAoC+ANToc/ZxQXbcemCoxTA7uVYRqSoaxeI6H4dxp0phapfBW tkezqTxmL8qZpe3mlrXZBP6yWs0JT+1tEFXOIEIPuxHpU3xepXbzg3mJ/zopGRIHuQkM VBfw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:message-id:date:from:user-agent:mime-version:to :subject:references:in-reply-to:content-transfer-encoding; bh=4/aEKg3oeliZOcNeSmu8dEaJvfwDTT+Dpg032jqBrLg=; b=FlFpu9mjbd+PTov0fIkaNb4i7H50d04KW/ztySf32P4UHz+40tpwmbxj4DwOguY6sj Qoren2hyu1XJVDB2C4hFHcHkiylUF8vC1NLrHeadcS3LpDLXvcB6mU1yBmZB5R/f8A8T 0x7xm3zR8J2HFxRccEouincxNNhPDP9N4NNK54F1/auY+4XI62dXDphJfb+iFtbipWVF lqqBINkL9ZRhaFnDD6rDkTLnCeiVH8nrhLW8W/B9M4dYejd69Ee2jwRxkIZC+C0S4zxv 2awaZRZYbYXzogkekdSCYAkFEyluC3xhKiUI/c682bOLtFZgFQ82vXsmDmm4ktZC2ACM cn0Q== X-Gm-Message-State: ANhLgQ1W1gQ/kUNstYMpfjgs9ogrfFceqWTHSTNr3uIQXrJLUFq3CgVu GjpDI/nW2d9oIp9w7R1qCPktzWQo3C0= X-Google-Smtp-Source: ADFU+vvWjBVWJRQ27Xp5y/Ry7XPJfV2JIxlFzbLTDdrHRzXbcg3Wjr7EPBiaLKrTVcvdkIOKoVS2vQ== X-Received: by 2002:a19:97:: with SMTP id 145mr2199342lfa.98.1582812474136; Thu, 27 Feb 2020 06:07:54 -0800 (PST) Received: from iMac.local ([213.87.126.161]) by smtp.googlemail.com with ESMTPSA id t1sm3280357lji.98.2020.02.27.06.07.52 (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Thu, 27 Feb 2020 06:07:53 -0800 (PST) Message-ID: <5E57CD37.4040908@dataegret.com> Date: Thu, 27 Feb 2020 20:07:51 +0600 From: Alexey Ermakov User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.14; rv:31.0) Gecko/20100101 Thunderbird/31.1.2 MIME-Version: 1.0 To: iserbin@bostonsd.ru, pgsql-bugs@lists.postgresql.org Subject: Re: BUG #16280: dead tuples (probably) effect plan and query performance References: <16280-e393fbb744eae7aa@postgresql.org> In-Reply-To: <16280-e393fbb744eae7aa@postgresql.org> Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 7bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk On 2/27/20 17:28, PG Bug reporting form wrote: > I can confirm that issue exists on 11.4 and 11.6 (updated to 11.6 since I > thought it was a bu that may have been fixed in 11.5 "Fix possible failure > of planner's index endpoint probes (Tom Lane)"), it is always reproducible, > and can be reproducted on prod and all lower environments. > I can't understand why it happens. As far as I understand there is something > to do with visibility map, but as per documention it should effect > index-only scans, not my case. > Main two questions are: > 1) Is it a bug? > 2) If it is expected behaviour - can someone please explain why it happens > and if there is any way to keep the good plan (without installing > extensions to force pin plans) > > Best regards, > Ilya > Hello, I don't think that's a bug, comparing these lines from both plans -> Bitmap Index Scan on content_idx (cost=0.00..155.07 rows=409 width=0) (actual time=4.932..4.932 rows=21952 loops=1) Index Cond: (content @> '{"anotherjsonkey": {"values": ["13"]}}'::jsonb) Buffers: shared hit=48 -> Bitmap Index Scan on content_idx (cost=0.00..27.11 rows=414 width=0) (actual time=4.287..4.287 rows=21952 loops=1) Index Cond: (content @> '{"anotherjsonkey": {"values": ["13"]}}'::jsonb) Buffers: shared hit=16 we can see that cost of bitmapscan is more in bad plan but actual time is about the same. I think that could be fixed by changing seq_page_cost/random_page cost to something like 1/10th of their current value (0.1/0.11) if your database is on SSD (or fits in memory). Change in cost possibly could be explained due to change in table size (if table size changed after vacuum). It's also possible to trigger autovacuum on that table more often by changing autovacuum_vacuum_scale_factor/autovacuum_vacuum_threshold (it's possible to do so on selected table only if necessary). In given case main reason of problem I think is that planner underestimate cost of rechecking condition on large jsonb value, AFAIK planner doesn't account width of the column but I might be wrong. -- Alexey Ermakov