Re: Query slow for new participants

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxxxx>:
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,


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux