sr_plan icon indicating copy to clipboard operation
sr_plan copied to clipboard

cached plan doesn't work for explain commands?

Open lemontree-mxh opened this issue 5 years ago • 0 comments

postgres=# select show_plan(1437592932);
                 show_plan
-------------------------------------------
 (Gather)
 ("  Output: i, im5, im100, im1000")
 ("  Workers Planned: 2")
 ("  ->  Parallel Seq Scan on public.j1")
 ("        Output: i, im5, im100, im1000")
 ("        Filter: (j1.i = 1)")
(6 rows)

postgres=# select query from sr_plans where query_hash = 1437592932;
             query
-------------------------------
 select * from j1 where i = 1;
(1 row)


postgres=# explain select * from j1 where i = 1;  -- I created the index after we created the cached plan
                           QUERY PLAN
----------------------------------------------------------------
 Index Scan using j1_i on j1  (cost=0.43..8.45 rows=1 width=16)
   Index Cond: (i = 1)
(2 rows)


postgres=# explain analyze select * from j1 where i = 1;
                                                QUERY PLAN
----------------------------------------------------------------------------------------------------------
 Index Scan using j1_i on j1  (cost=0.43..8.45 rows=1 width=16) (actual time=0.050..0.051 rows=1 loops=1)
   Index Cond: (i = 1)
 Planning Time: 0.122 ms
 Execution Time: 0.092 ms
(4 rows)

postgres=# select * from j1 where i = 1;
 i | im5 | im100 | im1000
---+-----+-------+--------
 1 |   1 |     1 |      1
(1 row)

Time: 615.249 ms

Here we can see we create a sr_plan for select * from sr_plans where i = 1, which is a Parallel Seq Scan. and during the execution of the plan, it can choose the cached plan as expected.

But if people use explain / explain analyze to check the plan, it will get a wrong result. I think a better solution is to use the cached plan as well to avoid such confusion.

What do you think?

lemontree-mxh avatar Apr 25 '20 13:04 lemontree-mxh