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 1pFs68-0004ve-EF for pgsql-general@arkaria.postgresql.org; Thu, 12 Jan 2023 07:34:04 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pFs65-0002PO-He for pgsql-general@arkaria.postgresql.org; Thu, 12 Jan 2023 07:34:01 +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 1pFs65-0002JH-3y for pgsql-general@lists.postgresql.org; Thu, 12 Jan 2023 07:34:01 +0000 Received: from mail-qv1-xf32.google.com ([2607:f8b0:4864:20::f32]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pFs62-0006DL-3P for pgsql-general@lists.postgresql.org; Thu, 12 Jan 2023 07:33:59 +0000 Received: by mail-qv1-xf32.google.com with SMTP id i12so12241066qvs.2 for ; Wed, 11 Jan 2023 23:33:57 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:from:to:cc:subject:date:message-id :reply-to; bh=eRpOJBAFiwXnwMAXe7ZDbjHJLNG+XhcG7rJ8UBfICcQ=; b=Hon1sKGy6kd/EyHNyksQ+0b78d2mlcqo6JICAPSTm3SZ5/TEQf5ppU9Cy1xoHGV+4+ O8s2kieG2/WCtxNSB6OfFiSsFc53N6Gy17rOLP/8tpkoXswceIbLEvcCeHKTW+d+2uXG LVOmBRfsUAb1aLmfxFCRrqpTl8I4gH269Azwq6naHdoqpxUSqf2DrLjvKPj3qN6OQw+c KkarxPCOoNT+WYdRmIkf5StBWc/r4yiV0U3x49sxG06thQXH1pVEIbP4DMFzSwFCuEpH laVc7xAj/kcyBNPRIH5puGojtpYJ4hYbyySAKv/3aT4M4WA/I1SVEIG8JZRtSLCM45/0 Z+Sg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:x-gm-message-state:from:to:cc:subject :date:message-id:reply-to; bh=eRpOJBAFiwXnwMAXe7ZDbjHJLNG+XhcG7rJ8UBfICcQ=; b=O7knmaIUIA9lvWf53D27WkqoYBMKeK9bLpQ229cjyACP36jx0aNUduZjXCSGpzkT5o 1+dGXPvWcEtH4rt6s9u3lfai2TpX3ebo7mD1HV2y93lrLFHv5wWtt+37BHq/J+x0fdrJ EY1hc6Ez4TjokvtrBNRh5iiM5XveKbBMwTaSyeVKyme9cfkrDvgnc00TPwRO1gfsaAbA pDmNRG2rmHClgT711k7vAnCt+9DY4jLVxF5I9geiY/6KBhAQvWNn21JnNS7TrBvhFjEy yZb+MZXbY3GDzuH+xSd8/boFx5Wi/lvymBbFtxN9gwCMAdG4oYyJsVyk++nv4Fipdwgf USOw== X-Gm-Message-State: AFqh2koYVyIMDRG15Qf2n28rkSKwcZPBybd56Wr2D4Oz9rXS9NXi8+h5 FmZh6abquneVuwo1OfgjdarPI8XPeLP+hQ== X-Google-Smtp-Source: AMrXdXtSCG878O5zRfF6fBGQr/G1O3wfb871I0ypN5O0fx78lSNolMvcyV1EIuHAR6TocHOEdpWMdg== X-Received: by 2002:a0c:8041:0:b0:531:b375:2cda with SMTP id 59-20020a0c8041000000b00531b3752cdamr64770754qva.9.1673508836958; Wed, 11 Jan 2023 23:33:56 -0800 (PST) Received: from [192.168.1.66] (108-250-206-84.lightspeed.nworla.sbcglobal.net. [108.250.206.84]) by smtp.googlemail.com with ESMTPSA id t11-20020a05620a034b00b006fa31bf2f3dsm10271092qkm.47.2023.01.11.23.33.56 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Wed, 11 Jan 2023 23:33:56 -0800 (PST) Content-Type: multipart/alternative; boundary="------------30GXtE1qHGWvyhyVNglHm998" Message-ID: Date: Thu, 12 Jan 2023 01:33:56 -0600 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.2.2 Subject: Re: EXPLAIN and FK references? Content-Language: en-US To: pgsql-general 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> From: Ron In-Reply-To: <905814.1673507515@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------30GXtE1qHGWvyhyVNglHm998 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit 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. -- Born in Arizona, moved to Babylonia. --------------30GXtE1qHGWvyhyVNglHm998 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit On 1/12/23 01:11, Tom Lane wrote:
Ron <ronljohnsonjr@gmail.com> 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.

--
Born in Arizona, moved to Babylonia.
--------------30GXtE1qHGWvyhyVNglHm998--