Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pFsBa-0005SU-QU for pgsql-general@arkaria.postgresql.org; Thu, 12 Jan 2023 07:39:42 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pFsBZ-0006Yd-LV for pgsql-general@arkaria.postgresql.org; Thu, 12 Jan 2023 07:39:41 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pFsBZ-0006WF-96 for pgsql-general@lists.postgresql.org; Thu, 12 Jan 2023 07:39:41 +0000 Received: from mail-pj1-x1034.google.com ([2607:f8b0:4864:20::1034]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pFsBW-0006Ge-Vg for pgsql-general@lists.postgresql.org; Thu, 12 Jan 2023 07:39:40 +0000 Received: by mail-pj1-x1034.google.com with SMTP id z9-20020a17090a468900b00226b6e7aeeaso19932291pjf.1 for ; Wed, 11 Jan 2023 23:39:38 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=in-reply-to:content-transfer-encoding:content-disposition :mime-version:references:message-id:subject:cc:to:from:date:from:to :cc:subject:date:message-id:reply-to; bh=kwZJTgUYV+FdGWIunRRWHKhyhjIn/IHgYq1bPb1d2oE=; b=K3mhpQMtAKSoaVLSOWCATbljIP9QMrthu67exsnhSr3n588Ou5UyPoW/6H/o7sCaTi H8vEv4p6Z1jfruoeQ2JmTW7YZ+FeZfyc1U7aTKhpC6Seo3WEzbAUmJozMTqTS26bbPzg uXU18muUTF+oKMsG0A9J9CKlXqRcshQb4xZR5b0QJTOJRq7q0ypztASNBjhaF1p3DSD6 9oU/dwMygopz0NNAyYNhbsZjTAoHeYUoD8yzTCn3pxZWIkrV6mewsGW8p50h+DxSgbhf Thp5NiWwH0NyH0l4XkGK4+5tw7ywQb8kDqiEZXxlf97ySA4l2SXIKDhtxUtIE5TgXM3W dCJw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=in-reply-to:content-transfer-encoding:content-disposition :mime-version:references:message-id:subject:cc:to:from:date :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=kwZJTgUYV+FdGWIunRRWHKhyhjIn/IHgYq1bPb1d2oE=; b=NOJjinAdaFbZJ71E82SML1UK57vIBm6L3VBYY6GXnVxYH1ZXWD1zmDpGLEJWszrdY7 BFRcL4NhRW0U7iICHOSXzDKQ/gqZV2lciBTrvR8RbbyLarw0lSC9pLNZQPu0AmnjRHh9 mO0Q/1plKaF/wSiG2YOBMF6QXotP4QLWn+YeIlySH+vrQAk4KPNgX3+LB6MF6d73NFmH DSMO0JxmEUX1sGLhY+b1froUaCjzObT6wqzELoWEqh5R0wUhhialt0ylSOFdClDbbAoL PP2xuufMSPCM2uS1HUjGjkjw7HAY4tl7p9Kp3+X0Ap2rGjG917U5DcYqoCn5rJjjbbgA b2Kw== X-Gm-Message-State: AFqh2koT7IAL31RRWHpW3MOkrlsy+XTAPDmbr5QOlcJTh88Y78K6erei bFR3d79GlehDndZ8qfYPRJE= X-Google-Smtp-Source: AMrXdXveNMK3d4zZsPE5G5iyPzGOt5peIes/91doNZye8Rh6TJp4Y4BchSEdpGIU6KYk+t5Fif9mgw== X-Received: by 2002:a17:902:a40e:b0:194:5c63:3647 with SMTP id p14-20020a170902a40e00b001945c633647mr1417874plq.67.1673509177733; Wed, 11 Jan 2023 23:39:37 -0800 (PST) Received: from jrouhaud (2001-b011-1006-19a3-286a-7bad-2643-e92b.dynamic-ip6.hinet.net. [2001:b011:1006:19a3:286a:7bad:2643:e92b]) by smtp.gmail.com with ESMTPSA id z6-20020a1709027e8600b0019445b8ef29sm3839916pla.61.2023.01.11.23.39.36 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Wed, 11 Jan 2023 23:39:37 -0800 (PST) Date: Thu, 12 Jan 2023 15:39:33 +0800 From: Julien Rouhaud To: Ron Cc: pgsql-general Subject: Re: EXPLAIN and FK references? Message-ID: <20230112073933.3esyluoe5ca7es74@jrouhaud> References: <269a6794-aa20-283d-bbc4-8cddaa2038de@gmail.com> <899474.1673503646@sss.pgh.pa.us> <431108a8-d814-848e-202b-e3a8e78ea39a@gmail.com> <905814.1673507515@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Disposition: inline Content-Transfer-Encoding: 8bit In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On Thu, Jan 12, 2023 at 01:33:56AM -0600, Ron wrote: > On 1/12/23 01:11, Tom Lane wrote: > > Ron writes: > > > On 1/12/23 00:07, Tom Lane wrote: > > > > No, not directly, but you could look at EXPLAIN ANALYZE to see which > > > > of the RI triggers is eating the time. > > > Good to know, but even deleting one day of data (90,000 rows using an index > > > scan on the date field) takes forever. > > So delete a relatively small number of rows, and do it with > > EXPLAIN *ANALYZE*. Without ANALYZE, you aren't getting any > > relevant data. > > Doing that when trying to delete *one minute* of data is exactly what was > needed.  99.999% of the time was spent on a checking another table which > didn't have a supporting index. > > Adding that index makes things run as expected. Just in case, I just remembered that pgcluu [1] has a feature to automatically detect missing indexes on FK. You can see an example report at [2]. [1] https://github.com/darold/pgcluu [2] https://pgcluu.darold.net/example/dolibarr-missing-index.html