Subject: Re: OLAP/reporting queries fall into nested loops over seq
 scans or other horrible planner choices
To: pgsql-performance@postgresql.org
References: <f1b85c37-e4b5-c158-5f67-a780a2191e20@gusw.net>
 <BN6PR15MB142620448377EAA48AA8650D855C0@BN6PR15MB1426.namprd15.prod.outlook.com>
From: Gunther <raj@gusw.net>
Message-ID: <c3cf6a5c-d622-3b39-36b6-a37da59f3760@gusw.net>
Date: Thu, 2 Nov 2017 12:36:13 -0400
User-Agent: Mozilla/5.0 (Windows NT 10.0; WOW64; rv:52.0) Gecko/20100101
 Thunderbird/52.4.0
MIME-Version: 1.0
In-Reply-To: 
 <BN6PR15MB142620448377EAA48AA8650D855C0@BN6PR15MB1426.namprd15.prod.outlook.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit
Content-Language: en-US
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

Thanks for your support Laurent.

I have an idea on one thing you said:

> Just adding to your voice. I recently experienced the same issue with a complex multi-table view, including pivots, and was surprised to see all the nested loops everywhere
and here is the clue for me:
> in spite of indices being available.
I would say that sometimes indexes are detrimental. If you don't need 
them for other reasons, you might want to not have them. And without the 
index, the Nested Loop strategy might not be chosen.

But that is a side-issue, because it can often not be avoided. Just 
saying in case it might help.

I also found the opposite now. In the query that made me "blow the lid" 
and "complain" here, my team decided to add an index and that did not 
get rid of Nested Loops but at least made the inner table access indexed 
rather than a table scan and the performance ended up OK. But it's not 
always predictable, and these indexes could trap the planner into 
sub-optimal solutions still.

I think there is an opportunity for a PgSQL query plan extension, 
especially wen dealing with CTE (WITH-clauses), PgSQL could make them a 
temporary table and add indexes that it needs for it on the fly, because 
after it has done one pass over the inner loop sequential scan it knows 
perfectly well how many rows it has, and knowing how many more 
iterations are coming from the sub-query that's driving the Nested Loop, 
it could decide that it's much faster to put an index on the nested 
relation, temporarily materialized. Or it could even decide to change 
it's plan mid-way and do the Hash Join.

This is why I had always dreamed that the PgSQL optimizer had some easy 
API where one could plug in experimental strategies. I personally am 
extremely efficient with XSLT for complex intelligent algorithms, and I 
dream of a PgSQL query plan structure exposed as XML which an XSLT 
plugin could then process to edit the plan. People could experiment with 
awesome intelligent new strategies based on statistics gathered along 
the way of the execution.

regards,
-Gunther


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance