Re: Postgres chooses slow query plan from time to time

2021-09-14 Thread Kristjan Mustkivi
Hello! Both are of type varchar(30). So is this something odd: Filter: (((product_code)::text = ($1)::text) AND ((balance_type)::text = ($4)::text)) ? But why does it do the type-cast if both product_code and balance_type are of type text (although with constraint 30) and the values are also of

Re: EnterpriseDB

2021-09-14 Thread manish yadav
In addition to the Sbob comments, if we install the EDB with postgres compatibility option in such a case we may continue to use postgres user as default super user and other configuration parameters would remain the same as community PostgreSQL.   It would work perfectly fine with pgAdmin4 and

Re: Postgres chooses slow query plan from time to time

2021-09-14 Thread Tom Lane
Kristjan Mustkivi writes: >>> Filter: (((product_code)::text = ($1)::text) AND >>> ((balance_type)::text = ($4)::text)) > But the Primary Key is defined as btree (cage_code, cage_player_id, > product_code, balance_type, version) so this should be exactly that > (apart from the extra "version" col

Re: Postgres chooses slow query plan from time to time

2021-09-14 Thread Kristjan Mustkivi
On Tue, Sep 14, 2021 at 5:15 PM Tom Lane wrote: > > Kristjan Mustkivi writes: > > -> Index Scan Backward using player_balance_history_idx2 on > > mytable pbh (cost=0.70..21639.94 rows=3885 width=66) (actual > > time=5934.153..5934.153 rows=1 loops=1) > > Index Cond: ((

Re: EnterpriseDB

2021-09-14 Thread sbob
EnterpriseDB is basically postgres with the added oracle compatability and some added external tools. THe default user & db will no longer be postgres but 'enterprisedb', but it is still postgresql so you won't have any issues working with EDB if you already know postgres On 9/13/21 20:52, M

Re: Postgres chooses slow query plan from time to time

2021-09-14 Thread Tom Lane
Kristjan Mustkivi writes: > -> Index Scan Backward using player_balance_history_idx2 on > mytable pbh (cost=0.70..21639.94 rows=3885 width=66) (actual > time=5934.153..5934.153 rows=1 loops=1) > Index Cond: ((cage_code = $3) AND (cage_player_id = > $2) AND (modified_tim

Re: Postgres chooses slow query plan from time to time

2021-09-14 Thread Kristjan Mustkivi
I am very sorry, I indeed copy-pasted an incomplete plan. Here it is in full: 2021-09-14 06:55:33 UTC, pid=27576 db=mydb, usr=myuser, client=ip, app=PostgreSQL JDBC Driver, line=55 LOG: duration: 5934.165 ms plan: Query Text: SELECT * FROM myschema.mytable pbh WHERE pbh.product_code =

Re: Postgres chooses slow query plan from time to time

2021-09-14 Thread Jeff Janes
On Tue, Sep 14, 2021 at 3:55 AM Kristjan Mustkivi wrote: > Hello Tomas, > > The auto explain analyze caught this: > > 2021-09-14 06:55:33 UTC, pid=12345 db=mydb, usr=myuser, client=ip, > app=PostgreSQL JDBC Driver, line=55 LOG: duration: 5934.165 ms plan: > Query Text: SELECT * FROM mysche

Re: Postgres chooses slow query plan from time to time

2021-09-14 Thread Laurenz Albe
On Tue, 2021-09-14 at 10:55 +0300, Kristjan Mustkivi wrote: > 2021-09-14 06:55:33 UTC, pid=12345  db=mydb, usr=myuser, client=ip, > app=PostgreSQL JDBC Driver, line=55 LOG:  duration: 5934.165 ms  plan: >   Query Text: SELECT *   FROM myschema.mytable pbh WHERE > pbh.product_code = $1   AND pbh.cag

Re: Postgres chooses slow query plan from time to time

2021-09-14 Thread Kristjan Mustkivi
Hi Jeff, The specialized index is present due to some other queries and the index is used frequently (according to the statistics). I do agree that in this particular case the index btree (cage_code, cage_player_id, product_code, balance_type, modified_time) would solve the problem but at the mome

Re: Postgres chooses slow query plan from time to time

2021-09-14 Thread Kristjan Mustkivi
Hello Tomas, The auto explain analyze caught this: 2021-09-14 06:55:33 UTC, pid=12345 db=mydb, usr=myuser, client=ip, app=PostgreSQL JDBC Driver, line=55 LOG: duration: 5934.165 ms plan: Query Text: SELECT * FROM myschema.mytable pbh WHERE pbh.product_code = $1 AND pbh.cage_player_id = $