Thread: Index and statistics not used

Good day

I’m struggling with a Postgres 13 performance issue and nothing I do seem to help.

I have two tables with one having a foreign key to the other. It happens to be for this one client the foreign key is always null, so no violation would be possible. When deleting from the one table the foreign key trigger for constraint takes 20 seconds to run.

The tables look as follows

table1

    id    bigint pkey

    value number

table2 (55 mil entries)

    id        bigint pkey

    table1_id bigint (fkey to table1 id)

    value     number

Running delete from table1 where id = 48938 the trigger for constraint runs for 20 seconds

Event when doing a simple select from table2 where table1_id = 48938 takes about 8 seconds

I’ve tried the following, but nothing seems to change the outcome:

CREATE INDEX table2_idx ON table2(table1_id);

CREATE INDEX table2_idx2 ON table2(table1_id) WHERE table1_id IS NOT NULL;

CREATE INDEX table2_idx3 ON table2(table1_id) INCLUDE (id) WHERE table1_id IS NOT NULL;

alter table table2 alter column table1_id set statistics 10000;

None of these steps changes the planner and it would continue to do table scans.

Regards

Riaan