BUG #15797: Wrong Execution Plan

The following bug has been logged on the website:

Bug reference: 15797
Logged by: Rodrigo Garcia
Email address: rodrigo(dot)garcia(at)cross-join(dot)com
PostgreSQL version: 11.1
Operating system: Linux
Description:

Theres is a difference of the plan generated just only by putting the
filters inside a inner query or outside a inner query. Inner query doesn't
have a grouping function in the column being filtered so the optimizer
shouldn't generate a Sequential Scan when it have all it needs to index the
table.

Bad plan is in situation 1), good plan in situation 2) below.

1) Filtering outside inner query

xviewer-r2=# explain
xviewer-r2-# select *
xviewer-r2-# from (
xviewer-r2(# select sn.begin_interval_time, sn.instance_number, tsname,
filenr, filename,
xviewer-r2(# phyrds, coalesce(LAG(phyrds,1) OVER (partition by
sn.instance_number, tsname, filenr ORDER BY sn.snap_id),phyrds)
prev_phyrds,
xviewer-r2(# phywrts, coalesce(LAG(phywrts,1) OVER (partition by
sn.instance_number, tsname, filenr ORDER BY sn.snap_id),phywrts)
prev_phywrts,
xviewer-r2(# readtim, coalesce(LAG(readtim,1) OVER (partition by
sn.instance_number, tsname, filenr ORDER BY sn.snap_id),readtim)
prev_readtim,
xviewer-r2(# writetim, coalesce(LAG(writetim,1) OVER (partition by
sn.instance_number, tsname, filenr ORDER BY sn.snap_id),writetim)
prev_writetim
xviewer-r2(# from rogers_ssp_rcust.dba_hist_snapshot sn inner join
rogers_ssp_rcust.dba_hist_filestatxs f1 on sn.snap_id = f1.snap_id and
sn.dbid = f1.dbid and f1.instance_number = sn.instance_number
xviewer-r2(# ) INNERQUERY
xviewer-r2-# where begin_interval_time >= now()+'-1h' and
begin_interval_time < now();
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
Subquery Scan on a (cost=2901049.42..3476168.31 rows=1 width=232)
Filter: ((a.begin_interval_time < now()) AND (a.begin_interval_time >=
(now() + '-01:00:00'::interval)))
-> WindowAgg (cost=2901049.42..3240892.40 rows=10456707 width=238)
-> Sort (cost=2901049.42..2927191.19 rows=10456707 width=110)
Sort Key: sn.instance_number, f1.tsname, f1.filenr,
sn.snap_id
-> Hash Join (cost=237.74..466719.86 rows=10456707
width=110)
Hash Cond: ((f1.snap_id = sn.snap_id) AND (f1.dbid =
sn.dbid) AND (f1.instance_number = sn.instance_number))
-> Seq Scan on dba_hist_filestatxs f1
(cost=0.00..384112.07 rows=10456707 width=111)
-> Hash (cost=133.54..133.54 rows=5954 width=28)
-> Seq Scan on dba_hist_snapshot sn
(cost=0.00..133.54 rows=5954 width=28)
(10 rows)

2) Changing the where clause just to be inside the inner query A:

xviewer-r2=# explain
xviewer-r2-# select *
xviewer-r2-# from (
xviewer-r2(# select sn.begin_interval_time, sn.instance_number, tsname,
filenr, filename,
xviewer-r2(# phyrds, coalesce(LAG(phyrds,1) OVER (partition by
sn.instance_number, tsname, filenr ORDER BY sn.snap_id),phyrds)
prev_phyrds,
xviewer-r2(# phywrts, coalesce(LAG(phywrts,1) OVER (partition by
sn.instance_number, tsname, filenr ORDER BY sn.snap_id),phywrts)
prev_phywrts,
xviewer-r2(# readtim, coalesce(LAG(readtim,1) OVER (partition by
sn.instance_number, tsname, filenr ORDER BY sn.snap_id),readtim)
prev_readtim,
xviewer-r2(# writetim, coalesce(LAG(writetim,1) OVER (partition by
sn.instance_number, tsname, filenr ORDER BY sn.snap_id),writetim)
prev_writetim
xviewer-r2(# from rogers_ssp_rcust.dba_hist_snapshot sn inner join
rogers_ssp_rcust.dba_hist_filestatxs f1 on sn.snap_id = f1.snap_id and
sn.dbid = f1.dbid and f1.instance_number = sn.instance_number
xviewer-r2(# where begin_interval_time >= now()+'-1h' and
begin_interval_time < now()
xviewer-r2(# ) INNERQUERY;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
Subquery Scan on a (cost=3352.77..3427.40 rows=1756 width=232)
-> WindowAgg (cost=3352.77..3409.84 rows=1756 width=238)
-> Sort (cost=3352.77..3357.16 rows=1756 width=110)
Sort Key: sn.instance_number, f1.tsname, f1.filenr,
sn.snap_id
-> Nested Loop (cost=0.85..3258.13 rows=1756 width=110)
-> Index Only Scan using dhs_01x on dba_hist_snapshot
sn (cost=0.29..8.31 rows=1 width=28)
Index Cond: ((begin_interval_time >= (now() +
'-01:00:00'::interval)) AND (begin_interval_time < now()))
-> Index Scan using dba_hist_filestatxspk on
dba_hist_filestatxs f1 (cost=0.56..3231.95 rows=1787 width=111)
Index Cond: ((snap_id = sn.snap_id) AND (dbid =
sn.dbid) AND (instance_number = sn.instance_number))
(9 rows)