1
vote
0answers
31 views

postgres - generating documents for full text search taking forever and eating up disk space

We use PostgreSQL and Django-Haystack with Elasticsearch for search on our service, and were thinking of switching to Postgres's built in full text search because of various annoyances using a NoSQL ...
0
votes
1answer
30 views

Mixed search in multiple columns

I have a table which has a particular column of type JSON, where some user's contact information are stored. I can alter the table and add new columns with cached values to increase search performance ...
1
vote
1answer
31 views

capability and performance of tags implemented as array vs text vs full text

In terms of search capability and performance, what are the pros & cons of implementing tags as an array or text or full text field? I am seeing limitations of capability in choosing to implement ...
0
votes
2answers
266 views

How do I use an Ispell dictionary with Postgres text search?

Postgres can use Ispell-compatible dictionaries in text search, but does not provide the required files.
0
votes
1answer
283 views

PostgreSQL FTS and Trigram-similarity Query Optimization

I have recently started working on PostgreSQL and I have around 12M rows to handle in which I want to apply Full Text Search. I don't have any prior experience in handling such databases. I have tried ...
1
vote
2answers
132 views

to use full text search or not?

In my web app, I have a web page for admins that displays the following fields: user id (numeric) device id(numeric) device description (text) cell phone number (text) user name (string ... but ...
0
votes
1answer
146 views

ERROR: text search configuration name “english” must be schema-qualified [closed]

Trying to do some full text searching with postgres and when running the following command UPDATE "EMAIL" SET PostText = to_tsvector('pg_catalog.english', "TEXT"); I'm getting the error: ERROR: ...
0
votes
1answer
496 views

Full text search on multiple tables in PostgreSQL

I have several tables that have related data. I want to have a search input box on the dashboard of the system that will search for anything (client, invoice, delivery note, ...). Is it possible to ...
1
vote
1answer
741 views

postgres GROUP BY and ORDER BY problem

I have two tables like this: CREATE TABLE cmap5 ( name VARCHAR(2000), lexemes TSQUERY ); and CREATE TABLE IF NOT EXISTS synonyms_all_gin_tsvcolumn ( cid int NOT NULL ...
4
votes
3answers
514 views

Full Text Search With PostgreSQL

i have a table with this rows: Stickers ------------------------------------------------------ ID | Title |Keywords (ts_vector) ------------------------------------------------------ ...
1
vote
1answer
145 views

Parse hyphenated word to unsigned integer

When tested to_tsvector on 'someword-123' with english full text config, I got the following result: SELECT to_tsvector('someword-123'); to_tsvector tsvector -------------------- "'-123':2 ...
6
votes
1answer
125 views

Can I get the union of the a Postgres fulltext index?

I have a fulltext index on a table. Is it possible to retrieve the set of terms used in the index (gist or gin)? With weights if possible? To clarify: If I have the following table: create table ...
4
votes
2answers
818 views

Slow ORDER BY with LIMIT

I have this query: SELECT * FROM location WHERE to_tsvector('simple',unaccent2("city")) @@ to_tsquery('simple',unaccent2('wroclaw')) order by displaycount I'm happy with it: "Sort ...
5
votes
2answers
2k views

Postgres full text search with multiple columns, why concat in index and not at runtime?

I've come across full text search in postgres in the last few days, and I am a little confused about indexing when searching across multiple columns. The postgres docs talk about creating a ts_vector ...
2
votes
2answers
366 views

What does “GIN doesn't support full index scans” mean?

I'm a RDBMS newbie and I have a PostgreSQL database of more than 50 millions lines, growing everyday. There is a column 'text', which contains human-generated text of around 20 words. I need to be ...
3
votes
3answers
572 views

ts_rank massively slows my query, how can I improve performance?

Here's the table definition Table "public.kb_article_contents" Column | Type | Modifiers ------------+----------+----------- article_id | smallint | not null contents | text | not ...
9
votes
3answers
2k views

How is LIKE implemented?

Can anyone explain how the LIKE operator is implemented in current database systems (e.g. MySQL or Postgres)? or point me to some references that explain it? The naive approach would be to inspect ...