Re: EXPLAIN and FK references?

On Thu, Jan 12, 2023 at 01:33:56AM -0600, Ron wrote:
> On 1/12/23 01:11, Tom Lane wrote:
> > Ron<ronljohnsonjr(at)gmail(dot)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.

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