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 1h7QRs-0000Qz-Fx for pgsql-performance@arkaria.postgresql.org; Fri, 22 Mar 2019 20:07:28 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1h7QRr-0003jQ-6t for pgsql-performance@arkaria.postgresql.org; Fri, 22 Mar 2019 20:07:27 +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 1h7QRq-0003jJ-PS for pgsql-performance@lists.postgresql.org; Fri, 22 Mar 2019 20:07:27 +0000 Received: from mail-it1-x144.google.com ([2607:f8b0:4864:20::144]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1h7QRn-00077T-CA for pgsql-performance@lists.postgresql.org; Fri, 22 Mar 2019 20:07:26 +0000 Received: by mail-it1-x144.google.com with SMTP id w18so5439590itj.4 for ; Fri, 22 Mar 2019 13:07:22 -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=yRlDdAV1GC+bTp3jyIRta+1UnqakWPjRVZQFcJ3N5Jc=; b=UTIJFGAYil4STPr5l0uGagZ/kTpVztxWc4xoqNfUwzhrqPor7xB79wEABENjBp2a1E SlUvD4+NVN8H2umtRXxuvPZgccokTwu8Ud07F3RrMokskQ2EXELDHWwNpH08FbJ03KhL xGE5ktynldMNj+BPU2v/GaoM2XL73VIqH0ap7seZAbyRIGyPKcr1BgZzrFTTmMo2uVNm yZ+a43xV4k1s5DEsBTIgdNKN04r6wY50t2ut6khu3NBxij16kLLBjPTD4NSOJYanAc+t sKWciwU3mpY4h9axEXvjeifs6EfVfFdDVmstcE+k5HBbcB6eZtpkkI71nVy8mfWTBRzJ YYOg== 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=yRlDdAV1GC+bTp3jyIRta+1UnqakWPjRVZQFcJ3N5Jc=; b=jKGG2RztYw5UrDT+n7VKlBlqhGQjMOOQzCts7Iqm9binlBamIypP5X3HbKxFiF/9Lh 9VxaJ46rPutrhxcBY9rAEVZGxOlfeZ0bNwMtAVeh9x/MiWBp9JF0PlULQuEc00+Ze19f RqDZDOPBRqlHuDYbtUepzOlGD96NAMkTy660Gfe7jQKl85oRxo/l+MiX6TDAF8ESrmDv cBF7o/eBd+hRmwXu5tlXobE7MP7Ux/ReTEj0QQs5LI7ZQQh/2eHy0GEE7EIy3lsUd8zn /1jT+8/nLmZubaF+TKp9eym/F38qfcN1+R7QIle2IoPqFxWXqH1Dau1vFY2sO1/l2fC9 qj3w== X-Gm-Message-State: APjAAAUKJuLXlZyJ6KfD0TedhgoQELZDkFfj+yRs+MR0ipQ6A5nMl/jG omd3RZmG3SyNFFGNfqOJZSDHRg== X-Google-Smtp-Source: APXvYqzwSboq61GomhqpVNQ/SFlWZfo6qB3MPQxTp6Us0azyOAsdq7xaXe/b0iPVqJ3VKYs6Dtiq2A== X-Received: by 2002:a05:660c:908:: with SMTP id s8mr2921280itj.50.1553285241464; Fri, 22 Mar 2019 13:07:21 -0700 (PDT) Received: from pryzbyj (charmander.telsasoft.com. [50.244.222.1]) by smtp.gmail.com with ESMTPSA id l13sm1672651itc.12.2019.03.22.13.07.20 (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Fri, 22 Mar 2019 13:07:20 -0700 (PDT) Received: by pryzbyj (Postfix, from userid 1000) id CEC65800A28; Fri, 22 Mar 2019 15:07:19 -0500 (CDT) Date: Fri, 22 Mar 2019 15:07:19 -0500 From: Justin Pryzby To: Gunther Cc: pgsql-performance@lists.postgresql.org Subject: Re: EXPLAIN PLAN for DELETE CASCADE or DELETE not using pkey index despite EXPLAINing that it would? Message-ID: <20190322200719.GR2952@telsasoft.com> References: <189c6fff-0180-8179-d059-e222ec83bb1e@gusw.net> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <189c6fff-0180-8179-d059-e222ec83bb1e@gusw.net> User-Agent: Mutt/1.5.23 (2014-03-12) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk On Thu, Mar 21, 2019 at 03:31:42PM -0400, Gunther wrote: > Hi, > > I have 250 rows to delete, but they are a target to a bunch of child tables > with foreign key on delete cascade. > > EXPLAIN DELETE FROM Foo WHERE id = (SELECT fooId FROM Garbage); Probably because: https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-FK "Since a DELETE of a row from the referenced table [...] will require a scan of the referencing table for rows matching the old value, it is often a good idea to index the referencing columns too." Can you show "\d+ foo", specifically its FKs ? Justin