I am analyzing query plans generated by the view in the database PostgreSQL
8.3, looking for missing information constraints not explicitly registrants
in the tables.
In nested queries, (ex. IN clause, ...), the query plan consist in the
evaluation of the subplane derived from clause (SELECT * ) and external
queries.
In the present case:
HashAggregate (cost=15.14..15.16 rows=1 width=247)
- Nested Loop IN Join (cost=3.46..15.12 rows=1 width=247)
Join Filter: (o.c_doctype_id = c_doctype.c_doctype_id)
- Hash Left Join (cost=3.46..12.38 rows=1 width=247)
Hash Cond: (bp.c_invoiceschedule_id = si.c_invoiceschedule_id)
Filter: ((o.invoicerule = 'I'::bpchar) OR ((o.invoicerule =
'O'::bpchar) AND (NOT (subplan))) OR ((o.invoicerule = 'D'::bpchar) AND
(l.qtyinvoiced l.qtydelivered)) OR ((o.invoicerule = 'S'::bpchar) AND
(bp.c_invoiceschedule_id IS NULL)) OR ((o.invoicerule = 'S'::bpchar) AND
(bp.c_invoiceschedule_id IS NOT NULL) AND ((si.invoicefrequency IS NULL) OR
(si.invoicefrequency = 'D'::bpchar) OR (si.invoicefrequency = 'W'::bpchar)
OR ((si.invoicefrequency = 'T'::bpchar) AND
(((adempiere.trunc((o.dateordered)::timestamp with time zone) =
(((adempiere.firstof(adempiere.getdate(), 'MM'::character
varying))::timestamp with time zone OPERATOR(adempiere.+)
si.invoicedaycutoff) - 1)) AND (adempiere.trunc(adempiere.getdate()) =
(((adempiere.firstof((o.dateordered)::timestamp with time zone,
'MM'::character varying))::timestamp with time zone OPERATOR(adempiere.+)
si.invoiceday) - 1))) OR ((adempiere.trunc((o.dateordered)::timestamp with
time zone) = (((adempiere.firstof(adempiere.getdate(), 'MM'::character
varying))::timestamp with time zone OPERATOR(adempiere.+)
si.invoicedaycutoff) + 14)) AND (adempiere.trunc(adempiere.getdate()) =
(((adempiere.firstof((o.dateordered)::timestamp with time zone,
'MM'::character varying))::timestamp with time zone OPERATOR(adempiere.+)
si.invoiceday) + 14) OR ((si.invoicefrequency = 'M'::bpchar) AND
(adempiere.trunc((o.dateordered)::timestamp with time zone) =
(((adempiere.firstof(adempiere.getdate(), 'MM'::character
varying))::timestamp with time zone OPERATOR(adempiere.+)
si.invoicedaycutoff) - 1)) AND (adempiere.trunc(adempiere.getdate()) =
(((adempiere.firstof((o.dateordered)::timestamp with time zone,
'MM'::character varying))::timestamp with time zone OPERATOR(adempiere.+)
si.invoiceday) - 1))
- Hash Join (cost=2.44..3.87 rows=3 width=300)
Hash Cond: (l.c_order_id = o.c_order_id)
- Seq Scan on c_orderline l (cost=0.00..1.31 rows=25
width=141)
Filter: (qtyordered qtyinvoiced)
- Hash (cost=2.40..2.40 rows=3 width=172)
- Hash Join (cost=1.13..2.40 rows=3 width=172)
Hash Cond: (bp.c_bpartner_id = o.c_bpartner_id)
- Seq Scan on c_bpartner bp (cost=0.00..1.17
rows=17 width=26)
- Hash (cost=1.10..1.10 rows=3 width=159)
- Seq Scan on c_order o
(cost=0.00..1.10 rows=3 width=159)
Filter: (docstatus = ANY
('{CO,CL,IP}'::bpchar[]))
- Hash (cost=1.01..1.01 rows=1 width=47)
- Seq Scan on c_invoiceschedule si (cost=0.00..1.01
rows=1 width=47)
SubPlan
- Seq Scan on c_orderline zz1 (cost=0.00..1.38 rows=1
width=0)
Filter: ((qtyordered qtydelivered) AND (c_order_id =
$0))
- Seq Scan on c_doctype (cost=0.00..2.73 rows=1 width=13)
Filter: ((c_doctype.docbasetype = 'SOO'::bpchar) AND
(c_doctype.docsubtypeso ALL ('{ON,OB,WR}'::bpchar[])))
In the analysis of the query plan and its subplane, I can not understand
what the parameter $0 represents, without looking the SQL query. My question
is: looking only at the query plan product, you can understand what is the
parameter $0?
SELECT o.ad_client_id, o.ad_org_id, o.c_bpartner_id, o.c_order_id,
o.documentno, o.dateordered, o.c_doctype_id, sum((l.qtyordered -
l.qtyinvoiced) * l.priceactual) AS totallines
FROM c_order o
JOIN c_orderline l ON o.c_order_id = l.c_order_id
JOIN c_bpartner bp ON o.c_bpartner_id = bp.c_bpartner_id
LEFT JOIN c_invoiceschedule si ON bp.c_invoiceschedule_id =
si.c_invoiceschedule_id
WHERE (o.docstatus = ANY (ARRAY['CO'::bpchar, 'CL'::bpchar, 'IP'::bpchar]))
AND
(o.c_doctype_id IN ( SELECT c_doctype.c_doctype_id
FROM c_doctype
WHERE c_doctype.docbasetype = 'SOO'::bpchar AND
(c_doctype.docsubtypeso ALL (ARRAY['ON'::bpchar, 'OB'::bpchar,
'WR'::bpchar] AND l.qtyordered l.qtyinvoiced
AND (o.invoicerule = 'I'::bpchar OR o.invoicerule =
'O'::bpchar
AND NOT (EXISTS ( SELECT 1 FROM c_orderline zz1
WHERE **zz1.c_order_id =
o.c_order_id** AND zz1.qtyordered zz1.qtydelivered)
