Re: Join optimization

2020-07-11 Thread luis . roberto
I'm sorry for the bad example. Here is another, with some data on PG: [ https://dbfiddle.uk/?rdbms=postgres_13&fiddle=ccfd1c4fa291e74a6db9db1772e2b5ac | https://dbfiddle.uk/?rdbms=postgres_13&fiddle=ccfd1c4fa291e74a6db9db1772e2b5ac ] and Oracle: [ https://dbfiddle.uk/?rdbms=oracle_18&fiddle=

Re: Join optimization

2020-07-11 Thread Fabrízio de Royes Mello
Em sáb, 11 de jul de 2020 às 14:20, escreveu: > Hi! > > Recently on a blogpost [1] I saw that Oracle was able to "optimize" a join > strategy by completely eliminating access to a table. > > Heres the execution on Oracle 18c [2] and PostgreSQL 13 (beta) [3]. > > Is there a fundamental reason why

Re: Listen/Notify feedback

2020-07-11 Thread Brian Dunavant
One aspect is if there is no one listening when a notify happens, the message is lost (e.g. no durability). If this is important to you, it can be addressed by writing the messages to a table as well when you NOTIFY, and the listener deletes messages after they are processed. On connection the l

Join optimization

2020-07-11 Thread luis . roberto
Hi! Recently on a blogpost [1] I saw that Oracle was able to "optimize" a join strategy by completely eliminating access to a table. Heres the execution on Oracle 18c [2] and PostgreSQL 13 (beta) [3]. Is there a fundamental reason why PG can't do the same? Thanks! [1] [ https://blog.dbi-

Re: Request to help on Query improvement suggestion.

2020-07-11 Thread Marlene Villanueva
On Sun, May 24, 2020, 11:48 PM Laurenz Albe wrote: > On Fri, 2020-05-22 at 16:15 +0530, devchef2020 d wrote: > > PostgreSQL : 9.5.15 > > > Created Indexes on column parental_path: > > = > > > > CREATE INDEX cable_pair_parental_path_idx > > ON SCHEMA.TABLE_NAME >

Re: Issue with timestamp without time zone datatype with default value as now()/localtimestamp

2020-07-11 Thread Adrian Klaver
On 7/11/20 2:17 AM, Deepika S Gowda wrote: Hi Adrian, System timezone. [postgres@pvodcdbst0001uk ~]$ timedatectl       Local time: Fri 2020-07-10 15:44:37 BST   Universal time: Fri 2020-07-10 14:44:37 UTC         RTC time: Fri 2020-07-10 14:44:37        Time zone: Europe/London (BST, +0100)

Listen/Notify feedback

2020-07-11 Thread Rita
I am investigating various pub/sub tools such as ActiveMQ, Rabbit, Redis, etc.I came across Postgresql Listen/Notify and was easily able to write code to listen to messages. For the people who have been using this for a while: what are its downsides, things to consider when writing good code that u

Re: Issue with timestamp without time zone datatype with default value as now()/localtimestamp

2020-07-11 Thread Rob Sargent
> On Jul 11, 2020, at 3:17 AM, Deepika S Gowda wrote: > >  > Hi Adrian, > > System timezone. > [postgres@pvodcdbst0001uk ~]$ timedatectl > Local time: Fri 2020-07-10 15:44:37 BST > Universal time: Fri 2020-07-10 14:44:37 UTC > RTC time: Fri 2020-07-10 14:44:37 >Time zo

Re: Issue with timestamp without time zone datatype with default value as now()/localtimestamp

2020-07-11 Thread Deepika S Gowda
Hi Adrian, System timezone. [postgres@pvodcdbst0001uk ~]$ timedatectl Local time: Fri 2020-07-10 15:44:37 BST Universal time: Fri 2020-07-10 14:44:37 UTC RTC time: Fri 2020-07-10 14:44:37 Time zone: Europe/London (BST, +0100) At DB level, odc=# select now(); n

Re: How to do phrase search?

2020-07-11 Thread Ivan E. Panchenko
Hello, On 10.07.2020 21:22, Anto Aravinth wrote: Hello, I have the following table: so2, which has following column details: ​ |id, title, posts, body (tsvector). | And I created the index on the following: |"so2_pkey" PRIMARY KEY, btree (id)| |"body" gin (body)| ​ And I wanted to query