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.89) (envelope-from ) id 1h6xkF-0000Jx-P1 for pgsql-hackers@arkaria.postgresql.org; Thu, 21 Mar 2019 13:28:32 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1h6xkE-0007Ko-Dy for pgsql-hackers@arkaria.postgresql.org; Thu, 21 Mar 2019 13:28:30 +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 1h6xkE-0007Kh-22 for pgsql-hackers@lists.postgresql.org; Thu, 21 Mar 2019 13:28:30 +0000 Received: from mimolette.dalibo.net ([212.85.157.144] helo=mail.dalibo.com) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1h6xk8-0006Gn-Ur for pgsql-hackers@lists.postgresql.org; Thu, 21 Mar 2019 13:28:28 +0000 Received: from [192.168.1.82] (lputeaux-658-1-177-253.w92-154.abo.wanadoo.fr [92.154.112.253]) by mail.dalibo.com (Postfix) with ESMTPSA id 234412C00C1; Thu, 21 Mar 2019 14:28:21 +0100 (CET) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=dalibo.com; s=a; t=1553174901; bh=eR5i5K1tbLux4LenF8hxDd+Ag5Wb5ErO/jXQnInNsL4=; h=Subject:To:Cc:References:From:Date:In-Reply-To:From; b=l6Jrc15XM5qBsQnNIDcRS8YnDATLwgr8wLXHnpHC1HkjNSVcPLJRcZ/30wC1yw0H5 bSM6VaNkRHgI5Zf9g1zJhYICE56Fm7WRqfuX4UtC+NrRWEUzRCeAPfl0rharfxL9jK IVB1x30fw7gBp0utY4Kn9IUwr0rx0MjwfzDm+LQ4= Subject: Re: Problem with default partition pruning To: Amit Langote , Yuzuko Hosoya , "'Imai, Yoshikazu'" Cc: 'PostgreSQL Hackers' References: <00e601d4ca86$932b8bc0$b982a340$@lab.ntt.co.jp> <010e01d4ce68$c0427b10$40c77130$@lab.ntt.co.jp> <0F97FA9ABBDBE54F91744A9B37151A5129A78A@g01jpexmbkw24> <001901d4daf5$1ef4f640$5cdee2c0$@lab.ntt.co.jp> <9bed6b79-f264-6976-b880-e2a5d23e9d85@lab.ntt.co.jp> <003501d4de21$8816f540$9844dfc0$@lab.ntt.co.jp> <9bb31dfe-b0d0-53f3-3ea6-e64b811424cf@lab.ntt.co.jp> From: Thibaut Openpgp: preference=signencrypt Autocrypt: addr=thibaut.madelaine@dalibo.com; prefer-encrypt=mutual; keydata= mQENBFf+BTsBCACz+LwN48a9f5sFlg4xEq6JTMdwFBGgDwewzOQUnVxnzh2WCyJ4WbMZW2P6 SmA/fy0iaw1K++fciQT6E/Q7P7bkiqXZlmAeHn0adlr2UIdb+qSjy26Hairxi1krsy9HP5pd ohiHElHzSU/PsTEjcYekwoO7MQpbGmavHUsRYdAep0eemDOXO68ig6X73/hqK0YQ9kkHVpNy f4/SWWIY6OUCeEWki0cR3r6ImGL/HPNHf1neYgWKBRvx1CtZrtcDXd5ynAEo8U1KPglmiy1U GVtgSIaGkTB2V3ApdREBYXUVTr1Mj15f4HeBI9qHhJgU3bx+6mr8Gsd0KWllcGULlO3bABEB AAG0MFRoaWJhdXQgTUFERUxBSU5FIDx0aGliYXV0Lm1hZGVsYWluZUBkYWxpYm8uY29tPokB NwQTAQgAIQUCV/4FOwIbAwULCQgHAgYVCAkKCwIEFgIDAQIeAQIXgAAKCRBdTH8gVQ+zNwj3 B/wPXJ/2kdPDIcKlAdT5eZQ2gxn4vqc+r29ZB3HACg8bAY740zyUjViU8u2i6k++FHNJmRPb aGREpWoBuSe0U9l9iMX50gvwVr+J/BbBd8WB9IrkjFfQkKtjkL6KKtoYtXiPpDzwfPMRt1DE ohaqbKUFufI/E73vxq7594No4kRiyA/gyGquiwf9PIvIT1OgFl+PgvkroOyBPSTDZWEkR++i GHZDLnCQb3hanMk0WqqWgVipL1G6ALPt6np2ofPW1BiY7nraAzlHIKbByMpBpQZ9hV9N9AlN rnDRl69rLgoETMmOslwqLQzek/76+BuHkRHP3M+MJy53Xskg7FAtshI6uQENBFf+BTsBCACw kh2fNF4TW3j4SrcMt5P+a0FC4fOQga2k4/GgFM9XIAhWZ77zYRhg4Ezie4GUiBNa9FVxOK41 +D0O00KlYL+5VCtEGAIyaXmeg7qjjFQuA7OV8GmUwdmbfnU961fz1ioBJuPU0a6Z79nNltdv x1SWS9KRrQvdbMjgkJkG+K2fIfFZQI1y4anqFZ0pVMFCRztVE0M3V6L/EcRLj8lnlblLDJVY 0n4TJXQ6o/H9iMPQg8SL9f8pyP0Q9ONcwJQ4lTtJDzf5U0PBeAtPQEPrybo8exoa/8DbZH9G M7NfefXdzoAGeL3g5/nDd4wkSzI3H2lV+pDnIG27XAboaehLb/lHABEBAAGJAR8EGAEIAAkF Alf+BTsCGwwACgkQXUx/IFUPsze/sgf+N6R1ZmkSoFVPjMavEZLTn40P/LRt+YslDxmD46lL vkoDk0oyU3YPPReAffp2DnH47nFHsx+ozi1otUn7ZlBjD73NWnK887PgjjZXj9Fp1DabZxg3 ssGOEZuYAFZueymqaqFjedwv6oghDMyUWiCPXHw/OlBssOXypP1nTqF62ZB4F+7GtX78leiN RjtM/vn5E5T/AFFkWePkkuilq6VWZ9T4KUiYy9Wsm93z0R5IrenYQ3/JMnMwX1Gjfi9S39VZ 2KxBJ74fpnZwOWytkiApnmAUjJ8DvKvcm3YjHww0DRPrn/FEn+r36pSlIPdh7vvaMgQFp8j4 95b/FmS2nYA/mLkCDQRX/gn0ARAAvICBGRpkKxGstczKo/6FrfK8/w32aiYP+uRETVW4Ccq7 QkXzSIJtwqFfQL/lFN62UyT3NsacF2u4pEw1Gj08wjOQBud5FshyeZF/XhJ1EzdmmGoW3iA0 mo+N8ItQIRoXkjfWztIJm0OnAFTzeK8UqdpTYCRtdqVnWQ44et9Emt5/y3rYqpbLx7eqnUvv O2EKTwcx1oS9rwVLtZ2UIrnRmqjGez/uHXg2Ne5F3rA4dOLakjlklxaniDiKXHw/DoaadENH v6uYVVvTtnNXjN6gPBrMP35sm/xwO/H/hBswhYHRCvz8OJptWQ6p7FXu6z+NiS9s6CvqEt5H TOvWqR8SXyPW4Tx2CqCany5yl0XQeHPYnC1pWSqYD25Rvyv8bR43G7W0InKbtnlW+z9ceBdH KIh4LEXdSMr5w1HoQZerS9AyX5PP/LHdk2+/dLIMPYXVlOan6sJs3TrvmQ/x7FTC766jVCFm Z51RmRSKxjTNeWWqU4HHedOukp2cp6hnuNy1jDfeoL0m/UslYBgHgCBp6icb2yvCe3lAuvej JATU+6XGzAPPKT2Fcs2Vgm90O5dInH4cF1CCypWtW1kNSFxLXXLFq6HP83tEAiVUCsakcOeX FPJS49Y/6V5PgeOehCEXFAY1r8CzvLOgK7WFCHV4f36DJFWHzif4NGn7OfJ2UtsAEQEAAYkD RAQYAQgADwUCV/4J9AIbAgUJBaOagAIpCRBdTH8gVQ+zN8FdIAQZAQgABgUCV/4J9AAKCRB9 /R2VHKO1Xud9EAC0jBe6mug2a7hshvu257y8NIwtDEkjQacZ/huo0SqYwMi30/pcg6YhXvad VFfxSgowuRl0UWi0ksHTilAZfSC1PlykJZSQd9QJ2r0ekE+iQj52QGxhGhXUsUs1kmVLsMWm m7FMY0htfcCsCAyJimkGRSASR9LfcHysKjQTV3FudXjqM2fVMTSkMxJEn2HlKcrdulsGPiU4 ExtDNMbiLsp/NXUpGA2t/HxUkYIOlG0OhceWLRr0UBLN6SnpFUk3Q2+65bTdMiuHSLdPb+XQ xwLEu+QIA/7yDzHSqsGlddHVy7VDPDiC4PFv43ptkz0H5NNatLWvnEMIUcx3EU3Q88gL6CAi 9r8NkPGN3bPbtbjBPfq7dXGystF7Rc83uU8MjcowUD/Aor1QjVPmSdBA9YbO/MXO/FNUEz+B HiI5F/ZojNgKDig9KJugV+GQHyeEHqZBI5Bkllw01UK8l7rvDdyXC+0b0kSsaU7HQ2yPVVT2 5p+zT0pDrgNxDZMOPOpPt7HfmCafGsJfx1a8zaKhIURW44Vdo46e6rW9c/TYDgGawjJMlihB /K4fhieTelvHuvwhw6HRygc//R4Od+kRr0CNLv5AkYG9Ygp9P4NQgHImKCWCDMI/LZauPGhz bNbVlQ80/0xq3418aXEqYeMm6pipCis3as+IZLpJkutnQ6oLgG2kB/439nGD0Wtgm51mDOqs NWnXvZNfX9Ok0W0br1fQRO6olmf8lwRtXOIIxElVtXKfZfuqSOuzNI+68djYmeOag+E2oHY8 4EubPeoN4iUSeNFzz9VbeOovpuXYocZsV//Ii3LlcWNLeBdhSUMU5PJeMi76n4q+wuzykwyG JKkRUrE9BKFvlIBGUeKTDN/2toIuFAuXF5UYjxDE5vPfZ2JXhtXCzQMWGHxyjIF8T7j9pz7o 0D/7DqAz9a217brW7QDH90Jc7dy/8KxkYf26aCK552SmPS6zxsAWaDC/AMlzH0VaRS7J+ZFr 2/rHHUz0Zorv3a04WzPHrzRu/VhkiOuFGzBouQINBFf+CuoBEADsOywyDOKwHVWw3MU9cHW2 x1HoZOJl822yyrZRCkw4sn71Ef33LKaRVgezSTK0ZaiYl/UHs3/NpZ7jVVnNPLF8+z6sCuvr XGJABXhHGd36IaBhk61PW917GwvpQW6nziITc+pQXd36b+AwMeU8ZfFrRTaMG+9Zn0G+Yc0Z NvVfdQSUYlMiUjWbL4u1fOLsJdtNO6hob9Naxhsnc+ROc0lIu2kXfhjbhKKCBRyt0p7OGdaL FU2c63zGANGVo2vbqRApmiA0OVrtXv4GOO8X5BSUNsOgKBCmZhJy7FKRdG/JftYqSYf6Unyj 2BduEFRZWxATjN+3Ij6Zwfl9bRqm73ps9rATSrvEOkk0ZgiYd0fvFPrh7xMqjPzHKz01wihs MYC1vXyWXZdUVmkrLNGIt4oCNq0MW+AEP7wje8zAKEFhPGMEY+ok3gqNcmDLzixBRJSudPe1 rv7zkSzxrBuOmU/LxpVtqNnteyEd8iLPK1eE1FONi7yduZ8SQAYWbcD9QYx+1zsm07Ni2iy0 +2lzZANJZxo87wAZz+5PIybbHjOpKh/xcgu1UPTi+waNDMFy8H+aKo2pEbAs6LwIHBRLydMa d2/Kw6C1EKSPqYiFgD5Y1RZGlV/Qtd6xiEoQQiRvqAJ3ub/nwCMXPkIAz9C2pdC4zJbasY5t malb8dtuKrKasQARAQABiQElBBgBCAAPBQJX/grqAhsMBQkFo5qAAAoJEF1MfyBVD7M3jQAH /iZiSOUxxEvAaTjwXdQTrJKUEVA6SePi1aezuXbLlk+3iDI73ic6/HcR/B7gIOFr2ApbKdTh OMDir0h7rwoCFbptVsjoMHNP83TsbAl7a69sETkHfXg9ewlNQcZzSxXUOAw9WqhlIuHhsla+ RSeErqQKSUh0yEKs6xntACzQjLdEFXrPuIRcBAbqNcX+GMzLxsE3ndJN7oHjYmihkVzif/qJ cZrZ9offXYGksr7+ziYFyfloYnEZHMbX9E+RHogYqyGvHKsetGUgTsHBnaoLe/ILQWgMLtCw 1g8+sR6tYxWyh4dtmbIsLTkrMNa+BO+gEpoMbAx5zO1i6naXv7ZC3LQ= Message-ID: Date: Thu, 21 Mar 2019 14:28:16 +0100 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101 Thunderbird/60.5.0 MIME-Version: 1.0 In-Reply-To: <9bb31dfe-b0d0-53f3-3ea6-e64b811424cf@lab.ntt.co.jp> Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk Le 20/03/2019 =C3=A0 10:06, Amit Langote a =C3=A9crit=C2=A0: > Hi Thibaut, > > On 2019/03/19 23:58, Thibaut Madelaine wrote: >> I kept on testing with sub-partitioning. > Thanks. > >> I found a case, using 2 default partitions, where a default partition = is >> not pruned: >> >> -------------- >> >> create table test2(id int, val text) partition by range (id); >> create table test2_20_plus_def partition of test2 default; >> create table test2_0_20 partition of test2 for values from (0) to (20)= >> =C2=A0 partition by range (id); >> create table test2_0_10 partition of test2_0_20 for values from (0) to= (10); >> create table test2_10_20_def partition of test2_0_20 default; >> >> # explain (costs off) select * from test2 where id=3D5 or id=3D25; >> =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=A0Append >> =C2=A0=C2=A0 ->=C2=A0 Seq Scan on test2_0_10 >> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Filter: ((id =3D 5) O= R (id =3D 25)) >> =C2=A0=C2=A0 ->=C2=A0 Seq Scan on test2_10_20_def >> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Filter: ((id =3D 5) O= R (id =3D 25)) >> =C2=A0=C2=A0 ->=C2=A0 Seq Scan on test2_20_plus_def >> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Filter: ((id =3D 5) O= R (id =3D 25)) >> (7 rows) >> >> -------------- >> >> I have the same output using Amit's v1-delta.patch or Hosoya's >> v2_default_partition_pruning.patch. > I think I've figured what may be wrong. > > Partition pruning step generation code should ignore any arguments of a= n > OR clause that won't be true for a sub-partitioned partition, given its= > partition constraint. > > In this case, id =3D 25 contradicts test2_0_20's partition constraint (= which > is, a IS NOT NULL AND a >=3D 0 AND a < 20), so the OR clause should rea= lly > be simplified to id =3D 5, ignoring the id =3D 25 argument. Note that = we > remove id =3D 25 only for the considerations of pruning and not from th= e > actual clause that's passed to the final plan, although it wouldn't be = a > bad idea to try to do that. > > Attached revised delta patch, which includes the fix described above. > > Thanks, > Amit Amit, I tested many cases with nested range sub-partitions... and I did not find any problem with your last patch=C2=A0 :-) I tried mixing with hash partitions with no problems. =46rom the patch, there seems to be less checks than before. I cannot think of a case that can have performance impacts. Hosoya-san, if you agree with Amit's proposal, do you think you can send a patch unifying your default_partition_pruning.patch and Amit's second v1-delta.patch? Cordialement, Thibaut