BUG #15797: Wrong Execution Plan - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      15797
Logged by:          Rodrigo Garcia
Email address:      rodrigo.garcia@cross-join.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)