Re: [GENERAL] full text search on hstore or json with materialized view?

On Thu, 20 Apr 2017 07:56:18 -0700, Rj Ewing wrote: >On Wed, Apr 19, 2017 at 6:44 PM, George Neuner wrote: >> >> If you can restrict the FTS query to certain keys: >> >> SELECT id FROM mytable >> WHERE tsquery( ... ) @@ to_tsvector(v) >> AND k IN ( ... ) >> GROUP BY id >> >> [not

Re: [GENERAL] full text search on hstore or json with materialized view?

On Thu, 20 Apr 2017 08:50:31 -0700, Rj Ewing wrote: >On Wed, Apr 19, 2017 at 9:55 PM, George Neuner wrote: > >> ... Since you are *testing* with 1M records (that >> create 44M k:v shards), I am assuming you will need to deal with much >> more than that in deployment. And if you think you need F

Re: [GENERAL] full text search on hstore or json with materialized view?

On Wed, Apr 19, 2017 at 9:55 PM, George Neuner wrote: > On Wed, 19 Apr 2017 16:28:13 -0700, Rj Ewing > wrote: > > >okay, messing around a bit more with the secondary k,v table it seems like > >this could be a good solution.. > > > >I created a keys table to hold the 63 key values, then I dropped

Re: [GENERAL] full text search on hstore or json with materialized view?

On Wed, Apr 19, 2017 at 8:09 PM, Jeff Janes wrote: > > Your best bet might be to ignore the per-field searching in the initial > (indexed) pass of the query to get everything that has all the search > terms, regardless of which field they occur in. And the re-check whether > each of the found va

Re: [GENERAL] full text search on hstore or json with materialized view?

On Wed, Apr 19, 2017 at 6:44 PM, George Neuner wrote: > > On Wed, 19 Apr 2017 11:57:26 -0700, Rj Ewing > wrote: > > >I did some testing using a secondary table with the key, value column. > >However I don't think this will provide the performance that we need. > >Queries we taking 60+ seconds jus

Re: [GENERAL] full text search on hstore or json with materialized view?

On Thu, 20 Apr 2017 00:55:48 -0400, George Neuner wrote: Doh! > SELECT count(distinct s.id) >FROM samples_lg_txt AS s >JOIN keys AS k ON k.id = s.key >WHERE (k.name = 'key1' AND s.tsv @@ to_query('value1') > OR (k.name = 'key2' AND s.tsv @@ to_query('value2') > > : > > SEL

Re: [GENERAL] full text search on hstore or json with materialized view?

On Wed, 19 Apr 2017 16:28:13 -0700, Rj Ewing wrote: >okay, messing around a bit more with the secondary k,v table it seems like >this could be a good solution.. > >I created a keys table to hold the 63 key values, then I dropped and >recreated the secondary table, using a FK referencing the keys

Re: [GENERAL] full text search on hstore or json with materialized view?

Please don't top-post, thanks. On Tue, Apr 18, 2017 at 3:18 PM, Rj Ewing wrote: > On Tue, Apr 18, 2017 at 3:00 PM, Bruce Momjian wrote: > >> >> Full text search of JSON and JSONB data is coming in Postgres 10, which >> is to to be released in September of this year: >> >> https://www.de

Re: [GENERAL] full text search on hstore or json with materialized view?

Please don't top post. https://en.wikipedia.org/wiki/Posting_style#Placement_of_replies https://en.wikipedia.org/wiki/Posting_style#Choosing_the_proper_posting_style >> On Tue, 18 Apr 2017 14:38:15 -0700, Rj Ewing >> wrote: >> >> > : >> >An idea that has come up is to use a materialized view

Re: [GENERAL] full text search on hstore or json with materialized view?

okay, messing around a bit more with the secondary k,v table it seems like this could be a good solution.. I created a keys table to hold the 63 key values, then I dropped and recreated the secondary table, using a FK referencing the keys table. I'm not really sure why, but a basic full text query

Re: [GENERAL] full text search on hstore or json with materialized view?

I did some testing using a secondary table with the key, value column. However I don't think this will provide the performance that we need. Queries we taking 60+ seconds just for a count. With 1 million rows in the primary table, this resulted in 44 million rows in the secondary k,v table for ful

Re: [GENERAL] full text search on hstore or json with materialized view?

On Tue, 18 Apr 2017 14:38:15 -0700, Rj Ewing wrote: >I am evaluating postgres for as a datastore for our webapp. We are moving >away from a triple store db due to performance issues. > >Our data model consists of sets of user defined attributes. Approx 10% of >the attributes tend to be 100% fille

Re: [GENERAL] full text search on hstore or json with materialized view?

A step in the right direction for me, however it doesn't appear to support per field full text searching. It is exciting though! On Tue, Apr 18, 2017 at 3:00 PM, Bruce Momjian wrote: > On Tue, Apr 18, 2017 at 02:38:15PM -0700, Rj Ewing wrote: > > I am evaluating postgres for as a datastore for o

Re: [GENERAL] full text search on hstore or json with materialized view?

On Tue, Apr 18, 2017 at 02:38:15PM -0700, Rj Ewing wrote: > I am evaluating postgres for as a datastore for our webapp. We are moving away > from a triple store db due to performance issues. > > Our data model consists of sets of user defined attributes. Approx 10% of the > attributes tend to be 1

[GENERAL] full text search on hstore or json with materialized view?

I am evaluating postgres for as a datastore for our webapp. We are moving away from a triple store db due to performance issues. Our data model consists of sets of user defined attributes. Approx 10% of the attributes tend to be 100% filled with 50% of the attributes having approx 25% filled. This

Re: [GENERAL] Full Text Search combined with Fuzzy

On 03.03.2017 16:17, Nicolas Paris wrote: Nice ! I do have 9.6 version. Would this kind of index could handle more than 20M large texts ? The recheck condition looks ressource consuming. You are right. I think pg_trgm will be not good for such large texts, unfortunately. The full text ind

Re: [GENERAL] Full Text Search combined with Fuzzy

Le 03 mars 2017 à 14:08, Artur Zakirov écrivait : > On 03.03.2017 15:49, Nicolas Paris wrote: > > > >Hi Oleg, > > > >Thanks. I thought pgtrgm was not able to index my long texts because of > >limitation of 8191 bytes per index row for btree. > > > >Then I found out it is possible to use pgtrgm over

Re: [GENERAL] Full Text Search combined with Fuzzy

On 03.03.2017 15:49, Nicolas Paris wrote: Hi Oleg, Thanks. I thought pgtrgm was not able to index my long texts because of limitation of 8191 bytes per index row for btree. Then I found out it is possible to use pgtrgm over a GIN/GIST index. My final use case is phrase mining in texts. I want

Re: [GENERAL] Full Text Search combined with Fuzzy

Le 27 févr. 2017 à 10:32, Oleg Bartunov écrivait : > > > On Sun, Feb 26, 2017 at 3:52 PM, Nicolas Paris wrote: > > Hello, > > AFAIK there is no built-in way to combine full text search and fuzzy > matching > (https://www.postgresql.org/docs/current/static/fuzzystrmatch.html). >

Re: [GENERAL] Full Text Search combined with Fuzzy

On Sun, Feb 26, 2017 at 3:52 PM, Nicolas Paris wrote: > Hello, > > AFAIK there is no built-in way to combine full text search and fuzzy > matching > (https://www.postgresql.org/docs/current/static/fuzzystrmatch.html). > By example, phrase searching with tipos in it. > > First I don't know if post

[GENERAL] Full Text Search combined with Fuzzy

Hello, AFAIK there is no built-in way to combine full text search and fuzzy matching (https://www.postgresql.org/docs/current/static/fuzzystrmatch.html). By example, phrase searching with tipos in it. First I don't know if postgresql concurrents (lucene based...) are able to do so. Second, is su

Re: [GENERAL] Full text search randomly not working for short prefixes?

På fredag 02. desember 2016 kl. 16:33:12, skrev Tom Lane mailto:t...@sss.pgh.pa.us>>: cen writes: > Something funny going on with my full text search.. and I have no idea what. The way to debug this sort of thing is generally to look at what tsquery you're actually getting.  I get regression

Re: [GENERAL] Full text search randomly not working for short prefixes?

Thanks, that makes sense. I think I'll go with the cast approach, I don't really need stemming anywhere. Tom Lane je 02. 12. 2016 ob 16:33 napisal: cen writes: Something funny going on with my full text search.. and I have no idea what. The way to debug this sort of thing is generally to lo

Re: [GENERAL] Full text search randomly not working for short prefixes?

cen writes: > Something funny going on with my full text search.. and I have no idea what. The way to debug this sort of thing is generally to look at what tsquery you're actually getting. I get regression=# select to_tsquery(unaccent('a:*')); NOTICE: text-search query contains only stop word

[GENERAL] Full text search randomly not working for short prefixes?

Something funny going on with my full text search.. and I have no idea what. I have a receiver called "Ana", this is her tsv column: '3865100':4 'acevent...@mailinator.com':3B 'ana':1A 'novak':2A This queries do not find her: SELECT * FROM receivers r WHERE r.tsv @@ to_tsquery(unaccent('a:*'

Re: [GENERAL] Full text search tsv column aproach vs concat confusion

Hi, On 16.11.2016 11:54, cen wrote: Hi I am seeking some clarification in regard to full text search across multiple tables and what the best approach is. Documentation talks about two approaches when it comes to building a document: on-the-fly concat of columns and a dedicated tsv column appro

[GENERAL] Full text search tsv column aproach vs concat confusion

Hi I am seeking some clarification in regard to full text search across multiple tables and what the best approach is. Documentation talks about two approaches when it comes to building a document: on-the-fly concat of columns and a dedicated tsv column approach. Let's say I want to perform a

Re: [GENERAL] Full text search on Chemistry text

On Wed, Jun 8, 2016 at 8:46 AM, Allan Kamau wrote: > I would like to generate tsvectors on documents that contain chemistry > related text. > Is there a synonym dictionary for chemistry terms available? > > -Allan > ​I did a Google search on "chemistry terms dictionary" and got a lot of hits. Ar

[GENERAL] Full text search on Chemistry text

I would like to generate tsvectors on documents that contain chemistry related text. Is there a synonym dictionary for chemistry terms available? -Allan

Re: [GENERAL] full text search index

You don't provide much context, like PostgreSQL version or machine characteristics. https://wiki.postgresql.org/wiki/SlowQueryQuestions On Wed, May 25, 2016 at 11:04 PM, Patrick Baker wrote: >> SELECT COUNT(DISTINCT j0_.id) AS sclr10 >> FROM customers j0_ >> WHERE ((LOWER(j0_.name_first) LIKE '

Re: [GENERAL] full text search index

On 26 May 2016 at 06:04, Patrick Baker wrote: > Hi there, > > I've got the following query: >> >> >> SELECT COUNT(DISTINCT j0_.id) AS sclr10 >> FROM customers j0_ >> WHERE ((LOWER(j0_.name_first) LIKE '%some%' >> OR LOWER(j0_.name_last) LIKE '%some%') >>AND j0_.id = 5) >> AND j0_

Re: [GENERAL] full text search index

> > > > Maybe Lucas Possamai can help. He seems to be a little bit further along > in this exercise. > > I'm too tired to care at the moment. And I haven't had much practical > work here anyway. > > David J. > > > > I subscribed to the list today, so don't have the old emails I had a look o

[GENERAL] full text search index

On Thursday, May 26, 2016, Patrick Baker wrote: > Hi there, > > I've got the following query: > >> >> SELECT COUNT(DISTINCT j0_.id) AS sclr10 >> FROM customers j0_ >> WHERE ((LOWER(j0_.name_first) LIKE '%some%' >> OR LOWER(j0_.name_last) LIKE '%some%') >>AND j0_.id = 5) >> AND j

[GENERAL] full text search index

Hi there, I've got the following query: > > SELECT COUNT(DISTINCT j0_.id) AS sclr10 > FROM customers j0_ > WHERE ((LOWER(j0_.name_first) LIKE '%some%' > OR LOWER(j0_.name_last) LIKE '%some%') >AND j0_.id = 5) > AND j0_.id = 5 The query is taking ages to run. I read about wild

Re: [GENERAL] Full text search question: "01.Bez." --> "Erster Bezirk"

Thank you, I was in direct contact with the author. All my issues and questions got sorted out. it's working perfectly! Thank you, Johann 2016-03-13 18:32 GMT+01:00 Dane Foster : > Hello, > > ​ > On Sat, Mar 12, 2016 at 11:40 AM, Johann Höchtl > wrote: > >> I fear I have an involved challenge c

Re: [GENERAL] Full text search question: "01.Bez." --> "Erster Bezirk"

Hello, ​ On Sat, Mar 12, 2016 at 11:40 AM, Johann Höchtl wrote: > I fear I have an involved challenge concerning FTS. > > Assume I have the following text in a column: > > Graz,06.Bez.:Blah > > This parses as: > SELECT alias, description, token FROM ts_debug('german', > 'Graz,06.Bez.:Blah'); >

[GENERAL] Full text search question: "01.Bez." --> "Erster Bezirk"

I fear I have an involved challenge concerning FTS. Assume I have the following text in a column: Graz,06.Bez.:Blah This parses as: SELECT alias, description, token FROM ts_debug('german', 'Graz,06.Bez.:Blah'); alias | description | token ---+-+ ascii

Re: [GENERAL] : :Full text search query ::

Hi, On 25.2.2015 12:50, JD wrote: > Hi All, > > please find herewith the following query > > 1. select * from partdetails where scode=118 and > (to_tsvector('english'::regconfig, part_number::text)) @@ to_tsquery('104') > > it is showing only 1 record as output, it is expected to give 17 reco

[GENERAL] : :Full text search query ::

Hi All, please find herewith the following query 1. select * from partdetails where scode=118 and (to_tsvector('english'::regconfig, part_number::text)) @@ to_tsquery('104') it is showing only 1 record as output, it is expected to give 17 records as output. 2. select * from partdetails where s

Re: [GENERAL] Full text search prefix matching

On 16.12.2014, at 17:09, Vincent Veyron wrote: > On Tue, 16 Dec 2014 14:59:51 +0200 > Heikki Rauhala wrote: > > Hi Heikki, > > There is a typo : > >> create materialized view name_fulltext as > > ERREUR: erreur de syntaxe sur ou près de « materialized » > LIGNE 1 : create materialized view

Re: [GENERAL] Full text search prefix matching

Heikki Rauhala writes: > Should text search prefixes work predicatably as documented in [1] even if > the lexemes are shorter than the query? How can I get it to work? I believe what you're seeing can be explained by these observations: regression=# select to_tsvector('finnish', 'sofia'); to_t

Re: [GENERAL] Full text search prefix matching

On Tue, 16 Dec 2014 14:59:51 +0200 Heikki Rauhala wrote: Hi Heikki, There is a typo : > > create materialized view name_fulltext as ERREUR: erreur de syntaxe sur ou près de « materialized » LIGNE 1 : create materialized view name_fulltext as You might want to post the results too, next time

[GENERAL] Full text search prefix matching

Hi, I’m trying to use PostgreSQL's full text search for searching names with prefix matching. I’ve got a materialized view with the tsvector’s in an indexed column which I’m then searching with prefix matching, as in the sqlfiddle: http://sqlfiddle.com/#!15/a2389/6 and below. My problem is th

Re: [GENERAL] Full-Text Search question

Search for fulltext tutorial + json functions http://www.postgresql.org/docs/9.3/static/functions-json.html On Wed, May 14, 2014 at 1:00 AM, Jesus Rafael Sanchez Medrano < jesusraf...@gmail.com> wrote: > thanks... could you please be so kind to post some snippet/code for this? > > Att. > == > J

Re: [GENERAL] Full-Text Search question

thanks... could you please be so kind to post some snippet/code for this? Att. == Jesus Rafael Sanchez Medrano "Life is a dream, of which all must wake up" On Tue, May 13, 2014 at 5:33 PM, Oleg Bartunov wrote: > Easy, you need to extract text fields from json and construct tsvector > from them

Re: [GENERAL] Full-Text Search question

Easy, you need to extract text fields from json and construct tsvector from them (use concatenation, for example). On Tue, May 13, 2014 at 7:38 PM, Jesus Rafael Sanchez Medrano wrote: > can postgres do FTS (full text search) on a json column? if possible, please > be so kindd to give some snippet

[GENERAL] Full-Text Search question

can postgres do FTS (full text search) on a json column? if possible, please be so kindd to give some snippet/example. Att. == Jesus Rafael Sanchez Medrano "Life is a dream, of which all must wake up"

Re: [GENERAL] Full text search on partial URLs

On 11/15/2013 07:40 PM, Zev Benjamin wrote: One problem that I've run into here is that I would also like to highlight matched text in my application. For my existing search solution, I do this with ts_headline. For partial matches, it's unfortunately not just a matter of searching for the tex

Re: [GENERAL] Full text search on partial URLs

On 11/15/2013 07:40 PM, Zev Benjamin wrote: One problem that I've run into here is that I would also like to highlight matched text in my application. For my existing search solution, I do this with ts_headline. For partial matches, it's unfortunately not just a matter of searching for the tex

Re: [GENERAL] Full text search on partial URLs

On 11/06/2013 02:04 PM, bricklen wrote: On Wed, Nov 6, 2013 at 10:53 AM, Zev Benjamin mailto:zev-pg...@strangersgate.com>> wrote: On 11/06/2013 01:47 PM, bricklen wrote: On Wed, Nov 6, 2013 at 10:45 AM, Zev Benjamin mailto:zev-pg...@strangersgate.com>

Re: [GENERAL] Full text search on partial URLs

On Wed, Nov 6, 2013 at 10:53 AM, Zev Benjamin wrote: > On 11/06/2013 01:47 PM, bricklen wrote: > >> >> On Wed, Nov 6, 2013 at 10:45 AM, Zev Benjamin >> mailto:zev-pg...@strangersgate.com>> wrote: >> >> Hi, >> >> I have Postgres full text search set up for my application and it's >> bee

Re: [GENERAL] Full text search on partial URLs

On 11/06/2013 01:47 PM, bricklen wrote: On Wed, Nov 6, 2013 at 10:45 AM, Zev Benjamin mailto:zev-pg...@strangersgate.com>> wrote: Hi, I have Postgres full text search set up for my application and it's been working great! However, my users would like their searches to turn up

Re: [GENERAL] Full text search on partial URLs

On Wed, Nov 6, 2013 at 10:45 AM, Zev Benjamin wrote: > Hi, > > I have Postgres full text search set up for my application and it's been > working great! However, my users would like their searches to turn up > parts of URLs. For example, they would like a search for "foobar" to turn > up a docum

[GENERAL] Full text search on partial URLs

Hi, I have Postgres full text search set up for my application and it's been working great! However, my users would like their searches to turn up parts of URLs. For example, they would like a search for "foobar" to turn up a document that contains the string "http://example.com/foobar/blah

Re: [GENERAL] Full text search regression tests

I am sorry this mail was not meant for this list.

[GENERAL] Full text search regression tests

Hello All, Attached patch adds regression tests to check the full-text search capability of pg_bigm. Regards, Beena Emerson bigm-fulltext-search-regression.patch Description: Binary data -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscripti

Re: [GENERAL] Full text search

On Fri, Jul 12, 2013 at 12:56 PM, itishree sukla wrote: > Hi Raghavendra, > > Thanks for your response, however i want same kind of result using full text > search. is it possible? > What do you mean when you say you are using full text search? Like what is the query that you used? That would be

Re: [GENERAL] Full text search

Hi Raghavendra, Thanks for your response, however i want same kind of result using full text search. is it possible? Regards, Itishree On Fri, Jul 12, 2013 at 12:14 PM, Raghavendra < raghavendra@enterprisedb.com> wrote: > > On Fri, Jul 12, 2013 at 11:48 AM, itishree sukla > wrote: > >> He

Re: [GENERAL] Full text search

On Fri, Jul 12, 2013 at 11:48 AM, itishree sukla wrote: > Hello everyone, > > I am using full text search, however it is not helping me to get the > partial value. > > For example in my document let Pizza Hut is there, if i am searching for > Pizza Hut is it giving me the values for only Pizza o

[GENERAL] Full text search

Hello everyone, I am using full text search, however it is not helping me to get the partial value. For example in my document let Pizza Hut is there, if i am searching for Pizza Hut is it giving me the values for only Pizza or a spell mistake like pizz is not returning any thing. any work arou

Re: [GENERAL] Full text search, SQL functions, and the planner

Marc Dahn writes: > So -- is there a way to let the planner look through the CASE? No. It would actually be wrong to simplify the expression in the way you're imagining, since "1 = case when bool_expr then 1 else 0 end" does not give the same result as the plain bool_expr if the latter yields nu

[GENERAL] Full text search, SQL functions, and the planner

Hi, To maintain an externally defined interface, I'd like to define a function hasword(haystack, needle) that (essentially) returns 1 when to_tsvector(haystack) @@ to_tsquery(needle), 0 otherwise. I've tried CREATE OR REPLACE FUNCTION ivo_hasword(haystack TEXT, needle TEXT) RETUR

[GENERAL] Full text search in Chinese

Can anyone shed any light on how I might go about implementing full text search for Chinese? I have heard I need to go down the NGRAM route but would love to get some external feedback. Our application has a per user 'ignore' list that we 'left outer join where null' sort of deal on so I really r

Re: [GENERAL] Full text search ranking: ordering using index and proximiti ranking with OR queries

On Wed, Mar 7, 2012 at 11:59 AM, Andrey Chursin wrote: > Is there any way to sort by ranking, avoiding seq scan? > The only way i see now is to use pg_trgm instead of ts_rank, but we > did not check yet how applicable is it for our purposes. pg_tgrm works very well in terms of measuring similarit

Re: [GENERAL] Full text search ranking: ordering using index and proximiti ranking with OR queries

There is some good news coming from Oleg Bartunov and Alexander Korotkov about improving ranking speed: http://wiki.postgresql.org/images/2/25/Full-text_search_in_PostgreSQL_in_milliseconds-extended-version.pdf It's worth reading their slides to gain a better understanding of PostgreSQL fulltext i

[GENERAL] Full-text search: Problems with dictionaries and periods

I made a custom synonym dictionary for my full-text search, using the following code from the documentation. CREATE TEXT SEARCH DICTIONARY my_synonym ( TEMPLATE = synonym, SYNONYMS = my_synonyms ); ALTER TEXT SEARCH CONFIGURATION english ALTER MAPPING FOR asciiword WITH my_synonym, en

Re: [GENERAL] Full text search ts_heading strange result

Johann Spies writes: > On Thu, Jul 26, 2012 at 04:19:02PM +0800, Craig Ringer wrote: >> You'll need to store language information alongside each text value >> if you want to do anything more sophisticated. > I was afraid that that will be the case :) I'm not sure that there's anything horribly

Re: [GENERAL] Full text search ts_heading strange result

On Thu, Jul 26, 2012 at 04:19:02PM +0800, Craig Ringer wrote: > You'll need to store language information alongside each text value > if you want to do anything more sophisticated. I was afraid that that will be the case :) I will have to update more than 32 entries which currently have inc

Re: [GENERAL] Full text search ts_heading strange result

On 07/26/2012 02:14 PM, Johann Spies wrote: Hallo Tom, I believe the problem is that the one-argument form of to_tsquery() uses the default TS configuration, which you have probably not got set to "simple". For me, the default TS configuration is "english", which will stem "polity" as "politi"

Re: [GENERAL] Full text search ts_heading strange result

Hallo Tom, > I believe the problem is that the one-argument form of to_tsquery() uses > the default TS configuration, which you have probably not got set to > "simple". For me, the default TS configuration is "english", which will > stem "polity" as "politi": > > regression=# select to_tsquery('

Re: [GENERAL] Full text search ts_heading strange result

Johann Spies writes: > I am beginning to use the full text search facilities in Postgresql > (9.0) and find the result of this query a bit strange: > query: > SELECT ts_headline('simple',title, to_tsquery('kerkreg|(church & polity)')) > from akb_articles A > where A.tsv@@ 'kerkreg|(church & pol

[GENERAL] Full text search ts_heading strange result

I am beginning to use the full text search facilities in Postgresql (9.0) and find the result of this query a bit strange: query: SELECT ts_headline('simple',title, to_tsquery('kerkreg|(church & polity)')) from akb_articles A where A.tsv@@ 'kerkreg|(church & polity)' Result "Kerkvereniging en

[GENERAL] Full text search advice requested

I have a table with bibliometric information on published articles. Fields of interest for full text searches are the 'title' and 'abstract' fields. Those fields can contain several languages but most of the entries use English. A grouped query on the 'language' field reveals that the following la

Re: [GENERAL] full text search and ILIKE type clauses.

2012/3/12 Tim Uckun : >> It is my understanding that since the extention citext is available that >> this gives you what your asking for and at least at this point isn't >> going to be part of the core. >> > > For me it's more of a workaround than a solution but yes probably good > enough. Collatio

Re: [GENERAL] full text search and ILIKE type clauses.

> It is my understanding that since the extention citext is available that > this gives you what your asking for and at least at this point isn't > going to be part of the core. > For me it's more of a workaround than a solution but yes probably good enough. Collation is more subtle than case inse

Re: [GENERAL] full text search and ILIKE type clauses.

t: Re: [GENERAL] full text search and ILIKE type clauses. From: Tim Uckun Date: Mon, March 12, 2012 1:20 pm To: mgo...@isstrucksoftware.net Cc: Tom Lane , pgsql-general > > We made most of our text, varchar columns citext data types so that we > could do case insensitive searches. Is this g

Re: [GENERAL] full text search and ILIKE type clauses.

> > We made most of our text, varchar columns citext data types so that we > could do case insensitive searches.  Is this going to negate most of the > index searches?  It appeared to our DBA that it would be easier to use > citext data type then need to use ILIKE instead? > In the same vein... D

Re: [GENERAL] full text search and ILIKE type clauses.

Software Solutions, LLC 904-226-0978 Original Message Subject: Re: [GENERAL] full text search and ILIKE type clauses. From: Tom Lane Date: Sun, March 11, 2012 7:56 pm To: Tim Uckun Cc: pgsql-general Tim Uckun writes: > I want to be able to search a lot of fields us

Re: [GENERAL] full text search and ILIKE type clauses.

> > If you're using 9.1, you might look into contrib/pg_trgm instead. If I was to use trgm would it be better to create a trigram index on each text field? In the past I have created a text field which contains the rest of the fields concatenated. That works great as long as you are looking for a

Re: [GENERAL] full text search and ILIKE type clauses.

Tim Uckun writes: > I want to be able to search a lot of fields using queries that use > ILIKE and unfortunately many of the queries will be using the > '%SOMETHING%' or '%SOMETHING' type clauses. Since indexes are useless > on those I was thinking I could use tsvectors but I can't figure out >

[GENERAL] full text search and ILIKE type clauses.

I want to be able to search a lot of fields using queries that use ILIKE and unfortunately many of the queries will be using the '%SOMETHING%' or '%SOMETHING' type clauses. Since indexes are useless on those I was thinking I could use tsvectors but I can't figure out how to accomplish this. One

Re: [GENERAL] Full text search ranking: ordering using index and proximiti ranking with OR queries

In a previous discussion thread, Oleg suggested that ts_rank is unable to use GIN indices: http://archives.postgresql.org/pgsql-general/2011-07/msg00351.php This is the only information I have about this. On Wed, Mar 7, 2012 at 18:59, Andrey Chursin wrote: > Is there any way to sort by ranking,

Re: [GENERAL] Full text search ranking: ordering using index and proximiti ranking with OR queries

Is there any way to sort by ranking, avoiding seq scan? The only way i see now is to use pg_trgm instead of ts_rank, but we did not check yet how applicable is it for our purposes. 7 марта 2012 г. 20:53 пользователь Nicolas Grilly написал: > According to Oleg in a previous discussion, ts_rank doe

Re: [GENERAL] Full text search ranking: ordering using index and proximiti ranking with OR queries

According to Oleg in a previous discussion, ts_rank does not use index because index does not store enough information for ranking: http://archives.postgresql.org/pgsql-general/2011-07/msg00351.php On Sat, Feb 18, 2012 at 12:39, Andrey Chursin wrote: > Hello > > I have two unrelated questions ab

[GENERAL] Full text search ranking: ordering using index and proximiti ranking with OR queries

Hello I have two unrelated questions about fts function ts_rank: 1) I've created GiST index on column with fts vector, but query SELECT * FROM table ORDER BY ts_rank(field, :query) LIMIT 20 is perfomed with sequential table scan. Index was created on field column. Does it mean FTS indexes does no

Re: [GENERAL] Full Text Search, avoiding lexemes search

Daniel, just use different fts configuration for search, which doesn't includes stemmers. Regards, Oleg On Fri, 27 Jan 2012, Daniel V?zquez wrote: Hi guys! Full text search, searches by lexemes, this minds that if you are finding for "gato" word you are really finding for {gat} lexeme. I you

[GENERAL] Full Text Search, avoiding lexemes search

Hi guys! Full text search, searches by lexemes, this minds that if you are finding for "gato" word you are really finding for {gat} lexeme. I you construct vectors for the words "gato", "gatos", "gata", "gatas", all have the same lexema {gat} Then the search "gato" that is to say the search {gat}

[GENERAL] Full Text Search, avoiding lexemes search

Hi guys! Full text search, searches by lexemes, this minds that if you are finding for "gato" word you are really finding for {gat} lexeme. I you construct vectors for the words "gato", "gatos", "gata", "gatas", all have the same lexema {gat} Then the search "gato" that is to say the search {gat}

Re: [GENERAL] Full text search - How to build a filtering dictionary

Thanks for reply, there is any simplest way? I have to do just a simple map (in a similar way of synonym dictionary), set the TSL_FILTER flag (if there's a map for a token) and then pass the normalized token to my own thesaurus dictionary. I'm working on Windows and I've to write a C library to

Re: [GENERAL] Full text search - How to build a filtering dictionary

Antonio, you can see contrib/unaccent dictionary, which is a filtering dictionary. I have a page about it - http://mira.sai.msu.su/~megera/wiki/unaccent Oleg On Wed, 18 Jan 2012, Antonio Franzoso wrote: Hi all, I need to build a synonym dictionary that performs a normalization of tokens

[GENERAL] Full text search - How to build a filtering dictionary

Hi all, I need to build a synonym dictionary that performs a normalization of tokens just like a filtering dictionary does. I've searched for a filtering dictionary template but I've found it. Where Can I find it? Or, if there isn't such a template, How can I build a simple filter dictionary t

Re: [GENERAL] full text search to_tsquery performance with ispell dictionary

On 11.05.11 17:31, "Tom Lane" wrote: >You really, really, really need to fix whatever is preventing you from >using pooling. Opening a database connection to run one query is just >horridly inefficient. Very true. I did not mean that anything actually prevents us from using pooling. We just

Re: [GENERAL] full text search to_tsquery performance with ispell dictionary

On 11.05.11 17:04, "t...@fuzzy.cz" wrote: >We had exactly the same problem and persistent connection solved it. First testing with persistent connections seems to work like a charm. Will do some thorough testing and watch the memory load. Hopefully, I will not trip over some sort of pitfall. Go

Re: [GENERAL] full text search to_tsquery performance with ispell dictionary

On Wed, 11 May 2011, Stanislav Raskin wrote: Yes, loading a large dictionary is known to be a fairly expensive operation. There's been discussions about how to make it cheaper, but nothing's been done yet. regards, tom lane Hi Tom, thanks for the quick response. Bad news for m

Re: [GENERAL] full text search to_tsquery performance with ispell dictionary

On 11.05.11 16:42, "Pavel Stehule" wrote: >I wrote a >patch that stores loaded dictionary in shared memory. Hi Pavel, very interesting. I will give it a closer look. What do you think about using ispell to create, store and index tsvectors, but at the same time to use the stemmer to create ts

Re: [GENERAL] full text search to_tsquery performance with ispell dictionary

Stanislav Raskin writes: > Is there any way of hack or compromise to achieve good performance without > losing fts ability? > I am thinking, for example, of a way to permanently keep a loaded > dictionary in memory instead of loading it for every connection. As I > wrote in response to Pavel Stehu

Re: [GENERAL] full text search to_tsquery performance with ispell dictionary

2011/5/11 Stanislav Raskin : > On 11.05.11 16:42, "Pavel Stehule" wrote: > > >>I wrote a >>patch that stores loaded dictionary in shared memory. > > Hi Pavel, > > very interesting. I will give it a closer look. > > What do you think about using ispell to create, store and index tsvectors, > but at

Re: [GENERAL] full text search to_tsquery performance with ispell dictionary

>> >> >> >>Yes, loading a large dictionary is known to be a fairly expensive >>operation. There's been discussions about how to make it cheaper, but >>nothing's been done yet. >> >>regards, tom lane > > Hi Tom, > > thanks for the quick response. Bad news for me ;( > We develop ajax-dri

Re: [GENERAL] full text search to_tsquery performance with ispell dictionary

On 11.05.11 15:45, "Pavel Stehule" wrote: >it is expected behave :( . A loading of ispell dictionary is very slow. > >Use a german snowball instead. > >You can you a some pooling connection software too. Thank you for the response. Is the dictionary german_stem supplied with postgresql a snowb

Re: [GENERAL] full text search to_tsquery performance with ispell dictionary

> > > >Yes, loading a large dictionary is known to be a fairly expensive >operation. There's been discussions about how to make it cheaper, but >nothing's been done yet. > >regards, tom lane Hi Tom, thanks for the quick response. Bad news for me ;( We develop ajax-driven web apps, wh

  1   2   >