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