Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1r4524-007GXz-7q for pgsql-general@arkaria.postgresql.org; Fri, 17 Nov 2023 20:01:40 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1r4513-00FZCD-QU for pgsql-general@arkaria.postgresql.org; Fri, 17 Nov 2023 20:00:37 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1r4513-00FZC5-FN for pgsql-general@lists.postgresql.org; Fri, 17 Nov 2023 20:00:37 +0000 Received: from mail-lj1-x229.google.com ([2a00:1450:4864:20::229]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1r450z-006x9i-RR for pgsql-general@lists.postgresql.org; Fri, 17 Nov 2023 20:00:35 +0000 Received: by mail-lj1-x229.google.com with SMTP id 38308e7fff4ca-2c85a5776a0so31589131fa.0 for ; Fri, 17 Nov 2023 12:00:33 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1700251232; x=1700856032; darn=lists.postgresql.org; h=to:subject:message-id:date:from:mime-version:from:to:cc:subject :date:message-id:reply-to; bh=CpRPXkCdWmM6uBtmcPCP/R6vc7VgSl2bNoEAELfjHJA=; b=WYfkeSVCiPAt9Ki/28tCG1EDPLxgDxfjf8ALSpQrG9zk32mae9tDBtiRNs7D395yg3 YFR+b9bVZhuFFFOQFuJM9XkZNi8rVJ9gWvxrM/OKaHq1hyrYDGtjXfPbB/Fo/A7YIo6+ mT7qp8QGlzz6TplHUBwJbihFzXFvqmJTkOfy2txdQIoqKTgtirGI3TIymjo70bHhslCI qMhoUaMvFcSbYQmBPD7InZR8MrVLgObV8YxrLQjJWNud/+CHqkvM982Bot5fxWlkm841 ajD7IDSzj1dzD8MynjH1nDt/JhiaEFbht7VXYYAtVsvo/4pCDODq1myE88GRObNZhnEb SvPQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1700251232; x=1700856032; h=to:subject:message-id:date:from:mime-version:x-gm-message-state :from:to:cc:subject:date:message-id:reply-to; bh=CpRPXkCdWmM6uBtmcPCP/R6vc7VgSl2bNoEAELfjHJA=; b=KlB47rQuEIPlMRpsn36+9GVwTwR5nEE44TyHujop/FSq+FTg6tIefcd/69iEs1n7lv 8XoqcQKPfEMy04eUqa+NMzU0G45yBpn+n8PvwW/gvBvRWUtAPy6suZ+IZM/AMlmgkDUo 127VdsCmwHbjIqM9yeFicvc3J4fQmaSY51rg/eh+MP72aFRxkhNS2dzPCo6SFleWbT1Z l3Wfg+4qv7FqZJnhN2NEoMR7GO6fsSX3dh/fbJJ0MdOmbKHRYTH94OfLYL2f6/wEYA0r BgNqulSusQTW2bHb/Fhlj8cZzhTtt+PRuovp9ABNPRkjSSoxEM0R3VkofS53Gb9+QgQI aMxQ== X-Gm-Message-State: AOJu0YyyJJ1LJkI//myhiSHh3ek/YAo0WcD9ypl3a1fIomHaFHVKNVCu CWyLjRerlomQv4qNcmEE/3I84XKsZlj7Cv8m5KlfZMQHhQ== X-Google-Smtp-Source: AGHT+IGuTQPXN/sdB1u8k/jTBBrJTrbS55UcrO8V1MIk0O858AaVEoFkEAHzNLeUNyrtno8DAf5zZedB7BxSyutStis= X-Received: by 2002:a2e:b910:0:b0:2c6:f15a:36db with SMTP id b16-20020a2eb910000000b002c6f15a36dbmr462424ljb.2.1700251231828; Fri, 17 Nov 2023 12:00:31 -0800 (PST) MIME-Version: 1.0 From: =?UTF-8?Q?Torsten_F=C3=B6rtsch?= Date: Fri, 17 Nov 2023 21:00:20 +0100 Message-ID: Subject: query plan To: pgsql-general Content-Type: multipart/alternative; boundary="000000000000b3c11a060a5e961f" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000b3c11a060a5e961f Content-Type: text/plain; charset="UTF-8" Hi, This is part of a query plan: Nested Loop Left Join (cost=26.32..47078866.36 rows=1344945195 width=626) -> Nested Loop Left Join (cost=25.74..5312.48 rows=1344945195 width=608) -> Nested Loop Left Join (cost=6.79..2876.77 rows=102 width=373) -> Nested Loop Left Join (cost=1.90..1965.51 rows=102 width=361) -> Bitmap Heap Scan on ... (cost=4.89..8.91 rows=2 width=28) -> Hash Left Join (cost=18.95..42.61 rows=3 width=243) -> Hash Left Join (cost=18.94..42.59 rows=3 width=203) -> Hash (cost=0.00..0.00 rows=1 width=48) -> Memoize (cost=0.58..4.59 rows=1 width=172) What I don't understand is this. The left node of the join is expected to return 102 rows. The right node 3. How can this result in >1e9 rows? The query involved way further down a partitioned table with 2 partitions, one pretty big in the 1e9 rows range, the other practically empty. The big partition had been analyzed before. But the partitioned table and the empty partition never. After analyzing them all was well. I am just curious to understand how that number is calculated. This is PG14. Thanks. --000000000000b3c11a060a5e961f Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi,

This is part of a query plan:=

=C2=A0Nested Loop Left Join =C2=A0(cost=3D26.32..47078866.36= rows=3D1344945195 width=3D626)
=C2=A0 =C2=A0-> =C2=A0Nested Loo= p Left Join =C2=A0(cost=3D25.74..5312.48 rows=3D1344945195 width=3D608)
= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0Nested Loop Left Join =C2=A0(= cost=3D6.79..2876.77 rows=3D102 width=3D373)
=C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0Nested Loop Left Join =C2=A0(cost= =3D1.90..1965.51 rows=3D102 width=3D361)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0Bitmap Heap Scan on ...=C2=A0 (cost=3D4= .89..8.91 rows=3D2 width=3D28)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-> = =C2=A0Hash Left Join =C2=A0(cost=3D18.95..42.61 rows=3D3 width=3D243)
= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0Hash Lef= t Join =C2=A0(cost=3D18.94..42.59 rows=3D3 width=3D203)
=C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0Hash =C2=A0(cost=3D0.0= 0..0.00 rows=3D1 width=3D48)
=C2=A0 =C2=A0-> =C2=A0Memoize= =C2=A0(cost=3D0.58..4.59 rows=3D1 width=3D172)

What I don't understand is this. The left node of the join is expecte= d to return 102 rows. The right node 3. How can this result in >1e9 rows= ?

The query involved way further down a partitione= d table with 2 partitions, one pretty big in the 1e9 rows range, the other = practically empty. The big partition had been analyzed before. But the part= itioned table and the empty partition never. After analyzing them all was w= ell.

I am just curious to understand how that numb= er is calculated.

This is PG14.

Thanks.
--000000000000b3c11a060a5e961f--