why is PG NOT pushing where clause to VIEW ? Resulting in bad performance.

Hello Support group.

I am wondering why a certain query is NOT pushing the where clause to the
view. See query and explain plan:

View create statement for xts_ws_currency_view is:

CREATE OR REPLACE VIEW tstream.xts_ws_currency_view AS
SELECT max(price.xcur_id) AS id,
iil.xtws_id AS wsid
FROM xts_invoice_item_link iil
JOIN xts_invoice_item ii ON ii.id = iil.xtin_id AND ii.active =
'Y'::bpchar
JOIN xts_price price ON price.id = ii.xpri_id
WHERE (ii.discriminator::text = 'XtsStatisticalInvoiceItem'::text OR
ii.discriminator::text = 'XtsLineItem'::text OR ii.discriminator::text =
'XtsCorrectionItem'::text) AND iil.xtws_id IS NOT NULL
GROUP BY iil.xtws_id;

-- bad explain plan:

explain analyze select iil.xtin_id, rate.rate--
sum(calcinvoiceitem(iil.xtin_id)*rate.rate)
from xts_workspec ws
join xts_ws_currency_view currencyView on currencyView.wsid = ws.id
join xts_invoice_item_link iil on iil.xtws_id = ws.id
join xts_exchange_rate rate on currencyView.id = rate.xcur_id
left join xts_invoice inv on (ws.xtin_id = inv.id and
date_trunc('month',inv.quarter_date) = rate.exchange_date)
where ws.id in (1322715, 1322604, 1322641, 1322513, 1322544, 1323201,
1322569, 1322875, 1322745, 1322678, 1322610, 1322632, 1322881)
and ws.active = 'Y'
and date_trunc('month', coalesce(ws.provision_date, ws.creation_date)) =
rate.exchange_date;

Nested Loop (cost=272344.18..278611.57 rows=1 width=12) (actual
time=17043.258..18528.436 rows=10 loops=1)
-> Nested Loop (cost=272343.75..278610.56 rows=1 width=16) (actual
time=17043.238..18528.304 rows=10 loops=1)
Join Filter: ((max(price.xcur_id)) = rate.xcur_id)
Rows Removed by Join Filter: 160
-> Hash Join (cost=272343.47..278597.12 rows=2 width=44) (actual
time=17035.156..18519.767 rows=10 loops=1)
Hash Cond: (iil_1.xtws_id = ws.id)
-> HashAggregate (cost=272280.95..274914.06 rows=263311
width=16) (actual time=16980.167..17950.542 rows=1214734 loops=1)
Group Key: iil_1.xtws_id
-> Hash Join (cost=157598.86..267896.71 rows=876849
width=16) (actual time=5626.185..15332.062 rows=1331610 loops=1)
Hash Cond: (ii.xpri_id = price.id)
-> Hash Join (cost=153012.66..250853.22
rows=983681 width=16) (actual time=5182.983..13162.960 rows=1331610 loops=1)
Hash Cond: (iil_1.xtin_id = ii.id)
-> Index Scan using xiil_xtws on
xts_invoice_item_link iil_1 (cost=0.43..56483.02 rows=1486432 width=16)
(actual time=0.125..4546.698 rows
=1482245 loops=1)
Index Cond: (xtws_id IS NOT NULL)
-> Hash (cost=118934.42..118934.42
rows=1960385 width=12) (actual time=5146.639..5146.639 rows=2328340 loops=1)
Buckets: 2097152 (originally
2097152) Batches: 4 (originally 2) Memory Usage: 49153kB
-> Seq Scan on xts_invoice_item ii
(cost=0.00..118934.42 rows=1960385 width=12) (actual time=0.073..3303.359
rows=2328340 loops=1)
Filter: ((active =
'Y'::bpchar) AND (((discriminator)::text =
'XtsStatisticalInvoiceItem'::text) OR ((discriminator)::text = 'Xt
sLineItem'::text) OR ((discriminator)::text = 'XtsCorrectionItem'::text)))
Rows Removed by Filter: 634009
-> Hash (cost=2824.42..2824.42 rows=140942
width=12) (actual time=441.305..441.305 rows=138430 loops=1)
Buckets: 262144 Batches: 1 Memory Usage:
7997kB
-> Seq Scan on xts_price price
(cost=0.00..2824.42 rows=140942 width=12) (actual time=0.040..207.336
rows=138430 loops=1)
-> Hash (cost=62.37..62.37 rows=11 width=28) (actual
time=0.369..0.369 rows=10 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Index Scan using xts_workspec_pkey on xts_workspec
ws (cost=0.43..62.37 rows=11 width=28) (actual time=0.064..0.334 rows=10
loops=1)
Index Cond: (id = ANY
('{1322715,1322604,1322641,1322513,1322544,1323201,1322569,1322875,1322745,1322678,1322610,1322632,1322881}'::integer[]))
Filter: (active = 'Y'::bpchar)
Rows Removed by Filter: 3
-> Index Scan using exch_dt on xts_exchange_rate rate
(cost=0.28..6.56 rows=13 width=16) (actual time=0.819..0.832 rows=17
loops=10)
Index Cond: (exchange_date = date_trunc('month'::text,
COALESCE(ws.provision_date, ws.creation_date)))
-> Index Scan using xiil_xtws on xts_invoice_item_link iil
(cost=0.43..0.98 rows=3 width=16) (actual time=0.008..0.009 rows=1 loops=10)
Index Cond: (xtws_id = iil_1.xtws_id)
Planning time: 353.027 ms
Execution time: 18545.612 ms

Now if I add:

and currencyView.wsid in (1322715, 1322604, 1322641, 1322513, 1322544,
1323201, 1322569, 1322875, 1322745, 1322678, 1322610, 1322632, 1322881)

in the query I get:

Nested Loop (cost=2.29..296.04 rows=2 width=12) (actual time=0.425..2.375
rows=10 loops=1)
Buffers: shared hit=292
-> Nested Loop (cost=1.86..287.53 rows=1 width=16) (actual
time=0.400..2.207 rows=10 loops=1)
Join Filter: ((max(price.xcur_id)) = rate.xcur_id)
Rows Removed by Join Filter: 160
Buffers: shared hit=252
-> Merge Join (cost=1.58..280.81 rows=1 width=44) (actual
time=0.348..1.414 rows=10 loops=1)
Merge Cond: (iil_1.xtws_id = ws.id)
Buffers: shared hit=202
-> GroupAggregate (cost=1.15..218.35 rows=4 width=16)
(actual time=0.227..0.935 rows=13 loops=1)
Group Key: iil_1.xtws_id
Buffers: shared hit=152
-> Nested Loop (cost=1.15..218.25 rows=11 width=16)
(actual time=0.145..0.802 rows=13 loops=1)
Buffers: shared hit=152
-> Nested Loop (cost=0.86..214.34 rows=12
width=16) (actual time=0.120..0.568 rows=13 loops=1)
Buffers: shared hit=113
-> Index Scan using xiil_xtws on
xts_invoice_item_link iil_1 (cost=0.43..61.92 rows=18 width=16) (actual
time=0.033..0.182 rows=15 loops=1)
Index Cond: ((xtws_id IS NOT NULL)
AND (xtws_id = ANY
('{1322715,1322604,1322641,1322513,1322544,1323201,1322569,1322875,1322745,1322678,1322610,1322632,1322881}'::
bigint[])))
Buffers: shared hit=53
-> Index Scan using xts_invoice_item_pkey
on xts_invoice_item ii (cost=0.43..8.46 rows=1 width=12) (actual
time=0.017..0.019 rows=1 loops=15)
Index Cond: (id = iil_1.xtin_id)
Filter: ((active = 'Y'::bpchar) AND
(((discriminator)::text = 'XtsStatisticalInvoiceItem'::text) OR
((discriminator)::text = 'XtsLineItem'::text) OR ((discriminator
)::text = 'XtsCorrectionItem'::text)))
Rows Removed by Filter: 0
Buffers: shared hit=60
-> Index Scan using xts_price_pkey on xts_price
price (cost=0.29..0.32 rows=1 width=12) (actual time=0.009..0.011 rows=1
loops=13)
Index Cond: (id = ii.xpri_id)
Buffers: shared hit=39
-> Index Scan using xts_workspec_pkey on xts_workspec ws
(cost=0.43..62.37 rows=11 width=28) (actual time=0.105..0.390 rows=10
loops=1)
Index Cond: (id = ANY
('{1322715,1322604,1322641,1322513,1322544,1323201,1322569,1322875,1322745,1322678,1322610,1322632,1322881}'::integer[]))
Filter: (active = 'Y'::bpchar)
Rows Removed by Filter: 3
Buffers: shared hit=50
-> Index Scan using exch_dt on xts_exchange_rate rate
(cost=0.28..6.56 rows=13 width=16) (actual time=0.011..0.042 rows=17
loops=10)
Index Cond: (exchange_date = date_trunc('month'::text,
COALESCE(ws.provision_date, ws.creation_date)))
Buffers: shared hit=50
-> Index Scan using xiil_xtws on xts_invoice_item_link iil
(cost=0.43..8.49 rows=3 width=16) (actual time=0.008..0.010 rows=1 loops=10)
Index Cond: (xtws_id = iil_1.xtws_id)
Buffers: shared hit=40
Planning time: 12.452 ms
Execution time: 2.596 ms

Why does PG not push the id's to the view , I am joinin like this:
currencyView.wsid = ws.id

So it should be possible (I think)

Please let me know if you need any further info.

Bots explain plans are working on the same data ofcourse and have been
executed one after the other, no changes to parameters have been done.

Kind regards,

Herwig