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 1jGEQO-0001PU-ON for pgsql-performance@arkaria.postgresql.org; Mon, 23 Mar 2020 04:10:53 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1jGEQN-0005W0-9d for pgsql-performance@arkaria.postgresql.org; Mon, 23 Mar 2020 04:10:51 +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 1jGEQM-0005Vs-SM for pgsql-performance@lists.postgresql.org; Mon, 23 Mar 2020 04:10:51 +0000 Received: from mail-qt1-x843.google.com ([2607:f8b0:4864:20::843]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1jGEQJ-0003mR-3y for pgsql-performance@lists.postgresql.org; Mon, 23 Mar 2020 04:10:50 +0000 Received: by mail-qt1-x843.google.com with SMTP id t17so477806qtn.12 for ; Sun, 22 Mar 2020 21:10:46 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=telsasoft-com.20150623.gappssmtp.com; s=20150623; h=date:from:to:cc:subject:message-id:references:mime-version :content-disposition:in-reply-to:user-agent; bh=mvdWrhylgeUuM9+WeasuBiYAYbPH50Af3lWiaXzXbQ0=; b=kw6vgMwJL5Pj78LlnthFK6evyr758GrcppH9bSjqqwTWRignhDPMcR/d+T4dXLwqQn cUjO2zBdojH43VC3uG07+BRLa3VGSRyx42IKpz1hXV8TmKOXgc+eG9eq0EtO3tbxacoz vBNLF+COIargtKh1mo/VUCSDYeT4skbrXOrr6r9xnA0In+sk0chqq8ZQpSruE66U6dCf cYAJ8/xV8+YGYuPPC6N/sklWpLTDdXhMp1dNhCnIZD75/38G9L/u7WjK/suQ7jyjDXFc orzmAEVzR0w+oxC6lx+hbp+AqCO/vFFF5noGTEXepC4XUvPBqY+mjYd9fnMQ1jfbiaBB RMBw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:date:from:to:cc:subject:message-id:references :mime-version:content-disposition:in-reply-to:user-agent; bh=mvdWrhylgeUuM9+WeasuBiYAYbPH50Af3lWiaXzXbQ0=; b=SU988OdlT3br/quOhNhwKhTYclXXBlRQnaMAhM4tcwaHkDFQTkgmmGDOEDzsBmLGKQ AENCGN9UnLkUipXmqA6j+K6ewihp1Jic1YKwFxsfl2UaBKwoHwdMhGNst6CYmj2F26ax hmty3AyGkd3Boadoq6dwmSl/GlSnTKGv1RZvUkpWl6vn6Ezp2aQHuSvLv0EkQyE/ZqIm YYNoJAWHkY4aLpkng16tJ+U/hhbD/I8i5818kmb8eyKpe/+Vf6Yg8iJSbHigm70TDTLc V1rLWyaqdzl+P+k9HqlZKePRBPsLbjGldO1DSnm4NFx0TZWh178OdzRHLiaqqmogj1/5 KaiA== X-Gm-Message-State: ANhLgQ008pf7oLJD8PA7SPOVOVcctZW72UPJFOQvCGFSX9I3evxBExRd KzV8AXoAa1xtGBm8zXOxIT9t5w== X-Google-Smtp-Source: ADFU+vtUBw1F2WEbE7lCwERSouzaO+4UT+AjLHqHPQKdLPE/CzmtSmpd/SCFwD552RPh5Xq3O1jC/w== X-Received: by 2002:ac8:2a0e:: with SMTP id k14mr19957972qtk.232.1584936644170; Sun, 22 Mar 2020 21:10:44 -0700 (PDT) Received: from pryzbyj (charmander.telsasoft.com. [50.244.222.1]) by smtp.gmail.com with ESMTPSA id 68sm10010360qkh.75.2020.03.22.21.10.42 (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Sun, 22 Mar 2020 21:10:43 -0700 (PDT) Received: by pryzbyj (Postfix, from userid 1000) id AF3A6800CF1; Sun, 22 Mar 2020 23:10:41 -0500 (CDT) Date: Sun, 22 Mar 2020 23:10:41 -0500 From: Justin Pryzby To: Ronnie S Cc: pgsql-performance@lists.postgresql.org Subject: Re: Partition Pruning (Hash Partitions) Support for DELETEs in PostgreSQL 11 and 12 Message-ID: <20200323041041.GH2563@telsasoft.com> References: MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.9.4 (2018-02-28) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk On Sun, Mar 22, 2020 at 11:45:53PM -0400, Ronnie S wrote: > Hello All, > > While doing some tests with hash partitioning behavior in PG11 and 12, I > have found that PG11 is not performing partition pruning with DELETEs > (explain analyze returned >2000 lines). I then ran the same test in PG12 > and recreated the objects using the same DDL, and it worked > Is this a bug, somewhat related to MergeAppend? > https://github.com/postgres/postgres/commit/5220bb7533f9891b1e071da6461d5c387e8f7b09 > If it is, anyone know if we have a workaround for DELETEs to use hash > partitions in PG11? I think due to this commit to pg12: https://commitfest.postgresql.org/22/1778/ https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=428b260f87e8861ba8e58807b69d433db491c4f4 ... https://www.postgresql.org/message-id/5c83dbca-12b5-1acf-0e85-58299e464a26%40lab.ntt.co.jp https://www.postgresql.org/message-id/4f049572-9440-3c99-afa1-f7ca7f38fe80%40lab.ntt.co.jp -- Justin