Buenos días.
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)
Sort Key: m_closures.id DESC
-> 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.
--
*Ruben Fitó *
Software Engineer
[image: Ubiquat Technologies, SL]
r.f...@ubiquat.com <j.catari...@ubiquat.com>
www.ubiquat.com
Tota la informació continguda en aquest document i arxius adjunts és
CONFIDENCIAL protegida per llei de secret comercial. Si l'ha rebut per
error, si us plau elimini'l i posi's en contacte amb l'emissor.
All information contained in this document and any attachments are
CONFIDENTIAL and protected under trade secret laws. If you receive this
message by mistake, please delete it and notify it immediately to the
sender.
