Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

cached plan doesn't work for explain commands? #4

Open
lemontree-mxh opened this issue Apr 25, 2020 · 0 comments
Open

cached plan doesn't work for explain commands? #4

lemontree-mxh opened this issue Apr 25, 2020 · 0 comments

Comments

@lemontree-mxh
Copy link

lemontree-mxh commented Apr 25, 2020

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?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

1 participant