agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedWhat does it mean? Plan stats and double rainbows.
4+ messages / 3 participants
[nested] [flat]
* What does it mean? Plan stats and double rainbows.
@ 2016-06-09 21:11 Michael Moore <michaeljmoore@gmail.com>
0 siblings, 2 replies; 4+ messages in thread
From: Michael Moore @ 2016-06-09 21:11 UTC (permalink / raw)
To: pgsql-sql
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.
Tia,
Mike
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: What does it mean? Plan stats and double rainbows.
@ 2016-06-09 21:52 Thomas Kellerer <spam_eater@gmx.net>
parent: Michael Moore <michaeljmoore@gmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: Thomas Kellerer @ 2016-06-09 21:52 UTC (permalink / raw)
To: pgsql-sql
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=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".
https://www.postgresql.org/docs/current/static/using-explain.html#USING-EXPLAIN-BASICS
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: What does it mean? Plan stats and double rainbows.
@ 2016-06-09 22:33 David G. Johnston <david.g.johnston@gmail.com>
parent: Michael Moore <michaeljmoore@gmail.com>
1 sibling, 1 reply; 4+ messages in thread
From: David G. Johnston @ 2016-06-09 22:33 UTC (permalink / raw)
To: Michael Moore <michaeljmoore@gmail.com>; +Cc: pgsql-sql
On Thu, Jun 9, 2016 at 5:11 PM, Michael Moore <michaeljmoore@gmail.com>
wrote:
> 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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: What does it mean? Plan stats and double rainbows.
@ 2016-06-10 18:12 Michael Moore <michaeljmoore@gmail.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Michael Moore @ 2016-06-10 18:12 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-sql
On Thu, Jun 9, 2016 at 3:33 PM, David G. Johnston <
david.g.johnston@gmail.com> wrote:
> On Thu, Jun 9, 2016 at 5:11 PM, Michael Moore <michaeljmoore@gmail.com>
> wrote:
>
>> 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 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 lateral*s. And then UNION ALL with 9 almost identical SELECT
statements.
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2016-06-10 18:12 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2016-06-09 21:11 What does it mean? Plan stats and double rainbows. Michael Moore <michaeljmoore@gmail.com>
2016-06-09 21:52 ` Thomas Kellerer <spam_eater@gmx.net>
2016-06-09 22:33 ` David G. Johnston <david.g.johnston@gmail.com>
2016-06-10 18:12 ` Michael Moore <michaeljmoore@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox