index on foreign key

gistfile1.txt

postgres=# \d t1
                 Table "public.t1"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 col1   | integer |           | not null |
 col2   | text    |           |          |
Indexes:
    "t1_pkey" PRIMARY KEY, btree (col1)
Referenced by:
    TABLE "t2" CONSTRAINT "t2_col2_fkey" FOREIGN KEY (col2) REFERENCES t1(col1) ON UPDATE CASCADE ON DELETE CASCADE

postgres=# \d t2
                 Table "public.t2"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 col3   | integer |           | not null |
 col2   | integer |           |          |
Indexes:
    "t2_pkey" PRIMARY KEY, btree (col3)
Foreign-key constraints:
    "t2_col2_fkey" FOREIGN KEY (col2) REFERENCES t1(col1) ON UPDATE CASCADE ON DELETE CASCADE

postgres=# begin;
BEGIN
postgres=*# EXPLAIN (ANALYZE, VERBOSE, COSTS, BUFFERS) delete from t1 where col1 < 100;
                                                         QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
 Delete on public.t1  (cost=0.42..10.14 rows=0 width=0) (actual time=0.097..0.097 rows=0 loops=1)
   Buffers: shared hit=202
   ->  Index Scan using t1_pkey on public.t1  (cost=0.42..10.14 rows=98 width=6) (actual time=0.006..0.018 rows=99 loops=1)
         Output: ctid
         Index Cond: (t1.col1 < 100)
         Buffers: shared hit=4
 Planning:
   Buffers: shared hit=1 read=4 dirtied=1
 Planning Time: 1.752 ms
 Trigger RI_ConstraintTrigger_a_17284 for constraint t2_col2_fkey: time=41258.152 calls=99
 Execution Time: 41258.319 ms
(11 rows)

postgres=*# rollback;
ROLLBACK
postgres=# begin;
BEGIN
postgres=*# rollback;
ROLLBACK
postgres=# create index on t2(col2);
CREATE INDEX
postgres=# analyze t2;
ANALYZE
postgres=# begin;
BEGIN
postgres=*# EXPLAIN (ANALYZE, VERBOSE, COSTS, BUFFERS) delete from t1 where col1 < 100;
                                                         QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
 Delete on public.t1  (cost=0.42..10.14 rows=0 width=0) (actual time=1.009..1.009 rows=0 loops=1)
   Buffers: shared hit=202
   ->  Index Scan using t1_pkey on public.t1  (cost=0.42..10.14 rows=98 width=6) (actual time=0.007..0.022 rows=99 loops=1)
         Output: ctid
         Index Cond: (t1.col1 < 100)
         Buffers: shared hit=4
 Planning:
   Buffers: shared hit=1 read=4 dirtied=1
 Planning Time: 4.741 ms
 Trigger RI_ConstraintTrigger_a_17284 for constraint t2_col2_fkey: time=1634.892 calls=99
 Execution Time: 1636.560 ms
(11 rows)

postgres=*# rollback;
ROLLBACK



we can make use of auto explain and other gucs to dig deep into the plan used by optimiser on foreign relations, which otherwise is not visible via explain

postgres=# \d t
                 Table "public.t"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 col1   | integer |           | not null |
Indexes:
    "t_pkey" PRIMARY KEY, btree (col1)
Referenced by:
    TABLE "t2" CONSTRAINT "tfk" FOREIGN KEY (col1) REFERENCES t(col1) ON UPDATE CASCADE ON DELETE CASCADE

postgres=# \d t2
                 Table "public.t2"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 col1   | integer |           |          |
Indexes:
    "t2_col1_idx" btree (col1)
    "t2_col1_idx1" btree (col1)
Foreign-key constraints:
    "tfk" FOREIGN KEY (col1) REFERENCES t(col1) ON UPDATE CASCADE ON DELETE CASCADE


auto exp setting
postgres@pg:~/udemy/16$ more db1/postgresql.auto.conf

log_statement = 'ddl'
log_lock_waits = 'on'
auto_explain.log_nested_statements = on
auto_explain.log_min_duration = 0
session_preload_libraries = 'auto_explain'
auto_explain.log_analyze = true
auto_explain.log_timing = on
auto_explain.log_triggers = on
auto_explain.log_verbose = on


if i run a simple explain, it does not cascade into trigger explain

postgres=# explain analyze verbose delete from t where col1 = 50;
                                                      QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
 Delete on public.t  (cost=0.29..8.31 rows=0 width=0) (actual time=0.044..0.045 rows=0 loops=1)
   ->  Index Scan using t_pkey on public.t  (cost=0.29..8.31 rows=1 width=6) (actual time=0.032..0.033 rows=1 loops=1)
         Output: ctid
         Index Cond: (t.col1 = 50)
 Planning Time: 0.058 ms
 Trigger RI_ConstraintTrigger_a_19781 for constraint tfk: time=6.652 calls=1
 Execution Time: 6.731 ms
(7 rows)


but with auto_explain enabled and nested statements/ log trigger enabled, i can see the plan there
2024-03-26 02:05:31.913 IST [6359] LOG:  duration: 6.557 ms  plan:
        Query Text: DELETE FROM ONLY "public"."t2" WHERE $1 OPERATOR(pg_catalog.=) "col1"
        Query Parameters: $1 = '50'
        Delete on public.t2  (cost=0.00..2135.00 rows=0 width=0) (actual time=6.556..6.556 rows=0 loops=1)
          ->  Seq Scan on public.t2  (cost=0.00..2135.00 rows=1 width=6) (actual time=0.189..6.554 rows=1 loops=1)
                Output: ctid
                Filter: ($1 = t2.col1)
                Rows Removed by Filter: 99988
2024-03-26 02:05:31.913 IST [6359] CONTEXT:  SQL statement "DELETE FROM ONLY "public"."t2" WHERE $1 OPERATOR(pg_catalog.=) "col1""
2024-03-26 02:05:31.913 IST [6359] LOG:  duration: 6.698 ms  plan:
        Query Text: explain analyze verbose delete from t where col1 = 50;
        Delete on public.t  (cost=0.29..8.31 rows=0 width=0) (actual time=0.044..0.045 rows=0 loops=1)
          ->  Index Scan using t_pkey on public.t  (cost=0.29..8.31 rows=1 width=6) (actual time=0.032..0.033 rows=1 loops=1)
                Output: ctid
                Index Cond: (t.col1 = 50)
        Trigger RI_ConstraintTrigger_a_19781 for constraint tfk: time=6.652 calls=1