Re: What does it mean? Plan stats and double rainbows.

On Thu, Jun 9, 2016 at 3:33 PM, David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> wrote:

> On Thu, Jun 9, 2016 at 5:11 PM, Michael Moore <michaeljmoore(at)gmail(dot)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