Re: Query slow for new participants

Hello,

Things to Try Before You Post
-> I went through these steps and they did not bring any difference.

Information You Need To Include
Postgres version
"PostgreSQL 10.6 on x86_64-pc-linux-gnu, compiled by gcc (Debian
6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit"

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'

Table "public.companyarticledb"
Column | Type | Collation |
Nullable | Default
----------------------------+-----------------------------+-----------+----------+---------
companyid | integer | | not
null |
articleid | integer | | not
null |
price | numeric(19,4) | |
|
contractstartdate | timestamp without time zone | |
|
contractenddate | timestamp without time zone | |
|
enabled | boolean | |
|
visible | boolean | |
|
sheid | integer | |
|
inmassbalance | boolean | |
|
internalwastetype | character varying(50) | |
|
buom | character varying(50) | |
|
stockunit | numeric(18,2) | |
|
priceperbuom | numeric(19,4) | |
|
purchaseunit | numeric(18,2) | |
|
preventioncounselorid | integer | |
|
licenseprovided | boolean | |
|
licensevaliduntil | timestamp without time zone | |
|
authorisationlocationid | integer | |
|
priceagreementreference | character varying(50) | |
|
interfaceaccountid | integer | |
|
createdon | timestamp without time zone | |
|
modifiedby | integer | |
|
createdby | integer | |
|
modifiedon | timestamp without time zone | |
|
createdonsupplier | timestamp without time zone | |
|
modifiedbysupplier | integer | |
|
createdbysupplier | integer | |
|
modifiedonsupplier | timestamp without time zone | |
|
newprice | numeric(19,4) | |
|
newcontractstartdate | timestamp without time zone | |
|
newcontractenddate | timestamp without time zone | |
|
newpriceagreementreference | character varying(50) | |
|
licensereference | character varying(50) | |
|
purchasercomment | character varying(500) | |
|
reportingunit | character varying(5) | |
|
articlecode | character varying(50) | |
|
participantdescription | character varying(500) | |
|
motivationneeded | boolean | |
|
photourl | character varying(500) | |
|
reviewedshe | boolean | |
|
noinspectionuntil | timestamp without time zone | |
|
priority | boolean | |
|
needschecking | boolean | |
|
role | character varying(20) | |
|
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)

Table Metadata
relname, relpages, reltuples, relallvisible, relkind, relnatts,
relhassubclass, reloptions, pg_table_size(oid)
"companyarticledb" 6191886 "5.40276e+08" 6188459 "r" 44 false "50737979392"

EXPLAIN (ANALYZE, BUFFERS), not just EXPLAIN
"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"
"Planning time: 0.220 ms"
"Execution time: 1011.502 ms"

History

For all other participants this returns a lot faster, for this new
participant this goes very slow.

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"
"Planning time: 0.233 ms"
"Execution time: 0.087 ms"

This is applicable for all queries joining companyarticledb for
companyid='77' for this participant.
I do not know why this participant is different than the others except that
it was recently added.

Hardware
Standard DS15 v2 (20 vcpus, 140 GB memory)

Maintenance Setup
I did ran VACUUM on the db just before executing the queries
I did reindex the indexes on companyarticledb

GUC Settings

"application_name" "pgAdmin 4 - CONN:6235249" "client"
"bytea_output" "escape" "session"
"checkpoint_completion_target" "0.7" "configuration file"
"client_encoding" "UNICODE" "session"
"client_min_messages" "notice" "session"
"DateStyle" "ISO, MDY" "session"
"default_statistics_target" "100" "configuration file"
"effective_cache_size" "105GB" "configuration file"
"effective_io_concurrency" "200" "configuration file"
"external_pid_file" "/opt/bitnami/postgresql/tmp/postgresql.pid" "command
line"
"hot_standby" "on" "configuration file"
"listen_addresses" "*" "configuration file"
"maintenance_work_mem" "2GB" "configuration file"
"max_connections" "200" "configuration file"
"max_parallel_workers" "20" "configuration file"
"max_parallel_workers_per_gather" "10" "configuration file"
"max_stack_depth" "2MB" "environment variable"
"max_wal_senders" "16" "configuration file"
"max_wal_size" "2GB" "configuration file"
"max_worker_processes" "20" "configuration file"
"min_wal_size" "1GB" "configuration file"
"random_page_cost" "1.1" "configuration file"
"shared_buffers" "35GB" "configuration file"
"wal_buffers" "16MB" "configuration file"
"wal_keep_segments" "32" "configuration file"
"wal_level" "replica" "configuration file"
"work_mem" "18350kB" "configuration file"

Thank you for your help

Regards,
Kim

Op ma 25 feb. 2019 om 17:16 schreef Justin Pryzby <pryzby(at)telsasoft(dot)com>:

> On Mon, Feb 25, 2019 at 03:41:18AM -0700, Kim wrote:
> > Is there any way how I can make the queries fast for new participants?
> This
> > is a big problem, because for new participants, speed is even more
> > important.
> >
> > Thank you for your help.
>
> Could you include information requested here ?
> https://wiki.postgresql.org/wiki/Slow_Query_Questions
>
> Justin
>

--
Met vriendelijke groeten,