pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedIndex and statistics not used
2+ messages / 2 participants
[nested] [flat]
* Index and statistics not used
@ 2021-05-17 20:42 rstander@exa.co.za
0 siblings, 1 reply; 2+ messages in thread
From: rstander@exa.co.za @ 2021-05-17 20:42 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Index and statistics not used
@ 2021-05-17 22:31 David Rowley <dgrowleyml@gmail.com>
parent: rstander@exa.co.za
0 siblings, 0 replies; 2+ messages in thread
From: David Rowley @ 2021-05-17 22:31 UTC (permalink / raw)
To: rstander@exa.co.za; +Cc: Pgsql Performance <pgsql-performance@lists.postgresql.org>
On Tue, 18 May 2021 at 08:42, <rstander@exa.co.za> wrote:
> 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
Does EXPLAIN show it uses a seq scan for this 8-second SELECT?
If so, does it use the index if you SET enable_seqscan TO off; ? If
so, how do the costs compare to the seqscan costs?
Is random_page_cost set to something sane? Are all the indexes valid?
(psql's \d table2 would show you INVALID if they're not.)
does: SHOW enable_indexscan; show that index scanning is switched on?
David
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2021-05-17 22:31 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-05-17 20:42 Index and statistics not used rstander@exa.co.za
2021-05-17 22:31 ` David Rowley <dgrowleyml@gmail.com>
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