WHERE (setweight (to_tsvector ( 'english'::regconfig, key_str ), 'A' ) || I then updated the query to use the new index, websearch_to_tsquery, and add the same default LIMIT used by Elasticsearch, SELECT * FROM search_idx I'm using PostgreSQL 10 in production, however, since I'm using the latest version of Elasticsearch, it's only fair to pull the latest version of PostgreSQL ( postgres:12.4-alpine at the time of writing). Setweight (to_tsvector ( 'english'::regconfig, val_str ), 'B' ) ) ) USING GIN ( (setweight (to_tsvector ( 'english'::regconfig, key_str ), 'A' ) || With help, I created a single GIN index instead of using two, CREATE INDEX search_idx_key_str_idx ON search_idx So it seems like the added overhead of maintaining an Elasticsearch cluster may be worth it to get the performance I'm after. I get 5-24ms on 136 matching results clustered on the low end the more you run the query. Now, run some queries, curl -sX GET "localhost:9200/search-idx/_search?pretty" \ Note: this script is fine for testing purposes, but in production, follow best practices on sizing bulk requests and using multiple threads. The following can insert ~20,000 rows/second into tables that don't have indexes, const pgp = require ( "pg-promise" ) ( ) const faker = require ( "faker" ) const Iterations = 150 const seedDb = async ( ) => seedIndex ( ) Id BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY , First, create a table, CREATE TABLE IF NOT EXISTS search_idx I'm already familiar with PostgreSQL, so let's see if it meets these requirements. The search company Algolia recommends an end-to-end latency budget of no more than 50ms, so we'll use that as our threshold. It should enable full-text search over keys and values and range queries over numbers and dates with 1.5 million unique key-value pairs as the expected maximum search index size. The use-case is real-time search over key-value pairs where the keys are strings and the values are either strings, numbers, or dates. I started investigating full-text search options recently. The "-" or prohibit operator excludes documents that contain the term after the "-" symbol.Update: Use one GIN index instead of two, websearch_to_tsquery, add LIMIT, and store TSVECTOR as separate column. For example, the following search will return no results: NOT "computational objects". Note: The NOT operator cannot be used with just one term. To search for documents that contain "computational objects" but not "Computational effort" use the query: "computational objects" NOT "Computational effort" The symbol ! can be used in place of the word NOT. This is equivalent to a difference using sets. The NOT operator excludes documents that contain the term after NOT. To search for documents that must contain "computational" and may contain "objects" use the query: +computational objects NOT The "+" or required operator requires that the term after the "+" symbol exist somewhere in a the field of a single document. To search for documents that contain "computational objects" and "Computational effort" use the query: "computational objects" AND "Computational Effort" + The symbol & can be used in place of the word AND. This is equivalent to an intersection using sets. The AND operator matches documents where both terms exist anywhere in the text of a single document. Or "computational objects" OR computational AND To search for documents that contain either "computational objects" or just "computational" use the query: "computational objects" computational
The symbol || can be used in place of the word OR. This is equivalent to a union using sets.
The OR operator links two terms and finds a matching document if either of the terms exist in a document. This means that if there is no Boolean operator between two terms, the OR operator is used. The OR operator is the default conjunction operator. The search system supports AND, "+", OR, NOT and "-" as Boolean operators. Boolean operators allow terms to be combined through logic operators.
0 kommentar(er)
