Message-ID: <5C74889A.3050007@sqlexec.com>
Date: Mon, 25 Feb 2019 19:30:18 -0500
From: MichaelDBA <MichaelDBA@sqlexec.com>
User-Agent: Postbox 5.0.25 (Windows/20180328)
MIME-Version: 1.0
To: Justin Pryzby <pryzby@telsasoft.com>
CC: support@mekong.be, pgsql-performance@postgresql.org
Subject: Re: Query slow for new participants
References: <1551091278468-0.post@n3.nabble.com>
 <20190225161607.GQ28750@telsasoft.com>
 <CALJ2KGUnuO6HEWM9pwpXbF4NF-jLHmZSub5YsYD_A=GfHMU6Eg@mail.gmail.com>
 <20190225235919.GR28750@telsasoft.com>
In-Reply-To: <20190225235919.GR28750@telsasoft.com>
Content-Type: multipart/alternative;
 boundary="------------000606050100040805050208"
Precedence: bulk

This is a multi-part message in MIME format.
--------------000606050100040805050208
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Regarding shared_buffers, please install the pg_buffercache extension 
and run the recommended queries with that extension during high load 
times to really get an idea about the right value for shared_buffers.  
Let's take the guess work out of it.

Regards,
Michael Vitale

> Justin Pryzby <mailto:pryzby@telsasoft.com>
> Monday, February 25, 2019 6:59 PM
> On Tue, Feb 26, 2019 at 12:22:39AM +0100, support@mekong.be wrote:
>
>> Hardware
>> Standard DS15 v2 (20 vcpus, 140 GB memory)
>
>> "effective_cache_size" "105GB" "configuration file"
>> "effective_io_concurrency" "200" "configuration file"
>> "maintenance_work_mem" "2GB" "configuration file"
>> "max_parallel_workers" "20" "configuration file"
>> "max_parallel_workers_per_gather" "10" "configuration file"
>> "max_worker_processes" "20" "configuration file"
>> "random_page_cost" "1.1" "configuration file"
>> "shared_buffers" "35GB" "configuration file"
>> "work_mem" "18350kB" "configuration file"
>
> I don't know for sure, but 35GB is very possibly too large shared_buffers.  The
> rule of thumb is "start at 25% of RAM" but I think anything over 10-15GB is
> frequently too large, unless you can keep the whole DB in RAM (can you?)
>
>> Table Metadata
>> relname, relpages, reltuples, relallvisible, relkind, relnatts, relhassubclass, reloptions, pg_table_size(oid)
>> "companyarticledb" 6191886 "5.40276e+08" 6188459 "r" 44 false "50737979392"
>
> work_mem could probably benefit from being larger (just be careful that you
> don't end up with 20x parallel workers running complex plans each node of which
> using 100MB work_mem).
>
>> Full Table and Index Schema
>> The difference is very bad for the new company,  even on the simplest query
>>
>>     SELECT * FROM CompanyArticleDB
>>       WHERE CompanyId = '77'
>>       AND ArticleId= '7869071'
>
> It sounds to me like the planner thinks that the distribution of companyID and
> articleID are independent, when they're not.  For example it think that
> companyID=33 filters out 99% of the rows.
>
>>   companyid                  | integer                     |           | not null |
>>   articleid                  | integer                     |           | not null |
>
>> EXPLAIN (ANALYZE, BUFFERS), not just EXPLAIN
>>    SELECT * FROM CompanyArticleDB
>>      WHERE CompanyId = '77'
>>      AND ArticleId= '7869071'
>> "Index Scan using ix_companyarticledb_company on companyarticledb (cost=0.57..2.80 rows=1 width=193) (actual time=1011.335..1011.454 rows=1 loops=1)"
>> "  Index Cond: (companyid = 77)"
>> "  Filter: (articleid = 7869071)"
>> "  Rows Removed by Filter: 2674361"
>> "  Buffers: shared hit=30287"
>
>> Example for another participant, there another index is used.
>> "Index Scan using pk_pricedb on companyarticledb  (cost=0.57..2.79 rows=1 width=193) (actual time=0.038..0.039 rows=0 loops=1)"
>> "  Index Cond: ((companyid = 39) AND (articleid = 7869071))"
>> "  Buffers: shared hit=4"
>
>> I do not know why this participant is different than the others except that
>> it was recently added.
>
> Were the tables ANALYZEd since then ?  You could check:
> SELECT * FROM pg_stat_user_tables WHERE relname='companyarticledb';
>
> If you have small number of companyIDs (~100), then the table statistics may
> incldue a most-common-values list, and companies not in the MCV list may end up
> with different query plans, even without correlation issues.
>
> It looks like the NEW company has ~3e6 articles, out of a total ~5e8 articles.
> The planner may think that companyID doesn't exist at all, so scanning the idx
> on companyID will be slightly faster than using the larger, composite index on
> companyID,articleID.
>
> Justin
>
>> Indexes:
>>      "pk_pricedb" PRIMARY KEY, btree (companyid, articleid)
>>      "EnabledIndex" btree (enabled)
>>      "ix_companyarticledb_article" btree (articleid)
>>      "ix_companyarticledb_company" btree (companyid)
>>      "participantarticlecodeindex" btree (articlecode)
>>      "participantdescriptionindex" gin (participantdescription gin_trgm_ops)
>> Foreign-key constraints:
>>      "fk_companyarticledb_accountsdb" FOREIGN KEY (modifiedby) REFERENCES accountsdb(id)
>>      "fk_companyarticledb_accountsdb1" FOREIGN KEY (createdby) REFERENCES accountsdb(id)
>>      "fk_companyarticledb_accountsdb2" FOREIGN KEY (preventioncounselorid) REFERENCES accountsdb(id)
>>      "fk_companyarticledb_articledb" FOREIGN KEY (articleid) REFERENCES articledb(id)
>>      "fk_companyarticledb_companydb" FOREIGN KEY (companyid) REFERENCES companydb(id)
>>      "fk_companyarticledb_interfaceaccountdb" FOREIGN KEY (interfaceaccountid) REFERENCES interfaceaccountdb(id)
>>      "fk_companyarticledb_supplieraccountdb" FOREIGN KEY (createdbysupplier) REFERENCES supplieraccountdb(id)
>>      "fk_companyarticledb_supplieraccountdb1" FOREIGN KEY (modifiedbysupplier) REFERENCES supplieraccountdb(id)
>


--------------000606050100040805050208
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit

<html><head>
<meta content="text/html; charset=ISO-8859-1" http-equiv="Content-Type">
</head><body bgcolor="#FFFFFF" text="#000000">Regarding shared_buffers, 
please install the pg_buffercache extension and run the recommended 
queries with that extension during high load times to really get an idea
 about the right value for shared_buffers.  Let's take the guess work 
out of it.<br>
<br>
Regards,<br>
Michael Vitale<br>
<span>

</span><br>
<blockquote style="border: 0px none;" 
cite="mid:20190225235919.GR28750@telsasoft.com" type="cite">
  <div style="margin:30px 25px 10px 25px;" class="__pbConvHr"><div 
style="width:100%;border-top:2px solid #EDF1F4;padding-top:10px;">   <div
 
style="display:inline-block;white-space:nowrap;vertical-align:middle;width:49%;">
   	<a moz-do-not-send="true" href="mailto:pryzby@telsasoft.com" 
style="color:#485664 
!important;padding-right:6px;font-weight:500;text-decoration:none 
!important;">Justin Pryzby</a></div>   <div 
style="display:inline-block;white-space:nowrap;vertical-align:middle;width:48%;text-align:
 right;">     <font color="#909AA4"><span style="padding-left:6px">Monday,
 February 25, 2019 6:59 PM</span></font></div>    </div></div>
  <div style="color:#909AA4;margin-left:24px;margin-right:24px;" 
__pbrmquotes="true" class="__pbConvBody"><pre wrap="">On Tue, Feb 26, 2019 at 12:22:39AM +0100, <a class="moz-txt-link-abbreviated" href="mailto:support@mekong.be">support@mekong.be</a> wrote:

</pre><blockquote type="cite"><pre wrap="">Hardware
Standard DS15 v2 (20 vcpus, 140 GB memory)
</pre></blockquote><pre wrap=""><!---->
</pre><blockquote type="cite"><pre wrap="">"effective_cache_size" "105GB" "configuration file"
"effective_io_concurrency" "200" "configuration file"
"maintenance_work_mem" "2GB" "configuration file"
"max_parallel_workers" "20" "configuration file"
"max_parallel_workers_per_gather" "10" "configuration file"
"max_worker_processes" "20" "configuration file"
"random_page_cost" "1.1" "configuration file"
"shared_buffers" "35GB" "configuration file"
"work_mem" "18350kB" "configuration file"
</pre></blockquote><pre wrap=""><!---->
I don't know for sure, but 35GB is very possibly too large shared_buffers.  The
rule of thumb is "start at 25% of RAM" but I think anything over 10-15GB is
frequently too large, unless you can keep the whole DB in RAM (can you?)

</pre><blockquote type="cite"><pre wrap="">Table Metadata
relname, relpages, reltuples, relallvisible, relkind, relnatts, relhassubclass, reloptions, pg_table_size(oid)
"companyarticledb" 6191886 "5.40276e+08" 6188459 "r" 44 false "50737979392"
</pre></blockquote><pre wrap=""><!---->
work_mem could probably benefit from being larger (just be careful that you
don't end up with 20x parallel workers running complex plans each node of which
using 100MB work_mem).

</pre><blockquote type="cite"><pre wrap="">Full Table and Index Schema
The difference is very bad for the new company,  even on the simplest query

   SELECT * FROM CompanyArticleDB
     WHERE CompanyId = '77'
     AND ArticleId= '7869071'
</pre></blockquote><pre wrap=""><!---->
It sounds to me like the planner thinks that the distribution of companyID and
articleID are independent, when they're not.  For example it think that
companyID=33 filters out 99% of the rows.

</pre><blockquote type="cite"><pre wrap=""> companyid                  | integer                     |           | not null |
 articleid                  | integer                     |           | not null |
</pre></blockquote><pre wrap=""><!---->
</pre><blockquote type="cite"><pre wrap="">EXPLAIN (ANALYZE, BUFFERS), not just EXPLAIN
  SELECT * FROM CompanyArticleDB
    WHERE CompanyId = '77'
    AND ArticleId= '7869071'
"Index Scan using ix_companyarticledb_company on companyarticledb (cost=0.57..2.80 rows=1 width=193) (actual time=1011.335..1011.454 rows=1 loops=1)"
"  Index Cond: (companyid = 77)"
"  Filter: (articleid = 7869071)"
"  Rows Removed by Filter: 2674361"
"  Buffers: shared hit=30287"
</pre></blockquote><pre wrap=""><!---->
</pre><blockquote type="cite"><pre wrap="">Example for another participant, there another index is used.
"Index Scan using pk_pricedb on companyarticledb  (cost=0.57..2.79 rows=1 width=193) (actual time=0.038..0.039 rows=0 loops=1)"
"  Index Cond: ((companyid = 39) AND (articleid = 7869071))"
"  Buffers: shared hit=4"
</pre></blockquote><pre wrap=""><!---->
</pre><blockquote type="cite"><pre wrap="">I do not know why this participant is different than the others except that
it was recently added.
</pre></blockquote><pre wrap=""><!---->
Were the tables ANALYZEd since then ?  You could check:
SELECT * FROM pg_stat_user_tables WHERE relname='companyarticledb';

If you have small number of companyIDs (~100), then the table statistics may
incldue a most-common-values list, and companies not in the MCV list may end up
with different query plans, even without correlation issues.

It looks like the NEW company has ~3e6 articles, out of a total ~5e8 articles.
The planner may think that companyID doesn't exist at all, so scanning the idx
on companyID will be slightly faster than using the larger, composite index on
companyID,articleID.

Justin

</pre><blockquote type="cite"><pre wrap="">Indexes:
    "pk_pricedb" PRIMARY KEY, btree (companyid, articleid)
    "EnabledIndex" btree (enabled)
    "ix_companyarticledb_article" btree (articleid)
    "ix_companyarticledb_company" btree (companyid)
    "participantarticlecodeindex" btree (articlecode)
    "participantdescriptionindex" gin (participantdescription gin_trgm_ops)
Foreign-key constraints:
    "fk_companyarticledb_accountsdb" FOREIGN KEY (modifiedby) REFERENCES accountsdb(id)
    "fk_companyarticledb_accountsdb1" FOREIGN KEY (createdby) REFERENCES accountsdb(id)
    "fk_companyarticledb_accountsdb2" FOREIGN KEY (preventioncounselorid) REFERENCES accountsdb(id)
    "fk_companyarticledb_articledb" FOREIGN KEY (articleid) REFERENCES articledb(id)
    "fk_companyarticledb_companydb" FOREIGN KEY (companyid) REFERENCES companydb(id)
    "fk_companyarticledb_interfaceaccountdb" FOREIGN KEY (interfaceaccountid) REFERENCES interfaceaccountdb(id)
    "fk_companyarticledb_supplieraccountdb" FOREIGN KEY (createdbysupplier) REFERENCES supplieraccountdb(id)
    "fk_companyarticledb_supplieraccountdb1" FOREIGN KEY (modifiedbysupplier) REFERENCES supplieraccountdb(id)
</pre></blockquote><pre wrap=""><!---->
</pre></div>
</blockquote>
<br>
</body></html>

--------------000606050100040805050208--