From: Tom Lane <tgl@sss.pgh.pa.us>
To: Wells Oliver <wells.oliver@gmail.com>
Cc: pgsql-admin <pgsql-admin@postgresql.org>
Subject: Re: DELETE not seeming to use the PK index..
Date: Mon, 20 Jul 2020 20:27:14 -0400
Message-ID: <769076.1595291234@sss.pgh.pa.us> (raw)
In-Reply-To: <CAOC+FBXK8MRf0V4eXED_L9=U0XwgZ8T4bzFuiGFnxovXokYe0w@mail.gmail.com>
References: <CAOC+FBXK8MRf0V4eXED_L9=U0XwgZ8T4bzFuiGFnxovXokYe0w@mail.gmail.com>
Wells Oliver <wells.oliver@gmail.com> writes:
> As you might guess, it takes forever. Why is it not using the index?
Because it thinks it's going to have to delete half of the table
(36M / 73M rows). That's pretty obviously coming from a fallback default
selectivity estimate, which makes one wonder if your statistics for the
tables are up to date, or indeed exist at all.
> I tried a VACUUM FULL ANALYZE before, no change to the planner.
Hmph. That should have updated things, but the numbers for temptable
at least are obviously not coming from any actual statistics. (The
"200" for number of distinct values is another telltale default.)
If temptable actually is a temp table, was it included in the VACUUM
or did you create it after?
Are there any non-built-in datatypes or operators involved here?
Perhaps you've enabled RLS and it's preventing access to the stats?
> Should I create a secondary index just on pkcol1? I thought it'd use the PK
> index since it's the first column.
This is not a lack-of-index problem, or at least it won't be until
you get rowcount estimates that would encourage the planner to think
that an index would be helpful. A rule of thumb is that if the
query needs to fetch more than a percent or two of the table, an
index is likely not worth the trouble.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: tgl@sss.pgh.pa.us, wells.oliver@gmail.com
Subject: Re: DELETE not seeming to use the PK index..
In-Reply-To: <769076.1595291234@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox