Tengo la siguiente query en postgresql que tiene un coste de casi un minuto:
EXPLAIN
SELECT
mtx.oper_tipus,
mtx.oper_estat
FROM
m_transaccions mtx
INNER JOIN comm_pax_transactions pax ON (mtx.num_operacio = pax.num_oper)
WHERE
mtx.estacio = 333
AND mtx.oper_tipus IN ('F', 'f', 'P', 'p')
AND mtx.oper_estat IN (0, 1)
AND mtx.num_operacio > COALESCE(
(
SELECT
num_oper_end
FROM
m_closures
WHERE
station = 333
ORDER BY
id DESC
LIMIT
1
), 0
);
Su PLAN es:
Hash Join (cost=323694.66..383455.57 rows=490 width=5)
Hash Cond: (pax.num_oper = mtx.num_operacio)
InitPlan 1 (returns $0)
-> Limit (cost=478.25..478.25 rows=1 width=8)
-> Sort (cost=478.25..482.60 rows=1741 width=8)
-> Bitmap Heap Scan on m_closures (cost=37.78..469.54 rows=1741 width=8)
Recheck Cond: (station = '333'::numeric)
-> Bitmap Index Scan on m_closures_idx_001 (cost=0.00..37.34 rows=1741 width=0)
Index Cond: (station = '333'::numeric)
-> Seq Scan on comm_pax_transactions pax (cost=0.00..58409.55 rows=359055 width=4)
-> Hash (cost=322894.80..322894.80 rows=25729 width=9)
-> Bitmap Heap Scan on m_transaccions mtx (cost=8022.36..322894.80 rows=25729 width=9)
Recheck Cond: ((estacio = 333) AND (oper_tipus = ANY ('{F,f,P,p}'::bpchar[])))
Filter: ((oper_estat = ANY ('{0,1}'::numeric[])) AND (num_operacio > $0))
-> Bitmap Index Scan on m_transaccions_idx6 (cost=0.00..8015.93 rows=103767 width=0)
Index Cond: ((estacio = 333) AND (oper_tipus = ANY ('{F,f,P,p}'::bpchar[])))
En cambio, si hago la query de esta forma, (indicando el valor en vez la subconsulta), tiene un coste de 2 segundos:
EXPLAIN
SELECT
mtx.oper_tipus,
mtx.oper_estat
FROM
m_transaccions mtx
INNER JOIN comm_pax_transactions pax ON (mtx.num_operacio = pax.num_oper)
WHERE
mtx.estacio = 333
AND mtx.oper_tipus IN ('F', 'f', 'P', 'p')
AND mtx.oper_estat IN (0, 1)
AND mtx.num_operacio > 167673905
Obtengo :
Nested Loop (cost=0.98..7623.84 rows=1 width=5)
-> Index Scan using m_transaccions_pkey on m_transaccions mtx (cost=0.56..7539.34 rows=10 width=9)
Index Cond: (num_operacio > 167673905)
Filter: ((oper_estat = ANY ('{0,1}'::numeric[])) AND (estacio = 333) AND (oper_tipus = ANY ('{F,f,P,p}'::bpchar[])))
-> Index Only Scan using comm_pax_transactions_idx1 on comm_pax_transactions pax (cost=0.42..8.44 rows=1 width=4)
Index Cond: (num_oper = mtx.num_operacio)
El valor de 167673905 lo obtengo de la subconsulta marcada en negrita y tiene un tiempo de 6 ms:
No entiendo muy bien cómo maneja postgresql las subconsultas. (Tampoco sé)
Alguna idea para mejorar la query?
Gracias de antemano.
