Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1eAeNH-0005VU-8c for pgsql-performance@arkaria.postgresql.org; Fri, 03 Nov 2017 15:59:15 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1eAeNG-0001Ln-Q3 for pgsql-performance@arkaria.postgresql.org; Fri, 03 Nov 2017 15:59:14 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1eAeLU-0006he-PI for pgsql-performance@postgresql.org; Fri, 03 Nov 2017 15:57:24 +0000 Received: from mail-qt0-x234.google.com ([2607:f8b0:400d:c0d::234]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1eAeLN-0008Hy-A7 for pgsql-performance@postgresql.org; Fri, 03 Nov 2017 15:57:23 +0000 Received: by mail-qt0-x234.google.com with SMTP id j58so3796292qtj.0 for ; Fri, 03 Nov 2017 08:57:16 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=seamlessdocs.com; s=google; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc; bh=78y4MUaGIYTKeaVR+UYxWPWTGhFtNhQT5PnuGUMe1RM=; b=JjttIBRfbec4HMYiNzp6i20t8MJy5zNvWolpzc5BW2udDocoF5YfmzDKJZKgdfRcnR /6jtecCNbjV9sm94a489XBj7V8cGIlpjpV9ubCBBS04y5puTDFWU3M/nAxq16mmGBtV/ 9SX7Ys5Szk9kT30pajrFiPSjb2OtAb/bfGeYA= X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:in-reply-to:references:from:date :message-id:subject:to:cc; bh=78y4MUaGIYTKeaVR+UYxWPWTGhFtNhQT5PnuGUMe1RM=; b=RB5aYl7HthIMnwhHsp/jP30IboxpzdWyA1i7EhmuCd7jcdBz823MGGCnFibUEYFyVN r8itKBwbxlUhaBCX/OxU+VPrTmiIrwlN5eL9UAXpgpSsI/K6W475yQWd+y0TWRUalW8h uDYy5XEZta+KMrxWkRADmfQPt2XM8RHq2UpHck5AqDfXJP7OGu/TP8w7kOyuLFytk9w0 Gs1tWQz97qvMdNGBH2PMjnnGWcJ5OoggFwybSl6aeMv/Tac0lG7ELyyTHMbteJzey+4o PCEGAvMBBaywPEK+Ldn0rArWBsZ5gPIRMwhGPEuhl6pLfUrG5UAsovCH1D+W0T5LRj4Y cOnQ== X-Gm-Message-State: AMCzsaXFLTp6zD85+hSylj+nlExyx0w4SB+mXqcvXesO6PZEV8/zY6ei Ab35onGfdbH+QyH90vGs+CToIguD0tG1vnFrg51THA== X-Google-Smtp-Source: ABhQp+Spx73Arn3YxwuqopizhD0asIMN+zwvE/4mcASt0TE4/fm70WQo1sceNf8btN5T3ipmIX2Nnixv2zke6mKLzCg= X-Received: by 10.200.19.7 with SMTP id e7mr11013455qtj.192.1509724635948; Fri, 03 Nov 2017 08:57:15 -0700 (PDT) MIME-Version: 1.0 Received: by 10.140.89.139 with HTTP; Fri, 3 Nov 2017 08:56:45 -0700 (PDT) In-Reply-To: References: <1509611428.3268.5.camel@cybertec.at> <1509701327868-0.post@n3.nabble.com> <9a76f13e-cdb1-1d8d-2178-67e6dcf169bc@gusw.net> <1509720945908-0.post@n3.nabble.com> From: Dave Nicponski Date: Fri, 3 Nov 2017 11:56:45 -0400 Message-ID: Subject: Re: Re: OLAP/reporting queries fall into nested loops over seq scans or other horrible planner choices To: Gunther Cc: pgsql-performance@postgresql.org Content-Type: multipart/alternative; boundary="089e0828d860a27ecb055d162848" List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org --089e0828d860a27ecb055d162848 Content-Type: text/plain; charset="UTF-8" Thank you Gunther for bringing this up. It's been bothering me quite a bit over time as well. Forgive the naive question, but does the query planner's cost estimator only track a single estimate of cost that gets accumulated and compared across plan variants? Or is it keeping a range or probabilistic distribution? I'm suspecting the former, but i bet either of the latter would fix this rapidly. The cases that frustrate me are where NL is chosen over something like HJ, where if the query planner is slightly wrong on the lower side, then NL would certainly beat HJ (but by relatively small amounts), but a slight error on the higher side mean that the NL gets punished tremendously, do to the big-o penalty difference it's paying over the HJ approach. Having the planner with some notion of the distribution might help it make a better assessment of the potential consequences for being slightly off in its estimates. If it notices that being off on a plan involving a NL sends the distribution off into hours instead of seconds, it could potentially avoid it even if it might be slightly faster in the mean. If i ever find time, maybe i'll try to play around with this idea and see how it performs... -dave- On Fri, Nov 3, 2017 at 11:13 AM, Gunther wrote: > 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 >> > > >> >> 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 (pgsql-performance@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance > -- Dave Nicponski Chief Technology Officer 917.696.3081 | dave@seamlessdocs.com 30 Vandam Street. 2nd Floor. NYC 855.77.SEAMLESS | SeamlessGov.com --089e0828d860a27ecb055d162848 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Thank you Gunther for bringing this up.=C2=A0 It's bee= n bothering me quite a bit over time as well.

Forgive th= e naive question, but does the query planner's cost estimator only trac= k a single estimate of cost that gets accumulated and compared across plan = variants?=C2=A0 Or is it keeping a range or probabilistic distribution?=C2= =A0 I'm suspecting the former, but i bet either of the latter would fix= this rapidly.

The cases that frustrate me are whe= re NL is chosen over something like HJ, where if the query planner is sligh= tly wrong on the lower side, then NL would certainly beat HJ (but by relati= vely small amounts), but a slight error on the higher side mean that the NL= gets punished tremendously, do to the big-o penalty difference it's pa= ying over the HJ approach.=C2=A0 Having the planner with some notion of the= distribution might help it make a better assessment of the potential conse= quences for being slightly off in its estimates.=C2=A0 If it notices that b= eing off on a plan involving a NL sends the distribution off into hours ins= tead of seconds, it could potentially avoid it even if it might be slightly= faster in the mean.

<fantasy> If i ever fin= d time, maybe i'll try to play around with this idea and see how it per= forms... </fantasy>

=C2=A0 =C2=A0-dave-

On Fri, No= v 3, 2017 at 11:13 AM, Gunther <raj@gusw.net> wrote:
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/d= ocs/current/static/runtime-config-query.html
<https://www.postgresql.o= rg/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 instea= d NL. But it's hit or miss.

May be there should be a tool which you can run periodically which will tes= t 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 h= ow much goes out to disk. Why isn't the executor of queries the best pe= rson to ask for these cost constants?

regards,
-Gunther



--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-pe= rformance



--
=

Dave Nicponski

Chief Technology Officer

917.696.3081

=C2=A0

|

=C2=A0

dave@seamlessdocs.com

30 Vandam Street. 2nd Floor. NYC=C2=A0
855.77.SEAMLESS= =C2=A0|=C2=A0SeamlessGov.com

--089e0828d860a27ecb055d162848--