3 ms·

"What is a cynic" the famous line from Lady Windmere's Fan comes up empty. You can find it here: https://www.gutenberg.org/files/790/790-h/790-h.htm https://www

"What is a cynic" the famous line from Lady Windmere's Fan comes up empty. You can find it here: https://www.gutenberg.org/files/790/790-h/790-h.htm https://www.gutenberg.org/files/790/790-h/790-h.htm The next line I can find though. How are you parsing?

gutensearch 6y ago
Thanks for noticing! To be more specific, "what is a" are stop words and "cynic" is very common, so a lot of rows are returned (see my other comment). ts_rank takes too long to rank them, and the server times out, leaving you with the previous query's table because I didn't take the time to program a correct response to this issue. "Cecil Graham. What is a cynic?" returns Lady Windermere's Fan almost instantly. The workarounds I've thought of would be to cache these queries (assuming I've seen them before, and after I've set up logging), buy a larger server, or pay Second Quadrant to speed up ts_rank... I'd love any suggestions from more experienced Postgres engineers!
- gutensearch 6y ago
  Thanks for noticing! To be more specific, "what is a" are stop words and "cynic" is very common, so a lot of rows are returned (see my other comment). ts_rank takes too long to rank them, and the server times out, leaving you with the previous query's table because I didn't take the time to program a correct response to this issue. "Cecil Graham. What is a cynic?" returns Lady Windermere's Fan almost instantly. The workarounds I've thought of would be to cache these queries (assuming I've seen them before, and after I've set up logging), buy a larger server, or pay Second Quadrant to speed up ts_rank... I'd love any suggestions from more experienced Postgres engineers! Edit to your edit, re parsing. The subset of rows returned follows: where language = %s::regconfig and textsearchable_index_col @@ phraseto_tsquery(%s::regconfig, %s) and relevance is determined by: ts_rank_cd(textsearchable_index_col, phraseto_tsquery(%s::regconfig, %s), 32) with the %s being language and paragraph respectively.
  - kristopolous 6y ago
    full-text searching is contentious, it's a rabbit-hole of holy-wars. I don't believe I'm going to summon cranks with the following: There's more specialized tools for your usecase than postgres and you should be looking into "n-gram indexing". Lucene based systems such as elasticsearch are quite popular there's also sphinx and xapian, also fairly widespread. You need to read the documentation and configure them, they are flexible and need to be tuned to this usecase. In the end, there is no "correct" way to do things. For instance, sometimes stemming words is the right way to go, but if you are say doing a medical system where two very different medicines could be spelled similar and stem to the same word, a mistake could lead to death, so no, stemming is very bad here. Sometimes stop words is the way to go, but if people are looking for quotes, such as "to be or not to be" well now you have the empty string, splendid. So yeah, configure configure configure. This may bring out the cranks: If you want to roll-your-own nosql systems like redis and mongo or couch seem to work really well (I've rolled my own in all 3 on separate occasions). I guarantee there's advanced features of maria and postgres that aren't widely used and some people reading this will confidently claim they are superior but I assure you, that is a minority opinion. Most people go with the other options. If you ever doubt it, ask the actual developers of postgres or maria on chat. They are extremely nice and way more honest about the limitations then their hardcore fans are. The databases are under constant development with advanced features and you'll learn a lot (really, they are both super chill dev communities, impressively so). Perhaps your solution (as mine has been) is a hybrid. You can store the existence for instance, in one system and the offsets and chunks in another so you get a parallelizable asynchronous worker pipeline, it's impressively fast when you horizontally scale it. <0.5 sec for multiple terabytes of text (and yes, I'm talking nvme/hundreds of gb of RAM per node/>=10gb network). I've legitimately just done random queries to marvel at the speed I really hope I save myself from the rock throwing.
    - gutensearch 6y ago
      Thanks! I really appreciate the pointers. I already had planned to explore some of these and you've expanded and directed the search nicely.
    - yorwba 6y ago

gutensearch 6y ago

Thanks for noticing! To be more specific, "what is a" are stop words and "cynic" is very common, so a lot of rows are returned (see my other comment). ts_rank takes too long to rank them, and the server times out, leaving you with the previous query's table because I didn't take the time to program a correct response to this issue. "Cecil Graham. What is a cynic?" returns Lady Windermere's Fan almost instantly. The workarounds I've thought of would be to cache these queries (assuming I've seen them before, and after I've set up logging), buy a larger server, or pay Second Quadrant to speed up ts_rank... I'd love any suggestions from more experienced Postgres engineers!

gutensearch 6y ago
Thanks for noticing! To be more specific, "what is a" are stop words and "cynic" is very common, so a lot of rows are returned (see my other comment). ts_rank takes too long to rank them, and the server times out, leaving you with the previous query's table because I didn't take the time to program a correct response to this issue. "Cecil Graham. What is a cynic?" returns Lady Windermere's Fan almost instantly. The workarounds I've thought of would be to cache these queries (assuming I've seen them before, and after I've set up logging), buy a larger server, or pay Second Quadrant to speed up ts_rank... I'd love any suggestions from more experienced Postgres engineers! Edit to your edit, re parsing. The subset of rows returned follows: where language = %s::regconfig and textsearchable_index_col @@ phraseto_tsquery(%s::regconfig, %s) and relevance is determined by: ts_rank_cd(textsearchable_index_col, phraseto_tsquery(%s::regconfig, %s), 32) with the %s being language and paragraph respectively.
- kristopolous 6y ago
  full-text searching is contentious, it's a rabbit-hole of holy-wars. I don't believe I'm going to summon cranks with the following: There's more specialized tools for your usecase than postgres and you should be looking into "n-gram indexing". Lucene based systems such as elasticsearch are quite popular there's also sphinx and xapian, also fairly widespread. You need to read the documentation and configure them, they are flexible and need to be tuned to this usecase. In the end, there is no "correct" way to do things. For instance, sometimes stemming words is the right way to go, but if you are say doing a medical system where two very different medicines could be spelled similar and stem to the same word, a mistake could lead to death, so no, stemming is very bad here. Sometimes stop words is the way to go, but if people are looking for quotes, such as "to be or not to be" well now you have the empty string, splendid. So yeah, configure configure configure. This may bring out the cranks: If you want to roll-your-own nosql systems like redis and mongo or couch seem to work really well (I've rolled my own in all 3 on separate occasions). I guarantee there's advanced features of maria and postgres that aren't widely used and some people reading this will confidently claim they are superior but I assure you, that is a minority opinion. Most people go with the other options. If you ever doubt it, ask the actual developers of postgres or maria on chat. They are extremely nice and way more honest about the limitations then their hardcore fans are. The databases are under constant development with advanced features and you'll learn a lot (really, they are both super chill dev communities, impressively so). Perhaps your solution (as mine has been) is a hybrid. You can store the existence for instance, in one system and the offsets and chunks in another so you get a parallelizable asynchronous worker pipeline, it's impressively fast when you horizontally scale it. <0.5 sec for multiple terabytes of text (and yes, I'm talking nvme/hundreds of gb of RAM per node/>=10gb network). I've legitimately just done random queries to marvel at the speed I really hope I save myself from the rock throwing.
  - gutensearch 6y ago
    Thanks! I really appreciate the pointers. I already had planned to explore some of these and you've expanded and directed the search nicely.
  - yorwba 6y ago