public inbox for [email protected]  

help / color / mirror / Atom feed

From: Teodor Sigaev <[email protected]>
To: Tomas Vondra <[email protected]>
To: Claudio Freire <[email protected]>
Cc: PostgreSQL-Dev <[email protected]>
Subject: Re: POC: GROUP BY optimization
Date: Thu, 7 Jun 2018 16:41:05 +0300
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<CAGTBQpbW+8jc8VRE39-3=cWSr8aGRRFds=s2BNQoh4ddO2X7UQ@mail.gmail.com>
	<[email protected]>

> So the costing was fairly trivial, we simply do something like
> 
>      comparison_cost = 2.0 * cpu_operator_cost;
> 
>      sort_cost = comparison_cost * tuples * LOG2(tuples);
> 
> which essentially ignores that there might be multiple columns, or that
> the columns may have sort operator with different costs.
Agree. And distribution of keys.
> 
> The question is how reliable the heuristics can be. The current patch
> uses just plain ndistinct, but that seems rather unreliable but I don't
> have a clear idea how to improve that - we may have MCV for the columns
> and perhaps some extended statistics, but I'm not sure how far we can
> run with that.
v8 already uses another algorithm.

> 
> Essentially what we need to estimate the number of comparisons for each
> column, to compute better comparison_cost.
Exactly

>> Priorization of the user-provided order can be as simple as giving
>> that comparison_cost a small handicap.
> 
> I see no point in doing that, and I don't recall a single place in the
> planner where we do that. If the user specified ORDER BY, we'll slap an
> explicit Sort on top when needed (which acts as the handicap, but in a
> clear way). Otherwise we don't do such things - it'd be just plain
> confusing (consider "ORDER BY a,b" vs. "ORDER BY b,c" with same data
> types, ndistinct etc. but unexpectedly different costs). Also, what
> would be a good value for the handicap?

Again agree. If we have fixed order of columns (ORDER BY) then we should not try 
to reorder it. Current patch follows that if I didn't a mistake.

-- 
Teodor Sigaev                                   E-mail: [email protected]
                                                    WWW: http://www.sigaev.ru/

view thread (89+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: POC: GROUP BY optimization
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox