gutensearch

5년 전discuss

Interesting! I don't recall seeing this before, and the app otherwise held up to the HN hug of death which was unexpected given nothing is optimised.

Can you recall the steps that led to the error?

gutensearch

5년 전discuss

Thanks! I had the exact same problem and eventually it got me to do something about it. It is particularly bad with writers from antiquity or with a lot of popular appeal.

I've begun adding to this repository, it'll come in piece by piece as I clean up the code: https://github.com/cordb/gutensearch

gutensearch

5년 전discuss

An API is already on my roadmap! I couldn't quite figure the state of passing query parameters in the URL with Dash, though.

gutensearch

5년 전discuss

And this is why the server is in Amsterdam, even though I have had good experiences with Hetzner in the past.

I was quite sad to read about the case in 2018, and it is unfortunate that it is still not resolved.

gutensearch

5년 전discuss

Thanks for the suggestions! I will try them next.

> Looking at your where clause, did you index language and textsearchable_index_col together ? If not, you are giving the planner the opportunity to skip your full text index.

Here is the index:

  update gutenberg.paragraphs
  set textsearchable_index_col = to_tsvector(b.cfgname::regconfig, coalesce(paragraph, ' '))
  from
  gutenberg.all_data a 
  inner join pg_ts_config b on lower(a.language) = b.cfgname 
  where gutenberg.paragraphs.num = a.num;
  create index textsearch_paragraph_idx on gutenberg.paragraphs using gin (textsearchable_index_col);

> if you order by ts_rank_cd then the query will sort all rows, including those that don't match. A better pattern is to take the ranking score as a column and then sort it in a subquery. [1] from stack overlfow has an example. (As an aside, from pg2, CTEs are no longer an optimization fence, so you can write the query out more cleanly with a CTE and still get the desired performance).

Ranking was already done as a column in a CTE and ranked further down:

  with paragraphs as (
                select 
                num
                , paragraph
                , ts_rank_cd(textsearchable_index_col, phraseto_tsquery(%s::regconfig, %s), 32) as rank 
                , ts_headline(%s, paragraph, phraseto_tsquery(%s::regconfig, %s), 'MaxFragments=1000, StartSel=**, StopSel=**') as highlighted_result 
                from gutenberg.paragraphs 
                where language = %s::regconfig and textsearchable_index_col @@ phraseto_tsquery(%s::regconfig, %s)
            )

I did rewrite the query as per your [1] but EXPLAIN ANALYZE found no difference.

Pagination is a tricky one. I was in a rush and hit an issue where building large Dash tables was very expensive and prone to crashing. I initially set up pagination as per the Dash examples, but that didn't play nice with markdown which I needed for the URLs and highlighting (giving these up for plain text tables made in HN-fast, but it confused my trial users). So the quick and dirty solution "to ship now" was to set up custom pagination in the SQL and show the results.

I think that if there is enough interest in this project, the front end will have to move off Dash.

gutensearch

5년 전discuss

Thanks! I would love for an experienced designer to join the project (and DevOps, and front end, and...). My email is in the profile.

gutensearch

5년 전discuss

Thank you for your generous offer of help! I look forward to taking it up (may take a while as I'm about to move countries and quarantine).

In particular I love that one of the examples in your comment history is in Latin as that language is not currently supported by Postgres FTS. Are Latin and Ancient Greek supported by Manticore? (dare I hope for Anglo Saxon...)

gutensearch

5년 전discuss

Thank you for the suggestion! The demo looks great and I'm curious to see how they automatically handle language. It would be nice to add support for Chinese which is the great absent from this attempt, even at the cost of several other languages which in any case have few books transcribed to text in Gutenberg.

I will probably write a blog post once I've tried a few of the approaches suggested in this thread.

gutensearch

5년 전discuss

Thanks! I really appreciate the pointers. I already had planned to explore some of these and you've expanded and directed the search nicely.

gutensearch

5년 전discuss

Thanks!

There's a workaround to the dropdown issue, see this other comment thread: https://news.ycombinator.com/item?id=25890458

gutensearch

5년 전discuss

Same place as the app: a Start-2-M-SSD from online.net in their AMS1 DC (Amsterdam).

Subset of sudo lshw --short:

  Class          Description
  ======================================================
  processor      Intel(R) Atom(TM) CPU  C2750  @ 2.40GHz
  memory         16GiB System Memory
  disk           256GB Micron_1100_MTFD

gutensearch

5년 전discuss

Thank you for taking the time to lay out feature requests in such details! I really appreciate it.

The current search box is a wrapper around Postgres phraseto_tsquery [1] whilst the Discovery tab uses plainto_tsquery, so you could play with either as an ersatz for some of these features for now, although special characters might get stripped or parsed incorrectly.

Do you know where the people you are talking about hang out online (for example, subreddits)? I'd love to get in touch with them once the features are built and for more general feedback.

[1] https://www.postgresql.org/docs/12/textsearch-controls.html

gutensearch

5년 전discuss

Thanks for the bug report!

It has to do (I think) with Dash's columnar layout which unfolds the menu over the next few columns at least in Chrome.

The quick workaround I found was to type out the language until it appears below and click on it or finish typing, then press enter. This should select it.

I'd love to hear from other Dash developers who've had, and solved this issue.

gutensearch

5년 전discuss

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.

gutensearch

5년 전discuss

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

5년 전discuss

That was definitely part of the original plan! I spotted two other attempts [1] [2] here using BERT and ElasticSearch respectively.

The main performance issue with the Postgres FTS approach (possibly also the others?) is ranking. Matching results uses the index, but ts_rank cannot.

Most of the time, few results are returned and the front end gets its answer in ~300ms including formatting the text for the front end (~20ms without).

However, a reasonably common sentence will return tens or hundreds of thousands of rows, which takes a minute or more to get ranked. In production, this could be worked around by tracking and caching such queries if they are common enough.

I'd love to hear from anyone experienced with the other options (Lucene, Solr, ElasticSearch, etc.) whether and how they get around this.

[1] https://news.ycombinator.com/item?id=19095963

[2] https://news.ycombinator.com/item?id=6562126 (the link does not load for me)

gutensearch

Submissions

Show HN: Full text search Project Gutenberg (60m paragraphs)

comments