performance - Benefits and trade offs for improving text search on small data in PostgreSQL -

-

  • i have 4 text columns of interest.
  • each column 100 characters.
  • the text in 3 of columns latin words. (the data biological catalog, , these names of things.)
  • the data 500 rows. don't expect grow beyond 1000.
  • a small number of users (under 10) have editing privileges add, update, , delete data. not expect these users put heavy load on database.

so suggests pretty small data set consider.

i need perform search on 4 columns rows @ least 1 column contains search text (case insensitive). query issued (and results served) via web application. i'm bit lost how approach it.

postgresql offers few options improving text searching speed. possible options built postgresql i've been considering are

  1. don't try index @ all. use ilike, like on lower, or similar. (without index?)
  2. index pg_trgm improve search speed. assume need index concatenation somehow.
  3. full text searching. assume involve concatenating index also.

unfortunately, i'm not familiar expected performance of of these or benefits , trades off, it's hard know things should try first , things shouldn't consider. things have read suggest doing indexing 2 , 3 pretty slow, conflicts fact i'll having occasional modifications going on. , mixed language makes full text search seem unattractive since appears language based, unless can handle multiple languages simultaneously. expect data small, simple ilike or maybe like on lower fast enough? or maybe indexing fast enough low load of modifications on data small? better off looking outside database?

granted, have benchmark these know sure what's fastest, unfortunately, don't have time project. benefits , trade offs of these methods? of these options not appropriate solving type of problem? other types of solutions (including potentially outside database) worth considering?

(i suppose might find kind of beginner's tutorial on text searching in pg useful, searches turn full text search part, don't know if it's useful me.)

i'm on pg 9.2.4, goodies pre-9.3 option.

update: i've expanded answer a detailed blog post.

rather focusing purely on speed, please consider search semantics first. define requirements.

for example, users need able differentiate based on order of terms? should

radiata pinus

find:

pinus radiata

? same rule apply words within column between columns?

are spaces word separators, or spaces within column part of search term?

do need wildcards? if so, need left-anchored wildcards (think staph%) or need right-anchored or infix wildcards (%ccus, p%s)? pg_tgrm infix wildcards. suffix wildcards can handled index on reverse() of word, gets clumsy in practice pg_tgrm best option there.

if you're searching discrete words , word-order isn't important, pg's full-text search to_tsvector , to_tsquery desirable. supports left-anchored wildcard searches, weighting, categories, etc.

if you're doing prefix searches of discrete columns simple like queries on regular b-tree index per column way go.

so. figure out need, then how it. current uncertainty stems partly not knowing quite want.


Comments

Post a Comment

Popular posts from this blog

java - activate/deactivate sonar maven plugin by profile? -

-
i use sonar plugin executes if activated in maven profile. sonar runs on every compile. the problem build fail other team members not have sonar application installed locally. pom.xml checked in team members same copy including sonar configuration, don't want them modify purpose of disabling sonar (they might inadvertently check modified copy in). please advise how configure maven (.m2/settings.xml) and/or pom.xml sonar feature can enabled/disabled local profile. here sonar configuration in each project's pom.xml: <plugin> <groupid>org.codehaus.mojo</groupid> <artifactid>sonar-maven-plugin</artifactid> <version>1.0</version> <executions> <execution> <id>make-sonar</id> <phase>compile</phase> <goals> <goal>sonar</goal> </goals> </execution> </executions> ...
Read more

python - TypeError: can only concatenate tuple (not "float") to tuple -

-
i'm new python, question might easy, anyway, patience: as trying call newton-raphson method calculate implied volatility in black-scholes formula call/put option pricing, first thing is, newton method in scipy.optimize seems calculate function's zeros, in black-scholes formula, want function's value option price, not zero. (i'm new here programming, i'm not sure techniques.) should write function things like: def f(sigma, price): return bsformula(s0,k,r,t,q,sigma) - price then, while calling newton method, takes args=() 1 parameter in function, write this: value = newton(bsprice2, 0.5, args=price) but error message: file "bs.py", line 36, in bsimpvol value = newton(bsprice2, 0.5, args=float(price)) file "/usr/lib/python2.7/dist-packages/scipy/optimize/zeros.py", line 143, in newton q0 = func(*((p0,) + args)) typeerror: can concatenate tuple (not "float") tuple could tell me why that? how fix it? apprecia...
Read more

java - What is the difference between String. and String.this. ? -

-
this question has answer here: meaning of .this , .class in java 2 answers i'll straight point. im still learning bit of syntax , want know difference between code is code a: public class buttonz extends jbutton{ public buttonz(){ settext(new string(string.valueof(i))); } } please ignore fact i undeclared, not lost. code b: public class buttonz extends jbutton{ public buttonz(){ settext(new string(string.this.charat(i))); } } what don't yet understand difference in typing string.this , string. i under assumption when use dot operator on class accessing it's static methods(and/or variables if they're not hidden). i have studied little bit , have concluded when using string. accessing string static methods.. when using string.this. i'm accessing methods class buttonz extendi...
Read more