Indexation des clés étrangères

Connecte a : Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production Version 18.4.0.0.0 create table geants( idg integer generated by default as identity, dtn timestamp, taille smallint, devise varchar(128), dicton varchar(128), berserk number); Table creee. insert into geants(dtn, taille, devise, dicton, berserk) with serie(i) as (select 1 from dual UNION ALL select i + 1 from serie where i < 100) select current_date - 3650 - trunc(dbms_random.value * 10000 + 1), 200 + (trunc(dbms_random.value * 200 + 1)), DBMS_RANDOM.string('x',32), DBMS_RANDOM.string('x',32), case when dbms_random.value < 0.001 then 1 else 0 end from serie; 100 lignes creees. create table lancers ( dtl timestamp, idg integer, perf integer ); Table creee. insert into lancers(dtl, idg, perf) with serie(i) as (select 1000000 from dual UNION ALL select i - 1 from serie where i > 1) select current_timestamp - i/1440, trunc(dbms_random.value * 100 + 1), trunc(dbms_random.value * 100000 + 1) from serie; 1000000 lignes creees. create unique index geants_pk on geants(idg); Index cree. alter table geants add primary key(idg); Table modifiee. alter table lancers add foreign key(idg) references geants(idg) on delete cascade; Table modifiee. create index lancers_br1 on lancers(dtl); Index cree. -- session 1 dml insert into lancers(dtl, idg, perf) values(systimestamp, 12, trunc(dbms_random.value * 100000 + 1)); 1 ligne creee. -- session 2 dml delete from geants where idg = 13; -- EN ATTENTE ! -- session 1 dml rollback; Annulation (rollback) effectuee. -- session 2 dml 1 ligne supprimee. rollback; Annulation (rollback) effectuee. -- session 1 query select max(perf) from lancers where dtl >= trunc(current_date); MAX(PERF) ---------- 99790 Plan d'execution ---------------------------------------------------------- Plan hash value: 2301366148 ---------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 26 | 8 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 26 | | | | 2 | TABLE ACCESS BY INDEX ROWID BATCHED| LANCERS | 1001 | 26026 | 8 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | LANCERS_BR1 | 1001 | | 5 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------------- -- session 2 query select avg(perf) from lancers where idg = 50; AVG(PERF) ---------- 49908,5266 Plan d'execution ---------------------------------------------------------- Plan hash value: 2587003846 ------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 26 | 792 (2)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 26 | | | |* 2 | TABLE ACCESS FULL| LANCERS | 11010 | 279K| 792 (2)| 00:00:01 | ------------------------------------------------------------------------------ -- session ddl create index lancers_fk1 on lancers(idg); Index cree. -- session 1 query select max(perf) from lancers where dtl >= trunc(current_date); MAX(PERF) ---------- 99790 Plan d'execution ---------------------------------------------------------- Plan hash value: 2301366148 ---------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 26 | 8 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 26 | | | | 2 | TABLE ACCESS BY INDEX ROWID BATCHED| LANCERS | 1001 | 26026 | 8 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | LANCERS_BR1 | 1001 | | 5 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------------- -- session 2 query select avg(perf) from lancers where idg = 50; AVG(PERF) ---------- 49908,5266 Plan d'execution ---------------------------------------------------------- Plan hash value: 2587003846 ------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 26 | 792 (2)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 26 | | | |* 2 | TABLE ACCESS FULL| LANCERS | 11010 | 279K| 792 (2)| 00:00:01 | ------------------------------------------------------------------------------ alter session set optimizer_index_cost_adj=10; Session modifiee. select avg(perf) from lancers where idg = 50; AVG(PERF) ---------- 49908,5266 Plan d'execution ---------------------------------------------------------- Plan hash value: 3610659263 ---------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 8 | 281 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 8 | | | | 2 | TABLE ACCESS BY INDEX ROWID BATCHED| LANCERS | 9981 | 79848 | 281 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | LANCERS_FK1 | 9981 | | 2 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------------- -- session 1 DML insert into lancers(dtl, idg, perf) values(systimestamp, 12, trunc(dbms_random.value * 100000 + 1)); 1 ligne creee. -- session 2 DML delete from geants where idg = 13; 1 ligne supprimee. -- session 1 DML rollback; Annulation (rollback) effectuee. -- session 2 DML rollback; Annulation (rollback) effectuee.

select version(); version --------------------------------------------------------------------------------------------------------------- PostgreSQL 11.1 (Debian 11.1-1.pgdg+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.2.0-9) 8.2.0, 64-bit create table geants( idg integer generated by default as identity, dtn timestamp, taille smallint, devise varchar(128), dicton varchar(128), berserk boolean); CREATE TABLE with recursive serie(i) as (select 1 UNION ALL select i + 1 from serie where i < 100) insert into geants(dtn, taille, devise, dicton, berserk) select current_date - 3650 - trunc(random() * 10000 + 1)::int, 200 + (trunc(random() * 200 + 1)), upper(md5(random()::text)),upper(md5(random()::text)), case when random() < 0.001 then true else false end from serie; INSERT 0 100 create table lancers ( dtl timestamp, idg integer, perf integer ); CREATE TABLE insert into lancers(dtl, idg, perf) with recursive serie(i) as (select 1000000 UNION ALL select i - 1 from serie where i > 1) select clock_timestamp() - ((i)::int || ' minutes')::interval, trunc(random() * 100 + 1), trunc(random() * 100000 + 1) from serie; INSERT 0 1000000 create unique index geants_pk on geants(idg); CREATE INDEX alter table geants add primary key using index geants_pk; ALTER TABLE alter table lancers add foreign key(idg) references geants(idg) on delete cascade; ALTER TABLE create index lancers_br1 on lancers using brin(dtl); CREATE INDEX -- session dml 1 start transaction; START TRANSACTION explain analyze insert into lancers(dtl, idg, perf) values(clock_timestamp(), 12, trunc(random() * 100000 + 1)); QUERY PLAN ------------------------------------------------------------------------------------------------ Insert on lancers (cost=0.00..0.03 rows=1 width=16) (actual time=0.153..0.158 rows=0 loops=1) -> Result (cost=0.00..0.03 rows=1 width=16) (actual time=0.021..0.031 rows=1 loops=1) Planning Time: 0.089 ms Trigger for constraint lancers_idg_fkey: time=0.125 calls=1 Execution Time: 0.396 ms -- session dml 2 start transaction; START TRANSACTION explain analyze delete from geants where idg = 13; QUERY PLAN ------------------------------------------------------------------------------------------------------ Delete on geants (cost=0.00..3.25 rows=1 width=6) (actual time=0.150..0.154 rows=0 loops=1) -> Seq Scan on geants (cost=0.00..3.25 rows=1 width=6) (actual time=0.056..0.088 rows=1 loops=1) Filter: (idg = 13) Rows Removed by Filter: 99 Planning Time: 0.219 ms Trigger for constraint lancers_idg_fkey: time=283.612 calls=1 Execution Time: 283.872 ms -- session dml 1 rollback; ROLLBACK -- session dml 2 rollback; ROLLBACK -- session query 1 explain select max(perf) from lancers where dtl >= current_date; QUERY PLAN ------------------------------------------------------------------------------------- Aggregate (cost=5769.45..5769.46 rows=1 width=4) -> Bitmap Heap Scan on lancers (cost=12.27..5767.11 rows=938 width=4) Recheck Cond: (dtl >= CURRENT_DATE) -> Bitmap Index Scan on lancers_br1 (cost=0.00..12.03 rows=23256 width=0) Index Cond: (dtl >= CURRENT_DATE) select max(perf) from lancers where dtl >= current_date; max ------- 99974 (1 ligne) Temps : 4,536 ms Temps : 4,702 ms Temps : 4,768 ms -- session query 2 explain select avg(perf) from lancers where idg = 50; QUERY PLAN ----------------------------------------------------------------------------------------- Finalize Aggregate (cost=11623.75..11623.76 rows=1 width=32) -> Gather (cost=11623.53..11623.74 rows=2 width=32) Workers Planned: 2 -> Partial Aggregate (cost=10623.53..10623.54 rows=1 width=32) -> Parallel Seq Scan on lancers (cost=0.00..10614.33 rows=3680 width=4) Filter: (idg = 50) (6 lignes) select avg(perf) from lancers where idg = 50; avg -------------------- 50042.634264011497 (1 ligne) Temps : 132,083 ms Temps : 130,933 ms Temps : 130,485 ms -- session ddl create index lancers_fk1 on lancers(idg); CREATE INDEX \di+ Liste des relations Schéma | Nom | Type | Propriétaire | Table | Taille | Description ----------+-------------+-------+--------------+---------+--------+------------- postgres | geants_pk | index | postgres | geants | 16 kB | postgres | lancers_br1 | index | postgres | lancers | 48 kB | postgres | lancers_fk1 | index | postgres | lancers | 21 MB | -- session query 1 explain select max(perf) from lancers where dtl >= current_date; QUERY PLAN ------------------------------------------------------------------------------------- Aggregate (cost=5769.45..5769.46 rows=1 width=4) -> Bitmap Heap Scan on lancers (cost=12.27..5767.11 rows=938 width=4) Recheck Cond: (dtl >= CURRENT_DATE) -> Bitmap Index Scan on lancers_br1 (cost=0.00..12.03 rows=23256 width=0) Index Cond: (dtl >= CURRENT_DATE) select max(perf) from lancers where dtl >= current_date; max ------- 99974 (1 ligne) Temps : 4,808 ms Temps : 4,607 ms Temps : 4,516 ms -- session query 2 explain select avg(perf) from lancers where idg = 50; QUERY PLAN ------------------------------------------------------------------------------------- Aggregate (cost=5916.72..5916.73 rows=1 width=32) -> Bitmap Heap Scan on lancers (cost=168.88..5894.63 rows=8833 width=4) Recheck Cond: (idg = 50) -> Bitmap Index Scan on lancers_fk1 (cost=0.00..166.67 rows=8833 width=0) Index Cond: (idg = 50) (5 lignes) select avg(perf) from lancers where idg = 50; avg -------------------- 50042.634264011497 (1 ligne) Temps : 20,300 ms Temps : 19,037 ms Temps : 18,700 ms -- session dml 1 start transaction; START TRANSACTION explain analyze insert into lancers(dtl, idg, perf) values(clock_timestamp(), 12, trunc(random() * 100000 + 1)); QUERY PLAN ------------------------------------------------------------------------------------------------ Insert on lancers (cost=0.00..0.03 rows=1 width=16) (actual time=0.441..0.446 rows=0 loops=1) -> Result (cost=0.00..0.03 rows=1 width=16) (actual time=0.025..0.036 rows=1 loops=1) Planning Time: 0.115 ms Trigger for constraint lancers_idg_fkey: time=0.181 calls=1 Execution Time: 0.891 ms (5 lignes) -- session dml 2 start transaction; START TRANSACTION explain analyze delete from geants where idg = 13; QUERY PLAN ------------------------------------------------------------------------------------------------------ Delete on geants (cost=0.00..3.25 rows=1 width=6) (actual time=0.120..0.124 rows=0 loops=1) -> Seq Scan on geants (cost=0.00..3.25 rows=1 width=6) (actual time=0.044..0.073 rows=1 loops=1) Filter: (idg = 13) Rows Removed by Filter: 99 Planning Time: 0.167 ms Trigger for constraint lancers_idg_fkey: time=61.931 calls=1 Execution Time: 62.152 ms