Обсуждение: What does it mean? Plan stats and double rainbows.
I'm having a difficult time finding documentation on EXPLAIN PLAN stats. For example, in
' -> Nested Loop Left Join (cost=0.43..1415.06 rows=2 width=1377) (actual time=0.093..0.093 rows=0 loops=1)'
what does 0.43..1415.06 mean? Is that a range? If so, it seems rather pointless, like saying "somewhere between 0 and infinity".
Also, is there a way to tell the query planner to limit the search for the best plan on a per statement basis. I know that this exists as a config parameter but I think that applies to the entire database. I have a query that takes 9 times more time to plan than it does to execute.
' -> Nested Loop Left Join (cost=0.43..1415.06 rows=2 width=1377) (actual time=0.093..0.093 rows=0 loops=1)'
what does 0.43..1415.06 mean? Is that a range? If so, it seems rather pointless, like saying "somewhere between 0 and infinity".
Also, is there a way to tell the query planner to limit the search for the best plan on a per statement basis. I know that this exists as a config parameter but I think that applies to the entire database. I have a query that takes 9 times more time to plan than it does to execute.
Tia,
Mike
Michael Moore schrieb am 09.06.2016 um 23:11: > I'm having a difficult time finding documentation on EXPLAIN PLAN stats. For example, in > ' -> Nested Loop Left Join (cost=0.43..1415.06 rows=2 width=1377) (actual time=0.093..0.093 rows=0loops=1)' > what does 0.43..1415.06 mean? Is that a range? >If so, it seems rather pointless, like saying "somewhere between 0 and infinity". https://www.postgresql.org/docs/current/static/using-explain.html#USING-EXPLAIN-BASICS
I'm having a difficult time finding documentation on EXPLAIN PLAN stats. For example, in
' -> Nested Loop Left Join (cost=0.43..1415.06 rows=2 width=1377) (actual time=0.093..0.093 rows=0 loops=1)'
what does 0.43..1415.06 mean? Is that a range? If so, it seems rather pointless, like saying "somewhere between 0 and infinity".
Thomas' link should cover this but it isn't giving you a probabilistic range , its giving the time to first record and time to fetch all records. For stuff like semi-joins you don't care about the total number of records found only that you can quickly find one record. A limited requirement but since plan output is somewhat generic in nature it always gives both numbers.
Also, is there a way to tell the query planner to limit the search for the best plan on a per statement basis. I know that this exists as a config parameter but I think that applies to the entire database. I have a query that takes 9 times more time to plan than it does to execute.
All parameters (in this context) are session-local in use; even if the default value is set at the scope of the entire server. You can make them transaction-local by using "SET LOCAL" instead of a plain "SET" when changing them within the session.
David J.
On Thu, Jun 9, 2016 at 3:33 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:
I read the content at the link Thomas provided. It pretty much clears things up. My query is basically a simple SELECT on a single table with 4 left join laterals. And then UNION ALL with 9 almost identical SELECT statements. I'm having a difficult time finding documentation on EXPLAIN PLAN stats. For example, in
' -> Nested Loop Left Join (cost=0.43..1415.06 rows=2 width=1377) (actual time=0.093..0.093 rows=0 loops=1)'
what does 0.43..1415.06 mean? Is that a range? If so, it seems rather pointless, like saying "somewhere between 0 and infinity".Thomas' link should cover this but it isn't giving you a probabilistic range , its giving the time to first record and time to fetch all records. For stuff like semi-joins you don't care about the total number of records found only that you can quickly find one record. A limited requirement but since plan output is somewhat generic in nature it always gives both numbers.Also, is there a way to tell the query planner to limit the search for the best plan on a per statement basis. I know that this exists as a config parameter but I think that applies to the entire database. I have a query that takes 9 times more time to plan than it does to execute.All parameters (in this context) are session-local in use; even if the default value is set at the scope of the entire server. You can make them transaction-local by using "SET LOCAL" instead of a plain "SET" when changing them within the session.David J.
I tried messing around with:
--set session geqo_threshold = '12';
--set session geqo_effort = '5';
set session from_collapse_limit = '1';
set session join_collapse_limit = '1';
but nothing made the planning phase faster, in fact it was often much slower.
Planning time: 31.351 ms
Execution time: 5.266 ms
The above is without any SET SESSIONs and is about as good as it gets.
No question here. Just thought you might be interested.
Thanks David and Thomas for your help.
Mike