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

From: Gunther
Subject: Re: [PERFORM] Re: OLAP/reporting queries fall into nested loops overseq scans or other horrible planner choices
Date: ,
Msg-id: a6cc0c0a-9047-adf4-c07a-74ee42a25536@gusw.net
(view: Whole thread, Raw)
In response to: [PERFORM] Re: OLAP/reporting queries fall into nested loops over seq scans orother horrible planner choices  (legrand legrand)
Responses: Re: [PERFORM] Re: OLAP/reporting queries fall into nested loops overseq scans or other horrible planner choices  (Dave Nicponski)
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, )

On 11/3/2017 10:55, legrand legrand wrote:
> To limit NL usage, wouldn't a modified set of Planner Cost Constants
> https://www.postgresql.org/docs/current/static/runtime-config-query.html
> <https://www.postgresql.org/docs/current/static/runtime-config-query.html>
>
> seq_page_cost
> random_page_cost
> cpu_tuple_cost
> cpu_index_tuple_cost
> cpu_operator_cost
>
> be more hash join freindly (as Oracle' optimizer_index_cost_adj )?
>
I twiddled with some of these and could nudge it toward a Sort Merge 
instead NL. But it's hit or miss.

May be there should be a tool which you can run periodically which will 
test out the installation to see how IO, CPU, and memory performs. Or, 
again, these statistics should be collected during normal operation so 
that nobody needs to guess them or test them in complex procedures. As 
the system runs, it should sample the seq_page_cost and random_page_cost 
(noticing that it has a SSD or HDD) and it should see how much disk read 
is from cache and how much goes out to disk. Why isn't the executor of 
queries the best person to ask for these cost constants?

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: Dave Nicponski
Date:
Subject: Re: [PERFORM] Re: OLAP/reporting queries fall into nested loops overseq scans or other horrible planner choices
From: 刘瑞
Date:
Subject: [PERFORM] Unnecessary DISTINCT while primary key in SQL