RE: Simple task with partitioning which I can't realize

De : Marc Millas Envoyé : mardi 1 mars 2022 19:00 À : Andrew Zakharov Cc : pgsql-performance@lists.postgresql.org Objet : Re: Simple task with partitioning which I can't realize Andrew, contrary to Oracle, in postgres you can add the indexes and/or the constraints which are meaningful t

Re: Advice needed: query performance deteriorates by 2000% within 1 minute

And I would absolutely crank up autovacuum and analyze settings. Turn up the cost limits, turn down the cost delays, decrease the scale factor. Whatever you need to do such that autovacuum runs often. No need to schedule a manual vacuum at all. Just don't wait until 20% of the table is dead before

Re: Advice needed: query performance deteriorates by 2000% within 1 minute

If you expect to have high cache hits and/or have ssd or similar fast storage, random page cost should be more like 1-2 rather than the default 4. When using jsonb, you'd normally have estimates based solely on the constants for the associated datatype (1/3 or 2/3 for a nullable boolean for instanc

Re: An I/O error occurred while sending to the backend (PG 13.4)

Em seg., 28 de fev. de 2022 às 13:50, l...@laurent-hasson.com < l...@laurent-hasson.com> escreveu: > > > >From: Ranier Vilela > >Sent: Thursday, February 24, 2022 08:46 > >To: Justin Pryzby > >Cc: l...@laurent-hasson.com; pgsql-performa...@postgresql.org > >Subject: Re: An I/O error occurred whi

Re: An I/O error occurred while sending to the backend (PG 13.4)

On Tue, Mar 01, 2022 at 04:28:31PM +, l...@laurent-hasson.com wrote: > Now, there is an additional component I think... Storage is on an array and I > am not getting a clear answer as to where it is 😊 Is it possible that > something is happening at the storage layer? Could that be reported as

RE: Simple task with partitioning which I can't realize

Yes, Marc – I understood you properly and totally. I was just saying about the hope that there is a trick to keep constraints on the base table level for my case. Thanks a bunch. Andrew. On Tue, Mar 01, 2022 at 9:00 PM Marc Millas wrote: Andrew, contrary to Oracle, in postgres y

Re: Simple task with partitioning which I can't realize

Andrew, contrary to Oracle, in postgres you can add the indexes and/or the constraints which are meaningful to you at partition level. I was not saying NOT to create keys, but I was saying to create them at partition level. Marc MILLAS Senior Architect +33607850334 www.mokadb.com On Tue, Mar

RE: Simple task with partitioning which I can't realize

Hi Marc – Since there is a DWH fed by ETL there no risks to have same gids in different region partitions. I considered simple partitioned table w/o any keys but I’d believed there is a solutions with keys that’s why I’m seeking the clue. Thanks. Andrew. From: Marc Millas Sent: Tuesday,

Re: Simple task with partitioning which I can't realize

On Tue, Mar 1, 2022 at 9:37 AM Andrew Zakharov wrote: > David, - yes, creation composite foreign/primary key is not a problem. But > the main question is what method should I use for partitioning by composite > key gid, region_code? > The convention here is to inline or bottom-post responses. Y

RE: Simple task with partitioning which I can't realize

David, - yes, creation composite foreign/primary key is not a problem. But the main question is what method should I use for partitioning by composite key gid, region_code? The partition method itself created not only for faster data access but for better administration. The administration like

Re: Simple task with partitioning which I can't realize

Hi, is there any chance (risk ?) that a given gid be present in more than one region ? if not (or if you implement it via a dedicated, non partition table), you may create a simple table partitioned by region, and create unique indexes for each partition. this is NOT equivalent to a unique constr

RE: An I/O error occurred while sending to the backend (PG 13.4)

> -Original Message- > From: Justin Pryzby > Sent: Monday, February 28, 2022 17:05 > To: l...@laurent-hasson.com > Cc: pgsql-performa...@postgresql.org > Subject: Re: An I/O error occurred while sending to the backend (PG 13.4) > > On Mon, Feb 28, 2022 at

Re: Simple task with partitioning which I can't realize

On Tue, Mar 1, 2022 at 8:37 AM Andrew Zakharov wrote: > create table region_hierarchy( > > gid uuid not null default uuid_generate_v1mc(), > > parent_gid uuid null, > > region_code int2, > > > > I’ve carefully looked thru docs/faqs/google/communities and found out that > I must include “g

Re: Never Ending query in PostgreSQL

On 3/1/22 16:01, Kumar, Mukesh wrote: > Hi Tomas , > > Thanks for replying , We have identified a Join condition which is > creating a problem for that query. > > Accept my apologies for pasting the plan twice. I am attaching the > query again in this mail > Queries without explain (or even bet

Simple task with partitioning which I can't realize

Hello all - I have a task which is simple at the first look. I have a table which contains hierarchy of address objects starting with macro region end ends with particular buildings. You can imagine how big is it. Here is short sample of table declaration: create table region_hierarchy( g

RE: Never Ending query in PostgreSQL

Hi Tomas , Thanks for replying , We have identified a Join condition which is creating a problem for that query. Accept my apologies for pasting the plan twice. I am attaching the query again in this mail We have found that by evicting the View paymenttransdetails_view from the attached quer

Re: Never Ending query in PostgreSQL

On 2/27/22 18:20, Jeff Janes wrote: > > On Sun, Feb 27, 2022 at 7:09 AM Kumar, Mukesh > wrote: > > Hi Team,  > > Can you please help in tunning the attached query as , i am trying > to run this query and it runs for several hours and it did not give

RLS not using index scan but seq scan when condition gets a bit complicated

Hi Postgres community, We are experiencing some performance issues when RLS is enabled for large tables. With simplified example: We have a table: CREATE TABLE emp.employees ( employee_id INTEGER PRIMARY KEY, -- companies table are defined in a different schema, not accessible to emp service