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)
)
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 (trunc(o.dateordered::timestamp with time zone)
<= ((firstof(getdate(), 'MM'::character varying)::timestamp with time zone
OPERATOR(+) si.invoicedaycutoff) - 1)
AND trunc(getdate()) >=
((firstof(o.dateordered::timestamp with time zone, 'MM'::character
varying)::timestamp with time zone OPERATOR(+) si.invoiceday) - 1)
OR trunc(o.dateordered::timestamp with time zone)
<= ((firstof(getdate(), 'MM'::character varying)::timestamp with time zone
OPERATOR(+) si.invoicedaycutoff) + 14)
AND trunc(getdate()) >=
((firstof(o.dateordered::timestamp with time zone, 'MM'::character
varying)::timestamp with time zone OPERATOR(+) si.invoiceday) + 14))
OR si.invoicefrequency = 'M'::bpchar
AND trunc(o.dateordered::timestamp with time zone)
<= ((firstof(getdate(), 'MM'::character varying)::timestamp with time zone
OPERATOR(+) si.invoicedaycutoff) - 1)
AND trunc(getdate()) >=
((firstof(o.dateordered::timestamp with time zone, 'MM'::character
varying)::timestamp with time zone OPERATOR(+) si.invoiceday) - 1)))
GROUP BY o.ad_client_id, o.ad_org_id, o.c_bpartner_id, o.c_order_id,
o.documentno, o.dateordered, o.c_doctype_id
Any suggestion?
--
View this message in context:
http://postgresql.nabble.com/Reverse-Engineering-search-constraints-are-not-explicitly-stated-in-the-tables-from-the-VIEW-tp5826625.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
