Re: Query slow for new participants
| От | support@mekong.be |
|---|---|
| Тема | Re: Query slow for new participants |
| Дата | |
| Msg-id | CALJ2KGUnuO6HEWM9pwpXbF4NF-jLHmZSub5YsYD_A=GfHMU6Eg@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: Query slow for new participants (Justin Pryzby <pryzby@telsasoft.com>) |
| Ответы |
Re: Query slow for new participants
(Michael Lewis <mlewis@entrata.com>)
Re: Query slow for new participants (Laurenz Albe <laurenz.albe@cybertec.at>) |
| Список | pgsql-performance |
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@telsasoft.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,
В списке pgsql-performance по дате отправления:
Предыдущее
От: MichaelDBAДата:
Сообщение: Re: Massive parallel queue table causes index deterioration, butREINDEX fails with deadlocks.