Re: [PERFORM] OLAP/reporting queries fall into nested loops over seqscans or other horrible planner choices

From: Gunther
Subject: Re: [PERFORM] OLAP/reporting queries fall into nested loops over seqscans or other horrible planner choices
Date: ,
Msg-id: c3cf6a5c-d622-3b39-36b6-a37da59f3760@gusw.net
(view: Whole thread, Raw)
In response to: Re: [PERFORM] OLAP/reporting queries fall into nested loops over seqscans or other horrible planner choices  ("")
List: pgsql-performance

Tree view

[PERFORM] OLAP/reporting queries fall into nested loops over seq scans or otherhorrible planner choices  (Gunther, )
 Re: [PERFORM] OLAP/reporting queries fall into nested loops over seqscans or other horrible planner choices  ("", )
  Re: [PERFORM] OLAP/reporting queries fall into nested loops over seqscans or other horrible planner choices  (Gunther, )
 Re: [PERFORM] OLAP/reporting queries fall into nested loops overseq scans or other horrible planner choices  (Laurenz Albe, )
  Re: [PERFORM] OLAP/reporting queries fall into nested loops over seqscans or other horrible planner choices  (Gunther, )
   [PERFORM] Re: OLAP/reporting queries fall into nested loops over seq scans orother horrible planner choices  (legrand legrand, )
   Re: [PERFORM] OLAP/reporting queries fall into nested loops overseq scans or other horrible planner choices  (Laurenz Albe, )
  [PERFORM] Re: OLAP/reporting queries fall into nested loops over seq scans orother horrible planner choices  (Thomas Kellerer, )
   Re: [PERFORM] Re: OLAP/reporting queries fall into nested loops overseq scans or other horrible planner choices  (Adam Brusselback, )
    Re: [PERFORM] Re: OLAP/reporting queries fall into nested loops overseq scans or other horrible planner choices  (Gunther, )
     Re: [PERFORM] Re: OLAP/reporting queries fall into nested loops overseq scans or other horrible planner choices  (Gunther, )
     [PERFORM] Re: OLAP/reporting queries fall into nested loops over seq scans orother horrible planner choices  (legrand legrand, )
      Re: [PERFORM] Re: OLAP/reporting queries fall into nested loops overseq scans or other horrible planner choices  (Gunther, )
       Re: [PERFORM] Re: OLAP/reporting queries fall into nested loops overseq scans or other horrible planner choices  (Dave Nicponski, )

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,
andwas 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 ()
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance


pgsql-performance by date:

From: legrand legrand
Date:
Subject: [PERFORM] Re: OLAP/reporting queries fall into nested loops over seq scans orother horrible planner choices
From: Laurenz Albe
Date:
Subject: Re: [PERFORM] OLAP/reporting queries fall into nested loops overseq scans or other horrible planner choices