On 2019-03-25 4:06 PM, Ron wrote:
On 3/25/19 8:15 AM, Frank wrote:
It would be interesting to see what the query planner tries to do with
this:
WHERE
CASE
WHEN a.tran_type = 'ar_rec' THEN y.posted
WHEN a.tran_type = 'cb_rec' THEN w.posted
END = '1'
I have attached the schema showing the full VIEW definition, and the
result of the following EXPLAIN -
EXPLAIN SELECT * FROM ccc.ar_trans WHERE cust_row_id = 4 AND tran_date
BETWEEN '2015-06-01' AND '2015-06-30'.
Because I have used 'WHERE tran_date' in the query, and tran_date is
also derived from a CASE expression, I imagine that will also add some
complication.
I am running PostgreSQL 11.1 on Fedora 29.
Frank
CREATE OR REPLACE VIEW ccc.ar_trans AS
SELECT 'ar_inv'::text AS tran_type,
a.row_id AS tran_row_id,
a.tran_number,
a.cust_row_id,
a.tran_date,
a.text,
a.inv_net_cust + a.inv_tax_cust AS amount_cust,
a.inv_net_local + a.inv_tax_local AS amount_local
FROM ccc.ar_tran_inv a
WHERE a.posted = true
UNION ALL
SELECT 'ar_crn'::text AS tran_type,
a.row_id AS tran_row_id,
a.tran_number,
a.cust_row_id,
a.tran_date,
a.text,
0::numeric - (a.crn_net_cust + a.crn_tax_cust) AS amount_cust,
0::numeric - (a.crn_net_local + a.crn_tax_local) AS amount_local
FROM ccc.ar_tran_crn a
WHERE a.posted = true
UNION ALL
SELECT 'arec'::text AS tran_type,
a.row_id AS tran_row_id,
a.tran_number,
a.cust_row_id,
CASE
WHEN a.tran_type::text = 'ar_rec'::text THEN y.tran_date
WHEN a.tran_type::text = 'cb_rec'::text THEN w.tran_date
ELSE NULL::date
END AS tran_date,
CASE
WHEN a.tran_type::text = 'ar_rec'::text THEN y.text
WHEN a.tran_type::text = 'cb_rec'::text THEN w.text
ELSE NULL::character varying
END AS text,
0::numeric - a.arec_cust AS amount_cust,
round(0::numeric - round(a.arec_amount /
CASE
WHEN a.tran_type::text = 'ar_rec'::text THEN y.tran_exch_rate
WHEN a.tran_type::text = 'cb_rec'::text THEN w.tran_exch_rate
ELSE NULL::numeric
END, u.scale), 2) AS amount_local
FROM ccc.ar_rec_subtran a
LEFT JOIN ccc.ar_tran_rec_det z ON z.row_id = a.tran_det_row_id
LEFT JOIN ccc.ar_tran_rec y ON y.row_id = z.tran_row_id
LEFT JOIN ccc.cb_tran_rec_det x ON x.row_id = a.tran_det_row_id
LEFT JOIN ccc.cb_tran_rec w ON w.row_id = x.tran_row_id
LEFT JOIN ccc.adm_params v ON v.row_id = 1
LEFT JOIN ccc.adm_currencies u ON u.row_id = v.local_curr_id
WHERE
CASE
WHEN a.tran_type::text = 'ar_rec'::text THEN y.posted
WHEN a.tran_type::text = 'cb_rec'::text THEN w.posted
ELSE NULL::boolean
END = true
UNION ALL
SELECT 'ar_disc'::text AS tran_type,
a.row_id AS tran_row_id,
a.tran_number,
a.cust_row_id,
a.tran_date,
a.text,
0::numeric - (a.disc_net_cust + a.disc_tax_cust) AS amount_cust,
0::numeric - (a.disc_net_local + a.disc_tax_local) AS amount_local
FROM ccc.ar_tran_disc a
WHERE a.posted = true
EXPLAIN SELECT * FROM ccc.ar_trans WHERE cust_row_id = 4 AND tran_date BETWEEN '2015-06-01' AND '2015-06-30'.
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Append (cost=0.00..2545.77 rows=404 width=132)
-> Seq Scan on ar_tran_inv a (cost=0.00..780.70 rows=300 width=125)
Filter: (posted AND (tran_date >= '2015-06-01'::date) AND (tran_date <= '2015-06-30'::date) AND (cust_row_id = 4))
-> Seq Scan on ar_tran_crn a_1 (cost=0.00..14.91 rows=1 width=172)
Filter: (posted AND (tran_date >= '2015-06-01'::date) AND (tran_date <= '2015-06-30'::date) AND (cust_row_id = 4))
-> Nested Loop Left Join (cost=1104.50..1729.72 rows=102 width=150)
-> Hash Left Join (cost=1104.50..1723.56 rows=102 width=73)
Hash Cond: (a_2.tran_det_row_id = x.row_id)
Filter: (CASE WHEN ((a_2.tran_type)::text = 'ar_rec'::text) THEN y.posted WHEN ((a_2.tran_type)::text = 'cb_rec'::text) THEN w.posted ELSE NULL::boolean END AND (CASE WHEN ((a_2.tran_type)::text = 'ar_rec'::text) THEN y.tran_date WHEN ((a_2.tran_type)::text = 'cb_rec'::text) THEN w.tran_date ELSE NULL::date END >= '2015-06-01'::date) AND (CASE WHEN ((a_2.tran_type)::text = 'ar_rec'::text) THEN y.tran_date WHEN ((a_2.tran_type)::text = 'cb_rec'::text) THEN w.tran_date ELSE NULL::date END <= '2015-06-30'::date))
-> Hash Right Join (cost=1102.15..1714.30 rows=1833 width=59)
Hash Cond: (y.row_id = z.tran_row_id)
-> Seq Scan on ar_tran_rec y (cost=0.00..511.25 rows=22025 width=22)
-> Hash (cost=1079.24..1079.24 rows=1833 width=45)
-> Hash Right Join (cost=553.11..1079.24 rows=1833 width=45)
Hash Cond: (z.row_id = a_2.tran_det_row_id)
-> Seq Scan on ar_tran_rec_det z (cost=0.00..397.53 rows=22053 width=8)
-> Hash (cost=530.20..530.20 rows=1833 width=41)
-> Seq Scan on ar_rec_subtran a_2 (cost=0.00..530.20 rows=1833 width=41)
Filter: (cust_row_id = 4)
-> Hash (cost=2.23..2.23 rows=10 width=24)
-> Hash Left Join (cost=1.04..2.23 rows=10 width=24)
Hash Cond: (x.tran_row_id = w.row_id)
-> Seq Scan on cb_tran_rec_det x (cost=0.00..1.10 rows=10 width=8)
-> Hash (cost=1.02..1.02 rows=2 width=24)
-> Seq Scan on cb_tran_rec w (cost=0.00..1.02 rows=2 width=24)
-> Materialize (cost=0.00..2.08 rows=1 width=4)
-> Nested Loop Left Join (cost=0.00..2.08 rows=1 width=4)
Join Filter: (u.row_id = v.local_curr_id)
-> Seq Scan on adm_params v (cost=0.00..1.01 rows=1 width=4)
Filter: (row_id = 1)
-> Seq Scan on adm_currencies u (cost=0.00..1.03 rows=3 width=8)
-> Seq Scan on ar_tran_disc a_3 (cost=0.00..14.38 rows=1 width=172)
Filter: (posted AND (tran_date >= '2015-06-01'::date) AND (tran_date <= '2015-06-30'::date) AND (cust_row_id = 4))
(33 rows)
