Re: [GENERAL] tgrm index for word_similarity

[Igal @ Lucee.org]

On 10/21/2017 5:01 AM, Arthur Zakirov wrote:

PostgreSQL doesn't use index scan with functions within WHERE clause. So
you always need to use operators instead. You can try <% operator and
pg_trgm.word_similarity_threshold variable:

=# SET pg_trgm.word_similarity_threshold TO 0.1;
=# SELECT name, popularity
FROM temp.items3_v
,(values ('some phrase'::text)) consts(input)
WHERE input <% name
ORDER BY 2, input <<-> name;


Thank you, your solution does show that the index is used when I do 
`explain analyze`, and makes the query finish in about 20ms so it's 
about 1.5 - 2 times faster than without the index, but that raises a few 
questions for me:


1) I thought that the whole idea behind indexes on expressions is that 
the index would be used in a WHERE clause?  See 
https://www.postgresql.org/docs/10/static/indexes-expressional.html - Am 
I missing something?


2) A query with `WHERE input <% name` utilizes the index, but a query 
without a WHERE clause at all does not?


3) What happens if I do not create an index at all?  Does the query that 
I run in 30 - 40ms, the one that does not utilize an index, creates all 
of the tri-grams on the fly each time that it runs?  Would it be 
possible for me to create a TABLE or a VIEW with the tri-grams so that 
there is no need to create them each time the query runs?


Thanks,

Igal Sapir
Lucee Core Developer
Lucee.org 

Re: [GENERAL] Re: Restoring tables with circular references dumped to separate files

[Melvin Davidson]

On Sat, Oct 21, 2017 at 4:48 PM, doganmeh  wrote:

> The list approach for partial restore is also useful, thank you.
>
> On another note, I used to take full backups (entire database), however
> switched to table by table scheme in order to make it more VCS friendly.
> Namely, so I only check into github the dumps of the tables that are
> updated
> only.
>
> So, from that perspective, is there a dump-restore scenario that is widely
> used, but is also VCS friendly? To my knowledge, pg_restore does not
> restore
> backups that are in "plain text" format, and compressed formats such as
> "tar" would not be github friendly.
>
>
>
> --
> Sent from: http://www.postgresql-archive.org/PostgreSQL-general-
> f1843780.html
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>


*>...is there a dump-restore scenario that is widely used, but is also VCS
friendly?*

*You might want to give consideration to pg_extractor.*

*https://github.com/omniti-labs/pg_extractor
*

*https://www.keithf4.com/pg_extractor/
*

-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

[GENERAL] Re: Restoring tables with circular references dumped to separate files

[doganmeh]

The list approach for partial restore is also useful, thank you. 

On another note, I used to take full backups (entire database), however
switched to table by table scheme in order to make it more VCS friendly.
Namely, so I only check into github the dumps of the tables that are updated
only. 
 
So, from that perspective, is there a dump-restore scenario that is widely
used, but is also VCS friendly? To my knowledge, pg_restore does not restore
backups that are in "plain text" format, and compressed formats such as
"tar" would not be github friendly. 



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Alternative to pgAdmin Postgres Manager that support pgagent (jobs)

[Adam Brusselback]

Happy to hear jpgAgent is working alright for you.  If you have any
questions with it feel free to ask me.

If you do want to help with pgAutomator, that sounds like something
you could start to learn on.  jpgAgent is pretty much feature complete
as far as my needs go, and no one has requested any additional
features, so i'd rather spend any new time on pgAutomator.

So one thing I know I need, is to figure out how to package a Postgres
extension, and get it onto pgxn.  The database portion of pgAutomator
is pretty complete at this point, so it'd be nice to learn how to
package it up even if I don't end up publishing it until I get a UI in
place.  I'll have to look, but i'm sure there are plenty of small
tasks that can be done with the agent itself as well.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Alternative to pgAdmin Postgres Manager that support pgagent (jobs)

[Juliano]

Thanks to everyone for your great answers

I have looked into pg_cron, jpgAgent and pgAutomator and enjoyed (j)pgAgent + 
pgAdmin so far

pg_cron looks great but rewrite my 346 jobs seems painful

Adam your tool is really nice and I would be glad to help on this project, btw 
I am not a developer but I would like to start to code as well

Regards
Juliano

>  Original Message 
> Subject: Re: [GENERAL] Alternative to pgAdmin Postgres Manager that support 
> pgagent (jobs)
> Local Time: 20 October 2017 4:14 AM
> UTC Time: 20 October 2017 03:14
> From: adambrusselb...@gmail.com
> To: pgsql-general@postgresql.org 
>
> I'm currently in the same boat that I wish there was something better
> for running jobs against Postgres than pgAgent.
> Using pgAdmin to manage my numerous jobs isn't the best experience
> i've ever had to say the least, but it does work. No other tool I
> have used will manage pgAgent jobs.
>
> I worked to alleviate some of my pain with pgAgent, by re-writing the
> entire agent portion: https://github.com/GoSimpleLLC/jpgAgent
> It uses the same schema as pgAgent, and you can manage it with pgAdmin
> just the same, it just has way more features, and has been extremely
> stable for me.
>
> One thing I have wanted to do for a while, is to write an entire
> alternative job scheduler / management tool separate from pgAdmin
> which will be dedicated to just that single purpose.
> I put in a good bit of work on that, but haven't had time to go back
> and get it to a point where I can actually release it:
> https://github.com/Tostino/pgAutomator
> The goal is to have a tool much closer to what is available for SQL
> Server, or Oracle compared to what we have today. The backend stuff
> is mostly working, I just need to find time to write a frontend to
> manage it.
>
> Anyways, sorry for a bit of rambling. My main point was that this is
> something that has been a personal pain point, and I haven't found
> anything I liked better up to this point in time.
> I'm slowly working on remedying that, but until the time my
> alternative is ready, i'm sticking with (j)pgAgent, and pgAdmin to
> manage it.
>
> Thanks,
> -Adam
>
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Weird performance difference

[israel]

On 2017-10-20 16:38, Tom Lane wrote:

Israel Brewster  writes:
Can you send "explain" (not explain analyze) for the production 
server?


Not for the full query - it only just completed, after 70 minutes or 
so, and I wasn't running under EXPLAIN ANALYZE. Running with a shorter 
date range of only 7 days, as you suggest below: 
https://explain.depesz.com/s/r80j 


First thing that jumps out from that is

Foreign Scan on oag_schedules (cost=100.00..128.60 rows=620 width=108) 
(actual time=3.576..477.524 rows=79,853 loops=1)


Being off by a factor of 100 at the scan level is never a good start 
for a
join plan.  Turn on use_remote_estimate (assuming these are 
postgres_fdw

tables).  Also try explicitly ANALYZE'ing the foreign tables.  I do not
believe auto-analyze will touch foreign tables ...


Thanks - the ANALYZE apparently did it. Running the FULL query (for the 
entire year) now returns in slightly better time than my test machine: 
https://explain.depesz.com/s/GtiM


Also, the query plan now looks similar. So now that it's working, I can 
move on to optimizing. It's already been suggested that I remove the 
cast to date (or index it), so I guess that's the first thing I'll try.




regards, tom lane



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Re: Restoring tables with circular references dumped to separate files

[Melvin Davidson]

On Sat, Oct 21, 2017 at 8:24 AM, doganmeh  wrote:

> Seems that would be easier and less error prone. Thanks,
>
>
>
> --
> Sent from: http://www.postgresql-archive.org/PostgreSQL-general-
> f1843780.html
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>


>I was dumping each table to a separate file so I could pick and choose
when restoring...

*It seems to me that instead on multiple single table dumps, you could take
advantage of restoring from a list-file.*


*Just *
*1. do a dump with custom format ( -F c)*

*EG: pg_dump -U postgres -F c -t table1 -t table2 -t table3 yourdb >
yourdb.dmp*


*2. use pg_restore -l to create a list-file*
*EG: pg_restore -l yourdb.dmp > yourdb.lis*

*3. edit yourdb.lis and comment out ( prefix with ; ) any line you don't
want*


*4. then use pg_restore with the edited list-file  *

*EG:  pg_restore -L yourdb.lis yourdb.dmp*

*See examples at the end of*

*https://www.postgresql.org/docs/9.6/static/app-pgrestore.html
*

-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

[GENERAL] Re: Restoring tables with circular references dumped to separate files

[doganmeh]

Seems that would be easier and less error prone. Thanks, 



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] tgrm index for word_similarity

[Arthur Zakirov]

On Thu, Oct 19, 2017 at 04:54:19PM -0700, Igal @ Lucee.org wrote:
> 
> My query at the moment is:
> 
>     SELECT name, popularity
>     FROM   temp.items3_v
>       ,(values ('some phrase'::text)) consts(input)
>     WHERE  true
>         and word_similarity(input, name) > 0.01  -- be lenient as some names
> are 75 characters long and we want to match even on a few characters of
> input
>     ORDER BY 2, input <<-> name
> 

PostgreSQL doesn't use index scan with functions within WHERE clause. So
you always need to use operators instead. You can try <% operator and
pg_trgm.word_similarity_threshold variable:

=# SET pg_trgm.word_similarity_threshold TO 0.1;
=# SELECT name, popularity
FROM temp.items3_v
,(values ('some phrase'::text)) consts(input)
WHERE input <% name
ORDER BY 2, input <<-> name;

-- 
Arthur Zakirov
Postgres Professional: http://www.postgrespro.com
Russian Postgres Company


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Restoring tables with circular references dumped to separate files

[Uwe]

I would alter the tables and remove the foreign key constraint, restore the 
data and add the constraint back. If the data is consistent, adding the 
foreign key should work without error.


On Friday, October 20, 2017 8:15:27 PM PDT doganmeh wrote:
> I was dumping each table to a separate file so I could pick and choose when
> restoring. However, seems this was not a great idea, since two of my tables
> happened to reference each other via FOREIGN KEYs, and I am not able to
> restore them. Is there a way to do this without manually merging the dump
> files? Thanks for guidance in advance.
> 
> 
> 
> --
> Sent from:
> http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html