Re: Query optimization

> > 3) Output of EXPLAIN ANALYZE of query. > > Result (cost=2.80..2.83 rows=1 width=1) (actual time=0.030..0.030 rows=1 > loops=1) >InitPlan 1 (returns $0) > -> Index Only Scan using idx_cachekeys on cachekeys (cost=0.55..2.80 > rows=1 width=0) (actual time=0.029..0.029 rows=1 lo

Re: Query optimization

> On Mar 13, 2025, at 8:39 PM, Durgamahesh Manne > wrote: > > > > On Fri, 14 Mar, 2025, 08:04 Rob Sargent, > wrote: > > Hi > > Gave you plan with out limit . Stats up to date insert .on conflict do > nothing runs some times > > Regards > Durga Mahesh

Re: Query optimization

On Thu, Mar 13, 2025 at 11:48 PM Durgamahesh Manne < maheshpostgr...@gmail.com> wrote: > On Fri, 14 Mar, 2025, 09:11 Ron Johnson, wrote: > >> On Thu, Mar 13, 2025 at 11:25 PM Durgamahesh Manne < >> maheshpostgr...@gmail.com> wrote: >> >>> On Fri, Mar 14, 2025 at 8:19 AM Ron Johnson >>> wrote: >>

Re: Query optimization

On Thursday, March 13, 2025, Durgamahesh Manne wrote: > > > > > >

Re: Query optimization

On Thu, Mar 13, 2025 at 11:25 PM Durgamahesh Manne < maheshpostgr...@gmail.com> wrote: > On Fri, Mar 14, 2025 at 8:19 AM Ron Johnson > wrote: > >> On Thu, Mar 13, 2025 at 10:16 PM Durgamahesh Manne < >> maheshpostgr...@gmail.com> wrote: >> [snip] >> >>> Hi Adrian Klaver >>> >>> 1) Postgres versio

Re: Query optimization

On Fri, Mar 14, 2025 at 8:19 AM Ron Johnson wrote: > On Thu, Mar 13, 2025 at 10:16 PM Durgamahesh Manne < > maheshpostgr...@gmail.com> wrote: > [snip] > >> Hi Adrian Klaver >> >> 1) Postgres version. >> select version(); >> version >> >> --

Re: Query optimization

On Thu, Mar 13, 2025 at 10:16 PM Durgamahesh Manne < maheshpostgr...@gmail.com> wrote: [snip] > Hi Adrian Klaver > > 1) Postgres version. > select version(); > version > > -

Re: Query optimization

On Fri, 14 Mar, 2025, 08:04 Rob Sargent, wrote: > > > > 3) Output of EXPLAIN ANALYZE of query. > > Result (cost=2.80..2.83 rows=1 width=1) (actual time=0.030..0.030 rows=1 > loops=1) >InitPlan 1 (returns $0) > -> Index Only Scan using idx_cachekeys on cachekeys > (cost=0.55..2.80 row

Re: Query optimization

On Fri, Mar 14, 2025 at 12:47 AM Adrian Klaver wrote: > On 3/13/25 12:12, Durgamahesh Manne wrote: > > Hi Team > > > > This query takes more time than usual for execution > > Define usual. > > > > How to optimize it in best possible way > > Can't be answered without, to start: > > 1) Postgres ver

Re: Moving from Linux to Linux?

Hi Ron, > On 12 Mar 2025, at 17:59, Ron Johnson wrote: > > Developers making DDL changes on production databases? Of course not. But I can't block developer databases. That'd make a few hundred developers happy. > Or are there prod and dev databases on the same instance? If so, then know >

Re: psql and regex not like

On Thu, Mar 6, 2025 at 10:38 AM Ron Johnson wrote: > psql -Xc "select datname from pg_database WHERE datname \!~ > 'template|postgres' ORDER BY datname;" > Remove the space: psql -Xc "select datname from pg_database WHERE datname!~ 'template|postgres' ORDER BY datname" I'm not really sure why

Re: hide data from admins

On Tue, Mar 11, 2025 at 9:48 PM Siraj G wrote: > What are the features available in Postgresql to hide PII (personal > identifiable information) from the Admin team? > Can you explain your threat model here, and who exactly the "Admin team" is and what access they have? As a general rule of thum

Re: psql and regex not like

Another dirty hack: MAGIC=\! psql -Xc "select datname from pg_database WHERE datname $MAGIC~ 'template|postgres' ORDER BY datname;" Em qui., 6 de mar. de 2025 às 10:38, Ron Johnson escreveu: > This statement runs great from the psql prompt. Does exactly what I want. > select datname from pg_da

Re: Query optimization

On Fri, 2025-03-14 at 00:42 +0530, Durgamahesh Manne wrote: > This query takes more time than usual for execution  > > How to optimize it in best possible way > > Columns used in this query >> composite index eventhough not running > optimally  > SELECT EXISTS (SELECT Key FROM CACHEKEYS WHERE Ca

Re: Query optimization

On 3/13/25 12:12, Durgamahesh Manne wrote: Hi Team This query takes more time than usual for execution Define usual. How to optimize it in best possible way Can't be answered without, to start: 1) Postgres version. 2) Complete(including indexes) table schema. 3) Output of EXPLAIN ANALY

Query optimization

Hi Team This query takes more time than usual for execution How to optimize it in best possible way Columns used in this query >> composite index eventhough not running optimally SELECT EXISTS (SELECT Key FROM CACHEKEYS WHERE CacheType = $1 AND TrsId = $2 AND BrandId = $3 AND SportId = $4 AND

Re: hide data from admins

Le Tue, 11 Mar 2025 22:03:50 -0400, Ron Johnson a écrit : > On Tue, Mar 11, 2025 at 9:48 PM Siraj G wrote: > > > Hello Experts! > > > > What are the features available in Postgresql to hide PII (personal > > identifiable information) from the Admin team? Like in Oracle we have data > > vault an

Re: Duplicate Key Values

On 3/13/25 09:25, mark bradley wrote: > I'm assuming that by 'simple version' you mean no inheritance. Inheritance was not specified by me in either case.  By simple, I mean Unless someone else is working on this code, it was done by you. Pretty sure it had to do with from this message: h

Re: Duplicate Key Values

> I'm assuming that by 'simple version' you mean no inheritance. Anyway, inheritance can be undone via ALTER TABLE dataset NO INHERIT node; Now, there are no dups and hopefully it will stay that way. Best regards, Mark Brady amazon.com/author/markjbrady

Re: Duplicate Key Values

> I'm assuming that by 'simple version' you mean no inheritance. Inheritance was not specified by me in either case. By simple, I mean that I used fewer columns in the test version, like so: [cid:c8b9cd7d-1fe4-496a-be63-cbb8f658a785] [cid:b4c79bd5-7eac-4dbf-bf79-750adc941b74] Where node_id i

Re: Duplicate Key Values

On 3/13/25 08:56, mark bradley wrote: >Postgresql does not assume / default to inheritance.  In text-mode clients where you type >in "raw" SQL, you have to explicitly add an explicit "INHERITS " clause to the >"CREATE TABLE foo" statement. >Are you creating the tables via PgAdmin point-and

Re: Duplicate Key Values

On 3/13/25 07:56, mark bradley wrote: >Mark, to illustrate: So, I think the crux of the problem is that Postgres assumes that inheritance is declared when it is not. Follow up. From pgAdmin end: https://www.pgadmin.org/docs/pgadmin4/9.1/table_dialog.html "Use the drop-down listbox next t

Re: Duplicate Key Values

On 3/13/25 07:56, mark bradley wrote: >Mark, to illustrate: >create table node (node_id integer primary key, fld1 varchar); >create table node_1 (node_id integer primary key, node_1_fld boolean) >inherits ( node); >NOTICE:  merging column "node_id" with inherited definition >insert into n

Re: Moving from Linux to Linux?

On 3/13/25 06:10, Paul Foerster wrote: The other option, which may be equally untenable, is to upgrade in- place to pg17 and convert everything to use the new built-in collation provider. That ought to be portable across different versions of Linux. Is C.UTF8 really the same as en_US.UTF8? I a

Re: Finding execution time for a query

On Thu, 2025-03-13 at 15:21 +0530, Prasanna Thirugnanasambandam wrote: > How do I find the execution time of a query? My Stackexchange answer didn't satisfy? https://dba.stackexchange.com/a/345698/176905 Yours, Laurenz Albe

Re: Moving from Linux to Linux?

> On Mar 12, 2025, at 11:01, Paul Foerster wrote: > DDL during logical replication unfortunately is a show-stopper. You *can* apply DDL while logical replication is going on, as long as you do so in a disciplined way. This generally means applying it to the subscriber before you apply it to

Re: Moving from Linux to Linux?

> On Mar 13, 2025, at 10:10, Paul Foerster wrote: > > Is C.UTF8 really the same as en_US.UTF8? No. C.UTF8 sorts on Unicode code points, which will be (somewhat) different from en_US.UTF8. If you want a collation that is "good enough" across multiple languages, the ICU collation und-x-icu

Re: Moving from Linux to Linux?

Hi Joe, > On 13 Mar 2025, at 03:55, Joe Conway wrote: > > If you are desperate you could start with > https://github.com/awslabs/compat-collation-for-glibc and create a new branch > for your current version of SLES/glibc and deploy the resulting rpm to RHEL. > At least in theory. FWIW I was a

Finding execution time for a query

Hello all, How do I find the execution time of a query? I tried these methods: I used pgbench - pgbench -n -t 1 -f ./query.sql and got: latency average = 9.787 ms <- should i take this as the execution time? initial connection time = 12.481 ms tps = 102.176356 (without initial conn

Re: Moving from Linux to Linux?

Hi Adrian, > On 12 Mar 2025, at 22:37, Adrian Klaver wrote: > > What version of RH are you migrating to? That'd be currently Red Hat 9 with glibc 2.34. Cheers, Paul