Re: Query slow for new participants - Mailing list pgsql-performance

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.454rows=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)