select version();
version
---------------------------------------------------------------------------------------------------
PostgreSQL 9.6.2 on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-10) 6.3.0 20170321, 64-bit
(1 ligne)
create table tickets(
ticket_id serial primary key,
dto timestamp,
dtp timestamp,
dtf timestamp,
etat char(1),
severite smallint,
inter_id smallint,
titre character varying(128),
description character varying);
CREATE TABLE
WITH serie(i, r) AS (SELECT generate_series(1000000,1,-1)::real, random())
insert into tickets(dto, dtp, dtf, etat, severite, inter_id, titre, description)
select
(current_timestamp - (ceil(i/3) || ' minutes')::interval - (trunc(random() * 100 + 1) || ' minutes')::interval),
(case when r >= 0.001 and r <= 0.002 then NULL else current_timestamp - (ceil(i/3) || ' minutes')::interval end),
(case when r <= 0.002 then NULL else current_timestamp - (ceil(i/3) || ' minutes')::interval + (trunc(random() * 360 + 1) || ' minutes')::interval end),
(case when r <= 0.001 then NULL when r <= 0.002 then 'A' else 'F' end),
(case when r < 0.1 then 1 when r < 0.8 then 2 else 3 end),
(trunc(random() * 1000 + 1)),
(md5(random()::text)),
(md5(random()::text))
from serie;
INSERT 0 1000000
\d+
Liste des relations
Schéma | Nom | Type | Propriétaire | Taille | Description
--------+-----------------------+----------+--------------+------------+-------------
public | tickets | table | postgres | 128 MB |
public | tickets_ticket_id_seq | séquence | postgres | 8192 bytes |
(2 lignes)
select avg(current_timestamp - dto) from tickets where etat is null;
avg
--------------------------
114 days 29:13:52.026701
(1 ligne)
Temps : 178,969 ms
select count(*) from tickets where etat = 'A';
count
-------
950
(1 ligne)
Temps : 145,508 ms
with ts_les_tickets(nb) as (select count(*) from tickets),
tickets_ouverts(nb) as (select count(*) from tickets where etat = 'A' or etat is null)
select ts_les_tickets.nb - tickets_ouverts.nb from ts_les_tickets, tickets_ouverts;
?column?
----------
998040
(1 ligne)
Temps : 214,215 ms
select count(*) from tickets where etat = 'F';
count
--------
998040
(1 ligne)
Temps : 162,483 ms
create index tickets_etat_i1 on tickets(etat);
CREATE INDEX
Temps : 954,655 ms
postgres=# \di+
Liste des relations
Schéma | Nom | Type | Propriétaire | Table | Taille | Description
--------+-----------------+-------+--------------+---------+--------+-------------
public | tickets_etat_i1 | index | postgres | tickets | 21 MB |
public | tickets_pkey | index | postgres | tickets | 21 MB |
(2 lignes)
select avg(current_timestamp - dto) from tickets where etat is null;
avg
--------------------------
114 days 29:13:52.026701
(1 ligne)
Temps : 1,906 ms
select count(*) from tickets where etat = 'A';
count
-------
950
(1 ligne)
Temps : 3,027 ms
with ts_les_tickets(nb) as (select count(*) from tickets),
tickets_ouverts(nb) as (select count(*) from tickets where etat = 'A' or etat is null)
select ts_les_tickets.nb - tickets_ouverts.nb from ts_les_tickets, tickets_ouverts;
?column?
----------
998040
(1 ligne)
Temps : 94,985 ms
select count(*) from tickets where etat = 'F';
count
--------
998040
(1 ligne)
Temps : 188,218 ms
drop index tickets_etat_i1;
DROP INDEX
Temps : 292,469 ms
create index tickets_etat_i2 on tickets(etat) where etat = 'A ' or etat is null;
CREATE INDEX
Temps : 178,385 ms
\di+
Liste des relations
Schéma | Nom | Type | Propriétaire | Table | Taille | Description
--------+-----------------+-------+--------------+---------+--------+-------------
public | tickets_etat_i2 | index | postgres | tickets | 64 kB |
public | tickets_pkey | index | postgres | tickets | 21 MB |
(2 lignes)
select avg(current_timestamp - dto) from tickets where etat is null;
avg
--------------------------
114 days 29:13:52.026701
(1 ligne)
Temps : 1,532 ms
select count(*) from tickets where etat = 'A';
count
-------
950
(1 ligne)
Temps : 1,103 ms
with ts_les_tickets(nb) as (select count(*) from tickets),
tickets_ouverts(nb) as (select count(*) from tickets where etat = 'A' or etat is null)
select ts_les_tickets.nb - tickets_ouverts.nb from ts_les_tickets, tickets_ouverts;
?column?
----------
998040
(1 ligne)
Temps : 89,632 ms
select count(*) from tickets where etat = 'F';
count
--------
998040
(1 ligne)
Temps : 159,928 ms
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
create table tickets nologging as
with serie(tid, i, r) as (
select
rownum tid, 1000001 - rownum i, dbms_random.value() r
from dual
connect by
level <= 1000000
)
select
tid ticket_id,
(current_timestamp - (ceil(i/3))/1440 - (trunc(dbms_random.value() * 100 + 1))/1440) dto,
(case when r >= 0.001 and r <= 0.002 then NULL else current_timestamp - (ceil(i/3))/1440 end) dtp,
(case when r <= 0.002 then NULL else current_timestamp - (ceil(i/3))/1440 + (trunc(dbms_random.value() * 360 + 1))/1440 end) dtf,
(case when r <= 0.001 then NULL when r <= 0.002 then 'A' else 'F' end) etat,
(case when r < 0.1 then 1 when r < 0.8 then 2 else 3 end) severite,
(trunc(dbms_random.value() * 1000 + 1)) inter_id,
(dbms_random.string('L', 32)) titre,
(dbms_random.string('L', 32)) description
from serie;
Table created.
alter table tickets add primary key(ticket_id);
Table altered.
desc tickets;
Name Null? Type
----------------------------------------- -------- ----------------------------
TICKET_ID NOT NULL NUMBER
DTO DATE
DTP DATE
DTF DATE
ETAT CHAR(1)
SEVERITE NUMBER
INTER_ID NUMBER
TITRE VARCHAR2(4000)
DESCRIPTION VARCHAR2(4000)
select bytes/1024/1024 from user_segments where segment_name = 'TICKETS';
BYTES/1024/1024
---------------
120
CREATE OR REPLACE FUNCTION ouverts(etat CHAR)
RETURN CHAR
DETERMINISTIC
AS BEGIN
IF etat = 'F' THEN
RETURN null;
ELSIF etat is null THEN
RETURN 'O';
ELSE
RETURN 'A';
END IF;
END;
/
Function created.
create index tickets_etat_i2 on tickets(ouverts(etat));
Index created.
select bytes/1024/1024 from user_segments where segment_name = 'TICKETS_ETAT_I2';
BYTES/1024/1024
---------------
,0625
CREATE OR REPLACE FUNCTION interval_to_sec ( i IN INTERVAL DAY TO SECOND )
RETURN NUMBER
IS
numSecs NUMBER;
BEGIN
numSecs := ((extract(day from i) * 24
+ extract(hour from i) )*60
+ extract(minute from i) )*60
+ extract(second from i);
RETURN numSecs;
END;
/
Function created.
explain plan for select avg(interval_to_sec(current_timestamp - dto)) from tickets where ouverts(etat) = 'O';
Explained.
select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
Plan hash value: 4141308402
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 994 (0)| 00:00:12 |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| TICKETS | 1022 | 8176 | 994 (0)| 00:00:12 |
|* 3 | INDEX RANGE SCAN | TICKETS_ETAT_I2 | 1022 | | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("ESSAI"."OUVERTS"("ETAT")='O')
15 rows selected.
explain plan for select count(*) from tickets where ouverts(etat) = 'A';
Explained.
select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
Plan hash value: 1213017162
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 1 | | |
|* 2 | INDEX RANGE SCAN| TICKETS_ETAT_I2 | 1022 | 1022 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
---------------------------------------------------
2 - access("ESSAI"."OUVERTS"("ETAT")='A')
14 rows selected.
explain plan for
with ts_les_tickets(nb) as (select count(*) from tickets),
tickets_ouverts(nb) as (select count(*) from tickets where ouverts(etat) in ('A','O'))
select ts_les_tickets.nb - tickets_ouverts.nb from ts_les_tickets, tickets_ouverts;
Explained.
select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
Plan hash value: 3910676987
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 574 (1)| 00:00:07 |
| 1 | NESTED LOOPS | | 1 | 26 | 574 (1)| 00:00:07 |
| 2 | VIEW | | 1 | 13 | 571 (1)| 00:00:07 |
| 3 | SORT AGGREGATE | | 1 | | | |
| 4 | INDEX FAST FULL SCAN| SYS_C007277 | 1000K| | 571 (1)| 00:00:07 |
| 5 | VIEW | | 1 | 13 | 3 (0)| 00:00:01 |
| 6 | SORT AGGREGATE | | 1 | 1 | | |
| 7 | INLIST ITERATOR | | | | | |
|* 8 | INDEX RANGE SCAN | TICKETS_ETAT_I2 | 2043 | 2043 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
8 - access("ESSAI"."OUVERTS"("ETAT")='A' OR "ESSAI"."OUVERTS"("ETAT")='O')
20 rows selected.
explain plan for select count(*) from tickets where etat = 'F';
Explained.
select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
Plan hash value: 2870398729
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 | 4165 (1)| 00:00:50 |
| 1 | SORT AGGREGATE | | 1 | 1 | | |
|* 2 | TABLE ACCESS FULL| TICKETS | 499K| 487K| 4165 (1)| 00:00:50 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
---------------------------------------------------
2 - filter("ETAT"='F')
14 rows selected.
explain plan for select count(*) from tickets where ouverts(etat) is null;
Explained.
select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
Plan hash value: 2870398729
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 | 4242 (3)| 00:00:51 |
| 1 | SORT AGGREGATE | | 1 | 1 | | |
|* 2 | TABLE ACCESS FULL| TICKETS | 997K| 974K| 4242 (3)| 00:00:51 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
---------------------------------------------------
2 - filter("ESSAI"."OUVERTS"("ETAT") IS NULL)
14 rows selected.