SELECT version();
version
------------------------------------------------------------------------------------------
PostgreSQL 9.6.0 on x86_64-pc-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit
(1 ligne)
Temps : 0,550 ms
\d+
Liste des relations
Schéma | Nom | Type | Propriétaire | Taille | Description
------------+-----------+-------+--------------+------------+-----------------------------------
admpatient | t1 | table | admpatient | 890 MB |
admpatient | t2 | table | admpatient | 172 MB |
admpatient | t3 | table | admpatient | 8192 bytes |
(3 lignes)
EXPLAIN SELECT count(*)
FROM t2 ,
t3
WHERE
coalesce(t2_c1, 'N') <> 'I'
AND
(
(t2_c2 >= TO_DATE('0101'
|| (EXTRACT (YEAR FROM CURRENT_DATE)-3), 'DDMMYYYY')
OR t2_c2 IS NULL)
and t2_c3 < TO_DATE('0101'
|| (EXTRACT (YEAR FROM CURRENT_DATE)), 'DDMMYYYY')
)
OR
( t2_c1 IN
(SELECT t1_c1 FROM t1 WHERE t1_c2 >= (EXTRACT (YEAR FROM CURRENT_DATE)-3))
);
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=75453479919.99..75453479919.99 rows=1 width=8)
-> Nested Loop (cost=0.00..75453477823.30 rows=838675 width=0)
-> Seq Scan on t3 (cost=0.00..1.01 rows=1 width=0)
-> Seq Scan on t2 (cost=0.00..75453469435.54 rows=838675 width=0)
Filter: ((((COALESCE(t2_c1, 'N'::character varying))::text <> 'I'::text) AND ((t2_c2 >= to_date(('0101'::text || ((date_part('year'::text, (('now'::cstring)::date)::timestamp without time zone) - '3'::double precision))::text), 'DDMMYYYY'::text)) OR (t2_c2 IS NULL)) AND (t2_c3 < to_date(('0101'::text || (date_part('year'::text, (('now'::cstring)::date)::timestamp without time zone))::text), 'DDMMYYYY'::text))) OR (SubPlan 1))
SubPlan 1
-> Materialize (cost=0.00..155285.83 rows=448398 width=9)
-> Seq Scan on t1 (cost=0.00..150853.84 rows=448398 width=9)
Filter: ((t1_c2)::double precision >= (date_part('year'::text, (('now'::cstring)::date)::timestamp without time zone) - '3'::double precision))
(9 lignes)
SHOW work_mem;
work_mem
----------
4MB
(1 ligne)
Temps : 0,363 ms
SELECT count(*)
FROM t2 ,
t3
WHERE
coalesce(t2_c1, 'N') <> 'I'
AND
(
(t2_c2 >= TO_DATE('0101'
|| (EXTRACT (YEAR FROM CURRENT_DATE)-3), 'DDMMYYYY')
OR t2_c2 IS NULL)
and t2_c3 < TO_DATE('0101'
|| (EXTRACT (YEAR FROM CURRENT_DATE)), 'DDMMYYYY')
)
OR
( t2_c1 IN
(SELECT t1_c1 FROM t1 WHERE t1_c2 >= (EXTRACT (YEAR FROM CURRENT_DATE)-3))
);
Cancel request sent
ERREUR: annulation de la requête à la demande de l'utilisateur
Temps : 81207,020 ms