[GENERAL] Changing varchar length by manipulating pg_attribute

Hi I have a database in which I'd like to increase the length of a varchar column. Unfortunately, the column is used in various views which then are used in other views, so doing this with ALTER TABLE ALTER COLUMN TYPE is quite a lot of work. I have found this suggestion

Re: [GENERAL] GIN Trigram Index Size

On 10/09/15 06:40, Jeff Janes wrote: > Vacuuming will allow the space to be reused internally. It will not > visibly shrink the index, but will mark that space as eligible for reuse. > > If you have a 36GB index and a reindex would have reduced it to 15GB, > then a vacuum will leave it at 36GB b

[GENERAL] GIN Trigram Index Size

Hi Some weeks ago I got some advice here on how to build a trigram index. Now, I have implemented this in our rather large database, which looks like this: We have one table of log events per month, used as partitions via "logs": public | logs | table| postgres public |

Re: [GENERAL] How to speed up pg_trgm / gin index scan

On 28/07/15 16:42, Merlin Moncure wrote: >> >> Great stuff! Sorry Oleg I don't have your original message anymore and >> can't reply into the right place in the thread, so I took the liberty to >> CC: you. > > There are some more big optimizations (via Jeff Janes) coming down the > pike for trigra

Re: [GENERAL] How to speed up pg_trgm / gin index scan

On 22/06/15 13:51, Christian Ramseyer wrote: > Hi > > I have a pretty large table with syslog messages. > > It is already partitioned by month, and for a single month I have e.g. > > > DM=# \d+ logs_01 > >

Re: [GENERAL] How to speed up pg_trgm / gin index scan

On 23/06/15 01:30, Oleg Bartunov wrote: > Try 9.4 and you'll surprise. > > 1. GIN has compression > 2. GIN has fast scan feature. > Dang, and I was so happy that the Enterprise Linux we have to use here finally had 9.1 with pg_trgm :) But this sounds too good not to try it, I'll try to get 9.4

Re: [GENERAL] How to speed up pg_trgm / gin index scan

On 22/06/15 20:32, Jaime Casanova wrote: > What version of postgres is this? GIN indexes improved a lot in 9.4, > they use less than half the space and have doubled the speed (on > average). > > Now, whatever version you have; a GIN index has two data structures, > the main one in which the index

Re: [GENERAL] How to speed up pg_trgm / gin index scan

On 22/06/15 19:00, Jeff Janes wrote: > > > A typical query on this table looks like this: > > explain analyze > select log_date, host, msg > from logs_01 as log where log.msg like '%192.23.33.177%' > and log.log_date >= '2015-1-18 1:45:24' > and log.log_date

[GENERAL] How to speed up pg_trgm / gin index scan

Hi I have a pretty large table with syslog messages. It is already partitioned by month, and for a single month I have e.g. DM=# \d+ logs_01 Column|Type | --+-+ host |

Re: [GENERAL] indexed range queries on jsonb?

On 8/26/14 3:30 PM, Larry White wrote: > > Logically, what I want is to be able to make queries like this: > > select * from document where ((payload->'intTest'))> 5; > > With casting, I came up with: > > select * from document where (((payload->'intTest'))::text)::integer > > 5;

Re: [GENERAL] Audtiting, DDL and DML in same SQL Function

On 2/2/12 12:39 AM, Scott Marlowe wrote: > On Wed, Feb 1, 2012 at 4:27 PM, Scott Marlowe wrote: >> On Wed, Feb 1, 2012 at 3:29 PM, Christian Ramseyer wrote: >>> Optimally, I'd just have my applications perform a single >>> call after connecting, e.g. "audit_

[GENERAL] Audtiting, DDL and DML in same SQL Function

Hello list I'm trying to build a little trigger-based auditing for various web applications. They have many users in the application layer, but they all use the same Postgres DB and DB user. So I need some kind of session storage to save this application level username for usage in my triggers, w

Re: [GENERAL] Cisco Systems fail

://petereisentraut.blogspot.com/2008/03/readding-implicit-casts-in-postgresql.html> Good luck Christian -- Christian Ramseyer r...@networkz.ch -- 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 search ignoring spaces and minus signs

On 10/13/2010 07:45 PM, Andrus wrote: CHAR(20) columns in 8.4 database may contains spaces and - signs like 13-333-333 12 3-44 33 33 333 12345 User enters code to search without spaces and - signs, like 12344 How to search for product code ignoring spaces and - signs? There are many options

Re: [GENERAL] FTS GIN Index Question

On 09/29/2010 12:10 PM, Oleg Bartunov wrote: Christian, On Wed, 29 Sep 2010, Christian Ramseyer wrote: Hi List I have a largish partitioned table, it has ~60 million records in each of 12 partitions. It appears that a Full Text Index could speed up some user queries a lot. A quick test with

[GENERAL] FTS GIN Index Question

Hi List I have a largish partitioned table, it has ~60 million records in each of 12 partitions. It appears that a Full Text Index could speed up some user queries a lot. A quick test with an additional tsvector column revealed that this would take up around 35 GB of space for this column an