Re: [GENERAL] Find similar records (compare tsvectors)

[Patrick Dung]

Thanks.
smlar is fast and quite good.I need find tuning on the search result. 

 On Saturday, March 7, 2015 12:07 AM, Oleg Bartunov  
wrote:
   

 On Fri, Mar 6, 2015 at 5:05 PM, Patrick Dung  wrote:
> Resend.
>
> How to quickly compare the similarity of two tsvector?
>

check http://www.sai.msu.su/~megera/postgres/talks/pgcon-2012.pdf


>
> On Monday, March 2, 2015 11:01 PM, Patrick Dung 
> wrote:
>
>
> Hello,
>
> I had a database with articles or attachment stored in bytea format.
> I also had a trigger: it insert/update the tsv column when a record is
> added/updated.
> The tsv column had a GIN index.
> With this setting, I can do very fast keyword search on the tsv.
>
> Suppose I had a specific record (id=10).
> How to list similar records based on ranking?
> In that case, I had to compare a tsvector with another tsvector.
>
> I had this SQL which make the original tsv as a text and then to tsquery,
> Then I can compare a tsv and a tsquery.
> SELECT ts_rank(i.tsv, replace(strip(original.tsv)::text, ' ', '|')::tsquery)
> as similarity, i.company, i.industry, i.post_timestamp, i.id FROM items i,
> (SELECT tsv, id FROM items WHERE id=10) AS original WHERE i.id !=
> original.id ORDER BY similarity;
>
> items table:
> id bigint
> company varchar
> industry varchar
> description varchar
> post_timestamp timestamp
> attachment bytea
> tsv tsvector
>
> The problem is that this is very slow.
> Any comment?
>
> Thank and regards,
> Patrick
>
>


-- 
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] How to get plpython2 in /lib?

[dpopova]

Just to let everybody know: happy end! Everything installed and MADlib is
successfully registered!
Details:
1. Had to use PostgresApp for this installation. No other method worked
for my particular case.
2. Had to manually remove several directories and files from my machine,
because uninstaller is doing only so much.  Was quite a job to find them
all! These were 'leftovers' from my previous unsuccessful installations
using EnterpriseDB installers (for Postgres and add-ons).

HUGE and HEART-FELT thank you for Adrian Klaver and Jacob Egger, for support!

Diana

> On 03/07/2015 10:09 AM, dpop...@uvic.ca wrote:
>> Thank you so much, Adrian! You are most helpful. :-)
>>
>> I was up at 2 a.m. worrying about my project (just 2 weeks to complete),
>> and by 10 a.m., after installing/uninstalling several times, started
>> questioning everything.
>> I needed a calm voice like yours to tell me that I might be weird, but
>> not
>> completely insane.
>
> Noticed the UVIC email. FYI, there will be a Postgres mini-conference at
> LFNW(http://linuxfestnorthwest.org) April 25th & 26th in Bellingham if
> you want to talk to some Postgres experts even after the project is done.
>
>>
>> Thank you,
>>
>> Diana
>>
>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



-- 
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] How to get plpython2 in /lib?

[Adrian Klaver]

On 03/07/2015 10:09 AM, dpop...@uvic.ca wrote:

Thank you so much, Adrian! You are most helpful. :-)

I was up at 2 a.m. worrying about my project (just 2 weeks to complete),
and by 10 a.m., after installing/uninstalling several times, started
questioning everything.
I needed a calm voice like yours to tell me that I might be weird, but not
completely insane.


Noticed the UVIC email. FYI, there will be a Postgres mini-conference at 
LFNW(http://linuxfestnorthwest.org) April 25th & 26th in Bellingham if 
you want to talk to some Postgres experts even after the project is done.




Thank you,

Diana





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


--
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] How to get plpython2 in /lib?

[dpopova]

Thank you so much, Adrian! You are most helpful. :-)

I was up at 2 a.m. worrying about my project (just 2 weeks to complete),
and by 10 a.m., after installing/uninstalling several times, started
questioning everything.
I needed a calm voice like yours to tell me that I might be weird, but not
completely insane.

Thank you,

Diana

> On 03/07/2015 09:52 AM, dpop...@uvic.ca wrote:
>> Adrian,
>>
>> I again wiped out everything postgres from my machine (Mac 10.10.2,
>> Yosemite). Now, if I want to start installing postgres 9.3 anew, what is
>> the best way to go for it?
>
> Aah, you anticipated the post I just sent.
>
>> I found PostgresApp, that promises the easiest way to start using
>> postgres. Does it mean that if I install this PostgresApp for 9.3 (click
>> on the link), it will install and run postgres 9.3?
>
> Yes:
>
> http://postgresapp.com/documentation/install.html
>
> "To install Postgres.app, just drag it to your Applications folder and
> double click.
>
> On first launch, Postgres will initialise a new database cluster and
> create a database for your username. A few moments after launching, you
> should be able to click on "Open psql" to connect to the database.
>
> If you'd like to use the command line tools delivered with Postgres.app,
> see the section on Command Line Tools."
>
>> Or I must first install postgres 9.3 by other means and only after that
>> I
>> can use PostgresApp for an easier access to postgres?
>
> No.
>>
>> Confused,
>>
>> Diana
>>
>>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>




-- 
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] How to get plpython2 in /lib?

[Adrian Klaver]

On 03/07/2015 09:52 AM, dpop...@uvic.ca wrote:

Adrian,

I again wiped out everything postgres from my machine (Mac 10.10.2,
Yosemite). Now, if I want to start installing postgres 9.3 anew, what is
the best way to go for it?


Aah, you anticipated the post I just sent.


I found PostgresApp, that promises the easiest way to start using
postgres. Does it mean that if I install this PostgresApp for 9.3 (click
on the link), it will install and run postgres 9.3?


Yes:

http://postgresapp.com/documentation/install.html

"To install Postgres.app, just drag it to your Applications folder and 
double click.


On first launch, Postgres will initialise a new database cluster and 
create a database for your username. A few moments after launching, you 
should be able to click on "Open psql" to connect to the database.


If you'd like to use the command line tools delivered with Postgres.app, 
see the section on Command Line Tools."



Or I must first install postgres 9.3 by other means and only after that I
can use PostgresApp for an easier access to postgres?


No.


Confused,

Diana





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


--
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] How to get plpython2 in /lib?

[Adrian Klaver]

On 03/06/2015 09:43 PM, dpop...@uvic.ca wrote:

Thank you, Adrian.
Maybe, you will be able to find something useful.
I spent more hours trying to
- install PostgreSQL 9.3 (successful, but with python3)
- install PostgresApp 9.3 (unsuccessful, could not start postgres,  but no
matter - no python2 there anyway), then uninstall it.
- install Python 2.7 (successful)

MADlib still cannot be registered - same failure, with no plpython2 found
in /lib:

madpack.py : INFO : Testing PL/Python environment...
madpack.py : INFO : > Creating language PL/Python...
madpack.py : ERROR : SQL command failed:
SQL: CREATE LANGUAGE plpythonu;
ERROR:  could not access file "$libdir/plpython2": No such file or directory

madpack.py : ERROR : Cannot create language plpythonu. Stopping
installation...
madpack.py : ERROR : MADlib installation failed.

Sad,  :-(



Still not sure why EDB is forcing Python 3 use and not offering option 
to use Python 2?


Might be worth posting in their forum below to see if there is a 
solution that I have missed:


http://forums.enterprisedb.com/forums/show/9.page

According to this:

https://github.com/PostgresApp/PostgresApp/issues/150

Postgres.app 9.3 should have plpythonu in it.

If it where me the first thing I would do is remove all the Postgres 
installs you have done, either EDB or postgres.app. I suspect that some 
of the problems you are seeing is multiple instances of Postgres trying 
to run at the same time. Then I would try the postgres.app version 
9.3.5.2. If the above does not work then see below.


There is the option of building from source, though that requires you 
have the build tools installed. I have built from source on Linux* and 
have installed Postgres using the installer on Windows, but I have never 
installed Postgres on OS X so I am probably not the best person to guide 
you through this. Hopefully someone with more experience can step in and 
offer further assistance.




Diana





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


--
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] How to get plpython2 in /lib?

[dpopova]

Adrian,

I again wiped out everything postgres from my machine (Mac 10.10.2,
Yosemite). Now, if I want to start installing postgres 9.3 anew, what is
the best way to go for it?
I found PostgresApp, that promises the easiest way to start using
postgres. Does it mean that if I install this PostgresApp for 9.3 (click
on the link), it will install and run postgres 9.3?
Or I must first install postgres 9.3 by other means and only after that I
can use PostgresApp for an easier access to postgres?

Confused,

Diana

> Thank you, Adrian.
> Maybe, you will be able to find something useful.
> I spent more hours trying to
> - install PostgreSQL 9.3 (successful, but with python3)
> - install PostgresApp 9.3 (unsuccessful, could not start postgres,  but no
> matter - no python2 there anyway), then uninstall it.
> - install Python 2.7 (successful)
>
> MADlib still cannot be registered - same failure, with no plpython2 found
> in /lib:
>
> madpack.py : INFO : Testing PL/Python environment...
> madpack.py : INFO : > Creating language PL/Python...
> madpack.py : ERROR : SQL command failed:
> SQL: CREATE LANGUAGE plpythonu;
> ERROR:  could not access file "$libdir/plpython2": No such file or
> directory
>
> madpack.py : ERROR : Cannot create language plpythonu. Stopping
> installation...
> madpack.py : ERROR : MADlib installation failed.
>
> Sad,  :-(
>
> Diana
>
>> On 03/06/2015 05:41 PM, dpop...@uvic.ca wrote:
>>> Postgres installed with Installer, from EnterpriseDB.
>>> Error received while trying to register MADlib 1.7.0.
>>
>> I seem to remember the EDB builds against Python 3 not 2 now, which
>> would explain the error below if MADlib is looking for Python 2. I
>> cannot remember if there is a work around, will dig into further.
>>
>>>
>>> Looked at PostgresApp, but it is a package for PostgreSQL 9.4. Cannot
>>> have
>>> this version of Postgres, because it is not supported by MADlib. Tried,
>>> does not work. Had to uninstall, + manual deletion of a number of
>>> 'leftovers'. :-(
>>
>> According to here:
>>
>> https://github.com/madlib/madlib/wiki/Installation-Guide
>>
>> it should work with 9.2+.
>>
>>
>> There is 9.3 version of PostgresApp available here:
>>
>> https://github.com/PostgresApp/PostgresApp/releases
>>
>>>
>>> Need MADlib for research. Please, help.
>>>
>>> Diana
>>>
>>>
 On 03/06/2015 12:44 PM, dpop...@uvic.ca wrote:
> Mac 10.10.2 (Yosemite)
> PostgreSQL 9.3
>
> error message:
> ERROR : SQL command failed:   SQL: CREATE LANGUAGE plpythonu;
> ERROR:  could not access file "$libdir/plpython2": No such file or
> directory
>
> Searched Web, nothing useful found...
>
> Any advice will be greatly appreciated.

 How was Postgres installed?

 What where you doing to get the error?
 Doing a pg_restore from another database?
 Running CREATE LANGUAGE/EXTENSION directly?


>
> Diana
>
>
>


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

>>>
>>>
>>>
>>>
>>
>>
>> --
>> Adrian Klaver
>> adrian.kla...@aklaver.com
>>
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>




-- 
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] autovacuum worker running amok - and me too ;)

[wambacher]

Hi, 

some final results:

I monitored the vaccum process and logged some data using one big table and
doing analyze/vaccum by hand. Table has two btree-indexes and one gin.
maintenance_work_mem was 1GB.

the analyze job used abot 1.2 GB virt mem during the whole task, no problems
at all.

The vacuum josb started with 3.3 GB and after processing the two "simple"
indexes it used up to 5.5 GB of Vmem going down to 2.3 GB for the final
work.

 

This lead to out of memory problems during the last days. The vacuum of the
first table (planet_osm_ways)  used *upto 12 GB* until the OOM-Killer killed
him.

Sorry, but *why do analyze and vacuum ignore maintenance_work_mem?* I have
no control about memory usage and ran in big trouble. Now i added 50GB swap
to my 24GB system and have to cross my fingers.

regards
walter
  



--
View this message in context: 
http://postgresql.nabble.com/autovacuum-worker-running-amok-and-me-too-tp5840299p5840914.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


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