Re: [HACKERS] Our trial to TPC-DS but optimizer made unreasonable plan

On Thu, Aug 27, 2015 at 1:01 PM, Qingqing Zhou wrote: > On Wed, Aug 26, 2015 at 5:28 PM, Tom Lane wrote: >> >> After looking at the code a bit, IMO the most reasonable thing to do is to >> include this transformation in

Re: [HACKERS] Our trial to TPC-DS but optimizer made unreasonable plan

On 2015-08-19 15:14:03 -0700, Josh Berkus wrote: > Asking users to refactor their applications to add OFFSET 0 is a bit > painful, if we could take care of it via a backwards-compatibility GUC. > We have many users who are specifically using the CTE optimization > barrier to work around planner

Re: [HACKERS] Our trial to TPC-DS but optimizer made unreasonable plan

On Wed, Aug 26, 2015 at 5:28 PM, Tom Lane t...@sss.pgh.pa.us wrote: After looking at the code a bit, IMO the most reasonable thing to do is to include this transformation in inline_set_returning_functions(), perhaps renaming it to something like inline_srfs_and_ctes(). This is essentially

Re: [HACKERS] Our trial to TPC-DS but optimizer made unreasonable plan

On 27/08/15 13:36, Kouhei Kaigai wrote: [...] My measurement is done on v9.5 based system. So, it also seems to me replacement of CHAR(n) by VARCHAR(n) will make sense. Is there any reason to not simply use text instead of CHAR(n) or VARCHAR(n)? Text is also welcome, of course. -- NEC

Re: [HACKERS] Our trial to TPC-DS but optimizer made unreasonable plan

On Wed, Aug 19, 2015 at 10:32 AM, Qingqing Zhou zhouqq.postg...@gmail.com wrote: On Tue, Aug 18, 2015 at 5:59 PM, Kouhei Kaigai kai...@ak.jp.nec.com wrote: BTW, did you register the patch on the upcoming commit-fest? Not yet, it is in WIP status. While I am working on the patch, I found some

Re: [HACKERS] Our trial to TPC-DS but optimizer made unreasonable plan

On Mon, Aug 17, 2015 at 6:40 AM, Kouhei Kaigai kai...@ak.jp.nec.com wrote: I think SortSupport logic provides a reasonable way to solve this kind of problem. For example, btint4sortsupport() informs a function pointer of the fast version of comparator (btint4fastcmp) which takes two Datum

Re: [HACKERS] Our trial to TPC-DS but optimizer made unreasonable plan

Qingqing Zhou zhouqq.postg...@gmail.com writes: Above two queries essentially the same, but the second one is a non-optimal plan. The reason is that how my patch works: it put a substitution in front of SS_process_ctes(): /* * If there is a WITH list, process each WITH query and build

Re: [HACKERS] Our trial to TPC-DS but optimizer made unreasonable plan

I wrote: What I had in mind in 38448.1430519...@sss.pgh.pa.us was to convert CTEs into plain subqueries during the prepjointree phase, either just before or as part of the pull_up_subqueries pass (since you'd want the converted subquery to be flattened if possible). After looking at the code

Re: [HACKERS] Our trial to TPC-DS but optimizer made unreasonable plan

-Original Message- From: pgsql-hackers-ow...@postgresql.org [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Peter Geoghegan Sent: Thursday, August 27, 2015 8:31 AM To: Kaigai Kouhei(海外 浩平) Cc: Greg Stark; PostgreSQL-development Subject: Re: [HACKERS] Our trial to TPC-DS

Re: [HACKERS] Our trial to TPC-DS but optimizer made unreasonable plan

-Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: Thursday, August 27, 2015 9:03 AM To: Qingqing Zhou Cc: Kaigai Kouhei(海外 浩平); Greg Stark; PostgreSQL-development Subject: Re: [HACKERS] Our trial to TPC-DS but optimizer made unreasonable plan Qingqing Zhou

Re: [HACKERS] Our trial to TPC-DS but optimizer made unreasonable plan

On 27/08/15 13:36, Kouhei Kaigai wrote: [...] My measurement is done on v9.5 based system. So, it also seems to me replacement of CHAR(n) by VARCHAR(n) will make sense. Is there any reason to not simply use text instead of CHAR(n) or VARCHAR(n)? [...] -Gavin -- Sent via pgsql-hackers

Re: [HACKERS] Our trial to TPC-DS but optimizer made unreasonable plan

On Tue, Aug 18, 2015 at 5:59 PM, Kouhei Kaigai kai...@ak.jp.nec.com wrote: BTW, did you register the patch on the upcoming commit-fest? Not yet, it is in WIP status. I think it may be a helpful feature, if we can add alternative subquery-path towards cte-scan on set_cte_pathlist() and choose

Re: [HACKERS] Our trial to TPC-DS but optimizer made unreasonable plan

On 08/18/2015 04:40 PM, Qingqing Zhou wrote: Attached please find the WIP patch and also the ANALYZE results. Notes: the patch may not directly apply to head as some network issue here so my Linux box can't talk to git server. So, one of the things we previously mentioned is that currently

Re: [HACKERS] Our trial to TPC-DS but optimizer made unreasonable plan

Josh Berkus j...@agliodbs.com writes: On 08/18/2015 04:40 PM, Qingqing Zhou wrote: Attached please find the WIP patch and also the ANALYZE results. Notes: the patch may not directly apply to head as some network issue here so my Linux box can't talk to git server. So, one of the things we

Re: [HACKERS] Our trial to TPC-DS but optimizer made unreasonable plan

On 08/19/2015 01:32 PM, Tom Lane wrote: Josh Berkus j...@agliodbs.com writes: On 08/18/2015 04:40 PM, Qingqing Zhou wrote: Attached please find the WIP patch and also the ANALYZE results. Notes: the patch may not directly apply to head as some network issue here so my Linux box can't talk to

Re: [HACKERS] Our trial to TPC-DS but optimizer made unreasonable plan

On Mon, Aug 17, 2015 at 9:40 AM, Kouhei Kaigai kai...@ak.jp.nec.com wrote: I think SortSupport logic provides a reasonable way to solve this kind of problem. For example, btint4sortsupport() informs a function pointer of the fast version of comparator (btint4fastcmp) which takes two Datum

Re: [HACKERS] Our trial to TPC-DS but optimizer made unreasonable plan

On Wed, Aug 19, 2015 at 6:08 PM, Kouhei Kaigai kai...@ak.jp.nec.com wrote: Indeed, 6 of 8 grouping keys in this query uses bpchar() data type, so it is natural comparison function consumed larger portion of CPU cycles. Do we have any idea to assist these queries by the backend? With

Re: [HACKERS] Our trial to TPC-DS but optimizer made unreasonable plan

On Mon, Aug 17, 2015 at 6:40 AM, Kouhei Kaigai kai...@ak.jp.nec.com wrote: Here is one other thing I could learn from TPC-DS benchmark. The attached query is Q4 of TPC-DS, and its result was towards SF=100. It took long time to compete (about 30min), please see the attached EXPLAIN

Re: [HACKERS] Our trial to TPC-DS but optimizer made unreasonable plan

On Mon, Aug 17, 2015 at 6:40 AM, Kouhei Kaigai kai...@ak.jp.nec.com wrote: Here is one other thing I could learn from TPC-DS benchmark. The attached query is Q4 of TPC-DS, and its result was towards SF=100. It took long time to compete (about 30min), please see the attached EXPLAIN ANALYZE

Re: [HACKERS] Our trial to TPC-DS but optimizer made unreasonable plan

On Mon, Aug 17, 2015 at 9:40 AM, Kouhei Kaigai kai...@ak.jp.nec.com wrote: I think SortSupport logic provides a reasonable way to solve this kind of problem. For example, btint4sortsupport() informs a function pointer of the fast version of comparator (btint4fastcmp) which takes two Datum

Re: [HACKERS] Our trial to TPC-DS but optimizer made unreasonable plan

: [HACKERS] Our trial to TPC-DS but optimizer made unreasonable plan On Thu, Aug 13, 2015 at 2:49 AM, Kouhei Kaigai kai...@ak.jp.nec.com wrote: In fact, cost of HashJoin underlying Sort node is: - Hash Join (cost=621264.91..752685.48 rows=1 width=132) On the other hands

Re: [HACKERS] Our trial to TPC-DS but optimizer made unreasonable plan

On Thu, Aug 13, 2015 at 2:49 AM, Kouhei Kaigai kai...@ak.jp.nec.com wrote: In fact, cost of HashJoin underlying Sort node is: - Hash Join (cost=621264.91..752685.48 rows=1 width=132) On the other hands, NestedLoop on same place is: - Nested Loop (cost=0.00..752732.26 rows=1

Re: [HACKERS] Our trial to TPC-DS but optimizer made unreasonable plan

On Thu, Aug 13, 2015 at 2:49 AM, Kouhei Kaigai kai...@ak.jp.nec.com wrote: In fact, cost of HashJoin underlying Sort node is: - Hash Join (cost=621264.91..752685.48 rows=1 width=132) On the other hands, NestedLoop on same place is: - Nested Loop (cost=0.00..752732.26 rows=1