[SQL] 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