BUG #15797: Wrong Execution Plan

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

BUG #15797: Wrong Execution Plan

PG Bug reporting form
The following bug has been logged on the website:

Bug reference:      15797
Logged by:          Rodrigo Garcia
Email address:      [hidden email]
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)

Reply | Threaded
Open this post in threaded view
|

Re: BUG #15797: Wrong Execution Plan

Tom Lane-2
PG Bug reporting form <[hidden email]> writes:
> Theres is a difference of the plan generated just only by putting the
> filters inside a inner query or outside a inner query.

You do realize that moving that WHERE clause changes the semantics
of the window functions?  The planner can't freely change the
filter location from one to the other.

                        regards, tom lane