Re: How to do phrase search?

[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 on my tsvector with the string: `Is it possible 
to toggle the visibility of an element`. I tried the following query:



If you want phrase search, use the phraseto_tsquery function, like

   phraseto_tsquery( 'Is it possible to toggle the visibility of an 
element')


or

   phraseto_tsquery( 'my_ts_config', 'Is it possible to toggle the 
visibility of an element')



​

|SELECT id, ts_rank(body, keywords) AS rank|

|FROM so2,|

|to_tsquery('Is & it & possible & to & toggle & the & visibility & of 
& an & element')|


|keywords|

|WHERE keywords @@ body ORDER BY rank DESC;|

​

This give the result in which the "element" string count is large in 
number, however there is a record which exactly matches the search 
string, that comes as third in my result. So logically it just checked 
in my query which word count occurs the most across my body column, 
which I don't want.


​

Can I say somehow to postgresql, that I want the pharse search rather 
than the text spilt up weight in the document. Is it possible to do?


Edit: I tried with `|ts_rank_cd\|` as well. Same result.

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

[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();
  now
---
 2020-07-10 15:45:20.875835+01
(1 row)

odc=# select localtimestamp;
  localtimestamp
---
 2020-07-10 15:45:33.28083
(1 row)

===
Createddate is loaded always with default value. its doesnt pick anything
from source DB wrt this column value.

As said out of 3k records , sometimes 50 to 100 records it shows as 2019.

Regards,
Deepika


On Fri, Jul 10, 2020 at 7:39 PM Adrian Klaver 
wrote:

> On 7/10/20 7:03 AM, Deepika S Gowda wrote:
> > Hi,
> >
> > On postgres 11.7 Master/Slave node, there is column named "createddate"
> > with datatype "timestamp without time zone" with default value as
> "now()";
> >
> > Column Name | Date Type | Default value
> > createddate |timestamp without time zone|Now()
> >
> >
> > Issue: From the java application , data is getting loaded into this
> > table where we expect column value should be today's date with
> > timestamp( "2020-07-10 10:56:43.21"). But, out of 3K records, 100
> > records are loaded as  "2019-07-10 10:56:43.21" (change in Year).
> >
> > What could be the issue? we tried changing the default value to
> > "localtimestamp".
>
> I would day the choices are:
>
> 1) A machine has it's clock set wrong.
>
> 2) The data is being loaded with a value for createdate that overrides
> the DEFAULT.
>
> >
> > Kindly help on this request
> >
> > Regardss,
> > Deepika
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

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

[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 zone: Europe/London (BST, +0100)
> 
> At DB level,
> odc=# select now();
>   now
> ---
>  2020-07-10 15:45:20.875835+01
> (1 row)
> 
> odc=# select localtimestamp;
>   localtimestamp
> ---
>  2020-07-10 15:45:33.28083
> (1 row)
> 
> ===
> Createddate is loaded always with default value. its doesnt pick anything 
> from source DB wrt this column value.
> 
> As said out of 3k records , sometimes 50 to 100 records it shows as 2019.
> 
> Regards,
> Deepika
> 
> 
>> On Fri, Jul 10, 2020 at 7:39 PM Adrian Klaver  
>> wrote:
>> On 7/10/20 7:03 AM, Deepika S Gowda wrote:
>> > Hi,
>> > 
>> > On postgres 11.7 Master/Slave node, there is column named "createddate" 
>> > with datatype "timestamp without time zone" with default value as "now()";
>> > 
>> > Column Name | Date Type | Default value
>> > createddate |timestamp without time zone|Now()
>> > 
>> > 
>> > Issue: From the java application , data is getting loaded into this 
>> > table where we expect column value should be today's date with 
>> > timestamp( "2020-07-10 10:56:43.21"). But, out of 3K records, 100 
>> > records are loaded as  "2019-07-10 10:56:43.21" (change in Year).
>> > 
>> > What could be the issue? we tried changing the default value to 
>> > "localtimestamp".
>> 
>> I would day the choices are:
>> 
>> 1) A machine has it's clock set wrong.
>> 
>> 2) The data is being loaded with a value for createdate that overrides 
>> the DEFAULT.
>> 
>> > 
You’ll have to show the code generating the records. And the insert. 
Your first post had times identical to the sub-second. We’re those artificial 
values? If so, can you show actual selected values which you believe arrived at 
approximately the same time but have both years?  Include the sql. 

Listen/Notify feedback

[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 use pub/sub, how do you deal with large messages, can I have
subscribers listen to replica nodes?

Thanks
-- 
--- Get your facts first, then you can distort them as you please.--

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

[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)

At DB level,
odc=# select now();
               now
---
  2020-07-10 15:45:20.875835+01
(1 row)

odc=# select localtimestamp;
       localtimestamp
---
  2020-07-10 15:45:33.28083
(1 row)

===
Createddate is loaded always with default value. its doesnt pick 
anything from source DB wrt this column value.


You are going to have to expand on the above. In particular what is the 
'source DB'? Does this mean the data is coming from another database? Or 
more generally where is the data coming from? Bottom line is I don't see 
DEFAULT now() or localtimestamp randomly reverting back a year. There is 
something else in this process that is setting the timestamp back.




As said out of 3k records , sometimes 50 to 100 records it shows as 2019.

Regards,
Deepika


On Fri, Jul 10, 2020 at 7:39 PM Adrian Klaver > wrote:


On 7/10/20 7:03 AM, Deepika S Gowda wrote:
 > Hi,
 >
 > On postgres 11.7 Master/Slave node, there is column named
"createddate"
 > with datatype "timestamp without time zone" with default value as
"now()";
 >
 > Column Name | Date Type                 | Default value
 > createddate |timestamp without time zone|Now()
 >
 >
 > Issue: From the java application , data is getting loaded into this
 > table where we expect column value should be today's date with
 > timestamp( "2020-07-10 10:56:43.21"). But, out of 3K records, 100
 > records are loaded as  "2019-07-10 10:56:43.21" (change in Year).
 >
 > What could be the issue? we tried changing the default value to
 > "localtimestamp".

I would day the choices are:

1) A machine has it's clock set wrong.

2) The data is being loaded with a value for createdate that overrides
the DEFAULT.

 >
 > Kindly help on this request
 >
 > Regardss,
 > Deepika


-- 
Adrian Klaver

adrian.kla...@aklaver.com 




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Request to help on Query improvement suggestion.

[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
> >   USING btree
> >   (md5(parental_path) COLLATE pg_catalog."default");
> >
> > CREATE INDEX cable_pair_parental_path_idx_fulltext
> >   ON SCHEMA.TABLE_NAME
> >   USING gist
> >   (parental_path COLLATE pg_catalog."default");
>
> > SELECT seq_no + 1 FROM SCHEMA.TABLE_NAME WHERE (parental_path LIKE
> '%,sheath--' || cable_seq_id || ',%' OR parental_path LIKE 'sheath--' ||
> cable_seq_id || ',%' OR parental_path LIKE '%,sheath--' ||
> > cable_seq_id OR parental_path = 'sheath--' || cable_seq_id) ORDER BY
> seq_no DESC LIMIT 1;
> >
> > Explain Plan:
> > =
> >
> > Limit  (cost=108111.60..108111.61 rows=1 width=4) (actual
> time=4597.605..4597.605 rows=0 loops=1)
> >  Output: ((seq_no + 1)), seq_no
> >  Buffers: shared hit=2967 read=69606 dirtied=1
> >  ->  Sort  (cost=108111.60..108113.09 rows=595 width=4) (actual
> time=4597.603..4597.603 rows=0 loops=1)
> >Output: ((seq_no + 1)), seq_no
> >Sort Key: TABLE_NAME.seq_no DESC
> >Sort Method: quicksort  Memory: 25kB
> >Buffers: shared hit=2967 read=69606 dirtied=1
> >->  Seq Scan on SCHEMA.TABLE_NAME  (cost=0.00..108108.63 rows=595
> width=4) (actual time=4597.595..4597.595 rows=0 loops=1)
> >  Output: (seq_no + 1), seq_no
> >  Filter: ((TABLE_NAME.parental_path ~~
> '%,sheath--64690,%'::text) OR (TABLE_NAME.parental_path ~~
> 'sheath--64690,%'::text) OR (TABLE_NAME.parental_path ~~
> '%,sheath--64690'::text) OR
> > (TABLE_NAME.parental_path = 'sheath--64690'::text))
> >  Rows Removed by Filter: 1930188
> >  Buffers: shared hit=2967 read=69606 dirtied=1
>
> An index on an expression can only be used if the expression is exactly
> the same as on one
> side of an operator in a WHERE condition.
>
> So your only chance with that query is to hope for a bitmap OR with an
> index on "parental path".
>
> Two things to try:
>
> 1)  CREATE INDEX ON table_name (parental_path text_pattern_ops);
>
> 2)  CREATE EXTENSION pg_trgm;
> CREATE INDEX ON table_name USING GIN (parental_path gin_trgm_ops);
>
> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com
>
>
>
>

Join optimization

[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-services.com/the-myth-of-nosql-vs-rdbms-joins-dont-scale/ | 
https://blog.dbi-services.com/the-myth-of-nosql-vs-rdbms-joins-dont-scale/ ] 
[2] [ 
https://dbfiddle.uk/?rdbms=oracle_18&fiddle=a5afeba2fdb27dec7533545ab0a6eb0e | 
https://dbfiddle.uk/?rdbms=oracle_18&fiddle=a5afeba2fdb27dec7533545ab0a6eb0e ] 
[3] [ 
https://dbfiddle.uk/?rdbms=postgres_13&fiddle=87c78b4e97fdbd87255efc2fc909ee62 
| 
https://dbfiddle.uk/?rdbms=postgres_13&fiddle=87c78b4e97fdbd87255efc2fc909ee62 
] 

Re: Listen/Notify feedback

[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 listener can query the table to catch up on any missed messages, or
messages that were mid-process during a crash.  This is trickier with more
than one listener.   This isn't a whole lot more efficient than just using
the table alone, but it saves you from having to poll so better response
times.

On Sat, Jul 11, 2020 at 8:58 AM Rita  wrote:

> 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 use pub/sub, how do you deal with large messages, can I have
> subscribers listen to replica nodes?
>
> Thanks
> --
> --- Get your facts first, then you can distort them as you please.--
>

Re: Join optimization

[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 PG can't do the same?
>

It does... did you see the “never executed” notice on the Postgres explain
output?

Regards,


-- 
   Fabrízio de Royes Mello Timbira - http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

Re: Join optimization

[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=21a98f499065ad4e2c35ff4bd1487e14 | 
https://dbfiddle.uk/?rdbms=oracle_18&fiddle=21a98f499065ad4e2c35ff4bd1487e14 ] 
. 

I don't understand oracle's execution plan very well, but it doesn't seem to be 
hitting the Users table... 


De: "Fabrízio de Royes Mello"  
Para: "luis.roberto"  
Cc: "pgsql-general"  
Enviadas: Sábado, 11 de julho de 2020 15:24:04 
Assunto: Re: Join optimization 


Em sáb, 11 de jul de 2020 às 14:20, < [ mailto:luis.robe...@siscobra.com.br | 
luis.robe...@siscobra.com.br ] > 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 PG can't do the same? 




It does... did you see the “never executed” notice on the Postgres explain 
output? 

Regards, 



BQ_BEGIN


BQ_END

-- 
Fabrízio de Royes Mello Timbira - [ http://www.timbira.com.br/ | 
http://www.timbira.com.br/ ] 
PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento