Re: [GENERAL] grep -f keyword data query

Hiroyuki Sato wrote: > Hello. > > I would like to create the query like the following. > It work well, but extreme slow. > Is it possible to improve this query like the command ``grep -f keyword > data``? > > What kind of Index should I create on url_lists table? can you

Re: [GENERAL] Recurring and non recurring events.

> On 26 Dec 2015, at 13:03, Kevin Waterson wrote: > > Thanks, as I am new to postgres, I was unaware of this function. Actually, the article you referenced makes use of generate_series as well (at INSERT INTO events), but then for some reason decides to create a

[GENERAL] grep -f keyword data query

Hello. I would like to create the query like the following. It work well, but extreme slow. Is it possible to improve this query like the command ``grep -f keyword data``? What kind of Index should I create on url_lists table? Detail https://gist.github.com/hiroyuki-sato/574b8ea5d9396e455d60

Re: [GENERAL] grep -f keyword data query

Hiroyuki Sato writes: > I would like to create the query like the following. > It work well, but extreme slow. > ... > Explain output. > Nested Loop (cost=0.45..1570856063.28 rows=5712200 width=57) >-> Index Scan using ix_name_keywords on keywords k

Re: [GENERAL] Calling function (table_name, schema_name) within event trigger

On 2015-12-27 13:19, Tom Lane wrote: Andreas Kretschmer writes: The problem is, that tg_table_name isn't declared within a event trigger. TG_TAG is defined, it contains the command, for instance CREATE TABLE. Yeah. According to

Re: [GENERAL] grep -f keyword data query

> Tom Lane hat am 27. Dezember 2015 um 19:11 geschrieben: > > > Hiroyuki Sato writes: > > I would like to create the query like the following. > > It work well, but extreme slow. > > ... > > Explain output. > > > Nested Loop

Re: [GENERAL] Calling function (table_name, schema_name) within event trigger

> Melvin Davidson hat am 27. Dezember 2015 um 19:55 > geschrieben: > > > It's kind of difficult to figure out what is going on. Apparently, the > function that is called "store.add_history_master()" thinks tg_table_name > is a COLUMN in a table, as evidenced by > "ERROR:

Re: [GENERAL] Calling function (table_name, schema_name) within event trigger

> 9.4 did not offer very complete facilities for finding out what the > DDL command had done; 9.5 will provide more info.) > > regards, tom lane Really? http://www.postgresql.org/docs/9.5/static/plpgsql-trigger.html still contains only TG_EVENT and TG_TAG for Triggers on

Re: [GENERAL] grep -f keyword data query

Andreas Kretschmer writes: >> Tom Lane hat am 27. Dezember 2015 um 19:11 geschrieben: >> What in the world is this @~ operator? And what sort of index are >> you using now, that can accept it? Are the rowcount estimates in >> the EXPLAIN output

[GENERAL] Calling function (table_name, schema_name) within event trigger

What is the correct syntax for calling a function from within an event trigger, passing in the table name and schema name as parameters to the function? The goal is to capture DDL changes to tables for the purpose of turning on (or off) auditing for production tables. The history_master

Re: [GENERAL] Calling function (table_name, schema_name) within event trigger

On 12/27/2015 12:14 PM, Andreas Kretschmer wrote: 9.4 did not offer very complete facilities for finding out what the DDL command had done; 9.5 will provide more info.) regards, tom lane Really? http://www.postgresql.org/docs/9.5/static/plpgsql-trigger.html still

Re: [GENERAL] Calling function (table_name, schema_name) within event trigger

Andreas Kretschmer writes: > The problem is, that tg_table_name isn't declared within a event trigger. > TG_TAG is defined, it contains the command, for instance CREATE TABLE. Yeah. According to

Re: [GENERAL] Calling function (table_name, schema_name) within event trigger

It's kind of difficult to figure out what is going on. Apparently, the function that is called "store.add_history_master()" thinks tg_table_name is a COLUMN in a table, as evidenced by "ERROR: column "tg_table_name" does not exist" Offhand, you probably want to assign TG_TABLE_NAME to a var and

Re: [GENERAL] efficient math vector operations on arrays

Hi, Don't know if it's exactly what you're looking for but the MADLib package has utility function for matrix and vector operations. see: http://doc.madlib.net/latest/group__grp__array.html Regards, - Jony On Fri, Dec 25, 2015 at 9:58 PM, Jim Nasby wrote: > On

[GENERAL] Options for complex materialized views sharing most of the same logic?

Hi guys, hope everyone's well. I'm in a situation that we find ourselves in a lot, and I'm wondering if there's an easier option. I have one view which totals about 60 columns per day. Each day has a "days ago" column like so: row_number() over (order by date desc) as days_back Reason being is

Re: [GENERAL] grep -f keyword data query

Hiroyuki Sato writes: > Sorry, I re-created my questions. I was mis-pasted query log on previous > question. > (@~ operator is PGroonga extension (http://pgroonga.github.io)) > [ "like" is actually the operator of interest ] Ah. You might get some good results with trigram

Re: [GENERAL] Options for complex materialized views sharing most of the same logic?

I do not. I just probably tweak it a couple of times a week due to adding/removal of columns of interest and I just would like to have the logic in one place if possible... On Sun, Dec 27, 2015 at 4:03 PM, Andreas Kretschmer wrote: > Do you recreate the views every

Re: [GENERAL] grep -f keyword data query

Hello Andreas and Tom Thank you for replying. Sorry, I re-created my questions. I was mis-pasted query log on previous question. (@~ operator is PGroonga extension (http://pgroonga.github.io)) Please ignore it. Best regards. 1, Problem. (1) Following query is exteme slow. (478sec) SELECT

Re: [GENERAL] grep -f keyword data query

Hello Tom. Thank you for replying. I re-created index with pg_trgm. Execution time is 210sec. Yes It is faster than btree index. But still slow. It is possible to improve this query speed? Should I use another query or idex? Best regards. 1, query SELECT u.url FROM

Re: [GENERAL] Options for complex materialized views sharing most of the same logic?

Do you recreate the views every day? Why? (stupid smartphone-app, sorry for top-posting) Am 27. Dezember 2015 22:39:58 MEZ, schrieb Wells Oliver : > >Is there some easier way for me to maintain the structure of the view >without copying/pasting it 4 times and making one

Re: [GENERAL] Options for complex materialized views sharing most of the same logic?

Maybe you can create a so called SRF - Funktion (Set Returning Funktion) with proper parameters to use AS 'create materialized view ... AS select * Form your_srf_Funktion(param1,param2,...,paramN). Untested, but i think it should work. (stupid smartphone-app ...) Am 27. Dezember 2015