Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nwe96-0007Tn-Qd for pgsql-admin@arkaria.postgresql.org; Thu, 02 Jun 2022 06:17:24 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1nwe94-00065Y-Ig for pgsql-admin@arkaria.postgresql.org; Thu, 02 Jun 2022 06:17:22 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nwe94-00065O-1p for pgsql-admin@lists.postgresql.org; Thu, 02 Jun 2022 06:17:22 +0000 Received: from mail-oi1-x230.google.com ([2607:f8b0:4864:20::230]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1nwe91-0001UJ-93 for pgsql-admin@lists.postgresql.org; Thu, 02 Jun 2022 06:17:21 +0000 Received: by mail-oi1-x230.google.com with SMTP id k11so5394911oia.12 for ; Wed, 01 Jun 2022 23:17:19 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=message-id:date:mime-version:user-agent:subject:content-language:to :references:from:in-reply-to; bh=1zSj6AJ3U9iLQ9tvODpEqsvUQGPVVm/1DT8L+GIWbQw=; b=dmkAyBTfXCH1xuZ0Jweq7O2LeXG1Ck1iy40zbyFWFt+95eTnxJHSWdJ3vrkhkVlTIt y6D2c6H6qJGhuvPVH1qvmZFanwZasZG4W2gk8Cnn3RPiCKRCO12NKJXNVSs/nl7H2yan 6WTsx8gKvmYY0MpMHIkZ9osZ7zqw70aX6lkitVZRLKY7wY7uYFlkiPsBtf6+rNEQPKJc 1haYdG4vuO7vKZxtixOW6ivKniXnt81UNFXKmT3jOuA5TJS+XQSl3OsyBmjUArc2HzUT 7q7bx+QE8qImYZq5PhvNYiDjDBXlr3NQHVb1lsw8hgCNX4oIFnpq6uwDyROo57nsWMv6 lFiQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=x-gm-message-state:message-id:date:mime-version:user-agent:subject :content-language:to:references:from:in-reply-to; bh=1zSj6AJ3U9iLQ9tvODpEqsvUQGPVVm/1DT8L+GIWbQw=; b=2cXom/1E1V6HLXP1m4HxHGeDZhQo91dRfNr0DMZxak3LINIdUxbwm+Zg4Pk1Wc9maW QQBlcKgQiZHJ/BPve8vPCUuZCPE6qI8bpWOgT50kywYgWBq7t1htPCp0NFaLRalCEO/i pBEyLzS8vym8T8iogvmOAtffZaCZ4E0gA5IutkcdIWnC22u6Fp1NxFb4pNN8e83Z+65F Ml6tKKyVfQ8ZTrEMd2mWwn+rjEDwjJoC5KtRpo2sxD5l1fZ6pU4QKC8jLT8ww2zjOsi5 tYBi4KhwW/2dJ6swVYB+P/FXA8Dh43qyZauPFaIVwfFQGZ+d2pWrpScd+oaHSRygeMx2 PghA== X-Gm-Message-State: AOAM530ITAGzu0oF++dHtlhEWovZ5wYKCK7nPnTxHnBZd/DZSmBKqgyA P3D25WFYhr1+T4bvzFbli8oDSIh1IwE= X-Google-Smtp-Source: ABdhPJwTOYVVL3f235Xjml4VCYLRz9uZWA58aR98unximQKZ2ad+XkHJuxPFaBLA44yPuZ+xOvmTyA== X-Received: by 2002:a05:6808:1454:b0:328:82a9:cb08 with SMTP id x20-20020a056808145400b0032882a9cb08mr1777160oiv.106.1654150638267; Wed, 01 Jun 2022 23:17:18 -0700 (PDT) Received: from [192.168.88.10] (ip68-11-68-85.no.no.cox.net. [68.11.68.85]) by smtp.googlemail.com with ESMTPSA id i7-20020a056870890700b000f32e094abbsm1786123oao.23.2022.06.01.23.17.17 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Wed, 01 Jun 2022 23:17:17 -0700 (PDT) Content-Type: multipart/alternative; boundary="------------ajzi3K0iQaR0eiGbkBYRI0xJ" Message-ID: Date: Thu, 2 Jun 2022 01:17:16 -0500 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:91.0) Gecko/20100101 Thunderbird/91.8.1 Subject: Re: how to calibrate the cost model parameters Content-Language: en-US To: pgsql-admin@lists.postgresql.org References: <68bd3f240bc5792eb098418656445c3ebd0488c5.camel@cybertec.at> <1ec517a25fd893f783e3bd4606eccf036b0c6696.camel@cybertec.at> <175811.1654008254@sss.pgh.pa.us> From: Ron In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------ajzi3K0iQaR0eiGbkBYRI0xJ Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit Manually (aka through a cron script) ANALYZE those seven tables on a regular basis.  During the business day, I ANALYZE some tables *every two hours*. On 5/31/22 21:20, jian xu wrote: > > Thanks everyone. The reason I asked this question is, my system has a > complex query, which uses CTE, then join 7 other tables. It also has where > clause using json data filter. Sometimes it runs slowly(5 minutes), but > sometimes it runs fast(2 seconds), (query text is same , no parameter). I > know it is because data distribution change. The table is  updated > frequently. > > I compared the execution plan, the fast query joins other tables first, > then join CTE, however, the slow question joins CTE first, then join other > tables. I analyzed the tables, it didn’t work, I increased the column > sampling from 100 to 10000 for all the columns used in where clause, > didn’t work. I set the random io cost to 1, it didn’t work. > > I am not able to modify the query. The only solution I find is > > 1. use |join_collapse_limit|to force the join order, however, it has > limitation, I need to modify the code to set it before submitting the > query, and set it back to original value after query run, in case it > didn’t change back, other query will affect. > 2. Set both sequence and random io cost to 0.1, it can always generate > correct plan. > > So I suspect the cost model parameters are not fully optimized on my > system, the fast and slow execution plan cost might be very close, so a > little bit data change will cause the plan change, and generated a wrong plan. > > Thanks > > James > > Sent from Mail for Windows > > *From: *Tom Lane > *Sent: *Tuesday, May 31, 2022 10:44 AM > *To: *Laurenz Albe > *Cc: *Jeff Janes ; jian xu > ; pgsql-admin@postgresql.org > *Subject: *Re: how to calibrate the cost model parameters > > Laurenz Albe writes: > > I think that the values of those parameters are more set by experience > and tradition than > > by measurable physical evidence.  In a way, it just happens to work > (mostly). > > For example, who says that on a spinning disk, random I/O is four times > as slow as > > sequential I/O?  Very likely, you have to factor in that part of the I/O > requests are > > satisfied from the kernel page cache. > > The 4.0 value *is* based on some real evidence --- many years ago, I put > in a lot of machine time to get an estimate of that for non-cached fetches > on the hardware I was using at the time.  If somebody did that over again > today, they'd likely get a somewhat different value, even assuming they > were still using spinning-rust storage.  But it doesn't matter too much. > A value around that is probably fine for rotating storage, while if you > are using SSD it's likely better to use something around 1.0, and beyond > that any inaccuracies in the cost constants are usually swamped by > imperfections of the cost models and other data.  For instance, it's not > that useful to sweat about what the right value is for cpu_operator_cost > when we've never spent any serious effort on assigning plausible procost > factors to different functions.  (Worse, the whole notion that a given > function has a fixed cost independent of its input data is sadly > inadequate.) > >                         regards, tom lane > -- Angular momentum makes the world go 'round. --------------ajzi3K0iQaR0eiGbkBYRI0xJ Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit Manually (aka through a cron script) ANALYZE those seven tables on a regular basis.  During the business day, I ANALYZE some tables every two hours.

On 5/31/22 21:20, jian xu wrote:

Thanks everyone. The reason I asked this question is, my system has a complex query, which uses CTE, then join 7 other tables. It also has where clause using json data filter. Sometimes it runs slowly(5 minutes), but sometimes it runs fast(2 seconds), (query text is same , no parameter). I know it is because data distribution change. The table is  updated frequently.

I compared the execution plan, the fast query joins other tables first, then join CTE, however, the slow question joins CTE first, then join other tables. I analyzed the tables, it didn’t work, I increased the column sampling from 100 to 10000 for all the columns used in where clause, didn’t work. I set the random io cost to 1, it didn’t work.

I am not able to modify the query. The only solution I find is

  1. use join_collapse_limit to force the join order, however, it has limitation, I need to modify the code to set it before submitting the query, and set it back to original value after query run, in case it didn’t change back, other query will affect.
  2. Set both sequence and random io cost to 0.1, it can always generate correct plan.

So I suspect the cost model parameters are not fully optimized on my system, the fast and slow execution plan cost might be very close, so a little bit data change will cause the plan change, and generated a wrong plan.

Thanks

James

 

Sent from Mail for Windows

 

From: Tom Lane
Sent: Tuesday, May 31, 2022 10:44 AM
To: Laurenz Albe
Cc: Jeff Janes; jian xu; pgsql-admin@postgresql.org
Subject: Re: how to calibrate the cost model parameters

 

Laurenz Albe <laurenz.albe@cybertec.at> writes:
> I think that the values of those parameters are more set by experience and tradition than
> by measurable physical evidence.  In a way, it just happens to work (mostly).
> For example, who says that on a spinning disk, random I/O is four times as slow as
> sequential I/O?  Very likely, you have to factor in that part of the I/O requests are
> satisfied from the kernel page cache.

The 4.0 value *is* based on some real evidence --- many years ago, I put
in a lot of machine time to get an estimate of that for non-cached fetches
on the hardware I was using at the time.  If somebody did that over again
today, they'd likely get a somewhat different value, even assuming they
were still using spinning-rust storage.  But it doesn't matter too much.
A value around that is probably fine for rotating storage, while if you
are using SSD it's likely better to use something around 1.0, and beyond
that any inaccuracies in the cost constants are usually swamped by
imperfections of the cost models and other data.  For instance, it's not
that useful to sweat about what the right value is for cpu_operator_cost
when we've never spent any serious effort on assigning plausible procost
factors to different functions.  (Worse, the whole notion that a given
function has a fixed cost independent of its input data is sadly
inadequate.)

                        regards, tom lane

 


--
Angular momentum makes the world go 'round.
--------------ajzi3K0iQaR0eiGbkBYRI0xJ--