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

From: Peter Geoghegan <pg(at)heroku(dot)com>
To: Kouhei Kaigai <kaigai(at)ak(dot)jp(dot)nec(dot)com>
Cc: Greg Stark <stark(at)mit(dot)edu>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Our trial to TPC-DS but optimizer made unreasonable plan
Date: 2015-08-26 23:31:00
Message-ID: CAM3SWZSyGu7onO=eR2CAow+-VmB8j+QTF6S49LukVRQn_1U7hw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

On Mon, Aug 17, 2015 at 6:40 AM, Kouhei Kaigai <kaigai(at)ak(dot)jp(dot)nec(dot)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 argument without indirect memory reference.
> This mechanism will also make sense for HashAggregate logic, to reduce
> the cost of function invocations.
>
> Please comment on the idea I noticed here.

Is this a 9.5-based system? If so, then you'd benefit from the
memcmp() pre-check within varstr_cmp() by being on 9.5, since the
pre-check is not limited to cases that use text/varchar SortSupport --
this could make a big difference here. If not, then it might be
somewhat helpful to add a pre-check that considers total binary
equality only before bcTruelen() is ever called. Not so sure about the
latter idea, though.

I'm not sure if it would help with hash aggregates to use something
like SortSupport to avoid fmgr overhead. It might make enough of a
difference to matter, but maybe the easier win would come from
considering simple binary equality first, and only then using an
equality operator (think HOT style checks). That would have the
advantage of requiring no per-type/operator class support at all,
since it's safe to assume that binary equality is a proxy for
"equivalence" of sort order (or whatever we call the case where
5.00::numeric and 5.000::numeric are considered equal).

--
Peter Geoghegan

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Kouhei Kaigai 2015-08-26 23:55:16 Re: Custom Scans and private data
Previous Message Andreas Karlsson 2015-08-26 23:12:54 Re: [PATCH] Reload SSL certificates on SIGHUP