Downloaded 1,303 times
![Lucene: Querying • Parse a Lucene query define fields String[] fields = new String[3]; fields[0] = “title”; fields[1] = “body”; fields[2] = “tags”; Query q = new MultiFieldQueryParser(fields, new StandardAnalyzer()).parse(‘performance’); • Execute the query parse search query Searcher s = new IndexSearcher(indexName); Hits h = s.search(q); time: 80 milliseconds](https://image.slidesharecdn.com/fulltextsearchinpostgresql-091103175457-phpapp02/75/Full-Text-Search-In-PostgreSQL-29-2048.jpg)
Uploaded byKarwin Software Solutions LLC
Full Text Search In PostgreSQL
The document discusses practical full-text search solutions in PostgreSQL, highlighting various methods including with and without indexing using systems like Sphinx and Apache Lucene. It contrasts the performance of different search methods, emphasizing the importance of appropriate indexing for efficiency and accuracy in querying large datasets. The document concludes with a comparison of indexing speeds and storage requirements across different systems, providing insights into the best practices for full-text search implementation.
In this document
Powered by AI
Overview of full-text search capabilities in PostgreSQL by Bill Karwin, highlighting experience and background.
Explains the importance of full-text search in web applications and sets the context for comparing five search solutions.
Introduction to sample data with 1.2 million tuples from StackOverflow posts to be used in search comparisons.
Discusses the inefficiencies of naive searching methods using regular expressions and LIKE queries.
Highlights the problems of irrelevant matches in traditional searches and the need for improved accuracy.
Introduces various full-text searching solutions including Full-Text Indexing and Inverted Indexes.
Details on PostgreSQL's text search features introduced since version 8.3, including TSVECTOR and TSQUERY.
Demonstrates how to perform basic text search queries in PostgreSQL with time taken for indexing.
Instructions on adding a TSVECTOR column to the Posts table for efficient text searching.
Overview of special index types available in PostgreSQL such as GIN and GiST.
How to create indexes for text search in PostgreSQL and compare query times with and without indexes.
Setup of a trigger to synchronize TSVECTOR data upon insertions and updates.
Introduction to the Lucene full-text search engine, its features, and its programming language compatibility.
Steps on how to use Lucene for indexing data with Java, including SQL integration details.
Details on how to parse and execute queries using Lucene with emphasis on query execution time.
Introduction to Sphinx Search, a full-text search engine with a focus on database integration.
Steps to configure, index, and query with Sphinx, including performance metrics for indexing time.
Basic explanation of inverted indexes and their role in search functionality, revised ER diagram included.
Data definitions and SQL implementations for creating and querying inverted indexes efficiently.
Overview of search engine services, particularly Google Custom Search Engine and its suitability.
Comparative analysis of different search solutions based on indexing time, storage space, and query speed.
Copyright notice regarding the presentation and guidelines for sharing this work.
Full Text Search In PostgreSQL
- 1. Practical full-text search in PostgreSQL BillKarwin PostgreSQL Conference West 09 • 2009/10/17
- 2. Me • 20+ yearsexperience • Application/SDK developer • Support, Training, Proj Mgmt • C, Java, Perl, PHP • SQL maven • MySQL, PostgreSQL, InterBase • Zend Framework • Oracle, SQL Server, IBM DB2, SQLite • Community contributor
- 3.
- 4. Text search • Webapplications demand speed • Let’s compare 5 solutions for text search
- 5. Sample data • StackOverflow.comPosts • Data dump exported September 2009 • 1.2 million tuples • ~850 MB
- 6.
- 7. Naive Searching Some people,when confronted with a problem, think “I know, I’ll use regular expressions.” Now they have two problems. — Jamie Zawinsky
- 8. Performance issue • LIKEwith wildcards: time: 91 sec SELECT * FROM Posts WHERE body LIKE ‘%postgresql%’ • POSIX regular expressions: SELECT * FROM Posts WHERE body ~ ‘postgresql’ time: 105 sec
- 9. Why so slow? CREATETABLE telephone_book ( full_name VARCHAR(50) ); CREATE INDEX name_idx ON telephone_book (full_name); INSERT INTO telephone_book VALUES (‘Riddle, Thomas’), (‘Thomas, Dean’);
- 10. Why so slow? •Search for all with last name “Thomas” uses SELECT * FROM telephone_book index WHERE full_name LIKE ‘Thomas%’ • Search for all with first name “Thomas” SELECT * FROM telephone_book WHERE full_name LIKE ‘%Thomas’ doesn’t use index
- 11. Indexes don’t help searchingfor substrings
- 12. Accuracy issue • Irrelevantor false matching words ‘one’, ‘money’, ‘prone’, etc.: body LIKE ‘%one%’ • Regular expressions in PostgreSQL support escapes for word boundaries: body ~ ‘yoney’
- 13. Solutions • Full-Text Indexingin the RDBMS • Sphinx Search • Apache Lucene • Inverted Index • Search Engine Service
- 14.
- 15. PostgreSQL Text-Search • SincePostgreSQL 8.3 • TSVECTOR to represent text data • TSQUERY to represent search predicates • Special indexes
- 16. PostgreSQL Text-Search: Basic Querying SELECT * FROM Posts WHERE to_tsvector(title || ‘ ’ || body || ‘ ’ || tags) @@ to_tsquery(‘postgresql & performance’); text-search matching operator
- 17. PostgreSQL Text-Search: Basic Querying SELECT * FROM Posts WHERE title || ‘ ’ || body || ‘ ’ || tags @@ ‘postgresql & performance’; time with no index: 8 min 2 sec
- 18. PostgreSQL Text-Search: Add TSVECTOR column ALTER TABLE Posts ADD COLUMN PostText TSVECTOR; UPDATE Posts SET PostText = to_tsvector(‘english’, title || ‘ ’ || body || ‘ ’ || tags);
- 19. Special index types •GIN (generalized inverted index) • GiST (generalized search tree)
- 20. PostgreSQL Text-Search: Indexing CREATE INDEX PostText_GIN ON Posts USING GIN(PostText); time: 39 min 36 sec
- 21. PostgreSQL Text-Search: Querying SELECT * FROM Posts WHERE PostText @@ ‘postgresql & performance’; time with index: 20 milliseconds
- 22. PostgreSQL Text-Search: Keep TSVECTOR in sync CREATE TRIGGER TS_PostText BEFORE INSERT OR UPDATE ON Posts FOR EACH ROW EXECUTE PROCEDURE tsvector_update_trigger( ostText, P ‘english’, title, body, tags);
- 23.
- 24. Lucene • Full-text indexingand search engine • Apache Project since 2001 • Apache License • Java implementation • Ports exist for C, Perl, Ruby, Python, PHP, etc.
- 25. Lucene: How to use 1. Add documents to index 2. Parse query 3. Execute query
- 26. Lucene: Creating an index • Programmatic solution in Java... time: 8 minutes 55 seconds
- 27. Lucene: Indexing String url = "jdbc:postgresql:stackoverflow"; Properties props = new Properties(); props.setProperty("user", "postgres"); run any SQL query Class.forName("org.postgresql.Driver"); Connection con = DriverManager.getConnection(url, props); Statement stmt = con.createStatement(); String sql = "SELECT PostId, Title, Body, Tags FROM Posts"; ResultSet rs = stmt.executeQuery(sql); open Lucene Date start = new Date(); index writer IndexWriter writer = new IndexWriter(FSDirectory.open(INDEX_DIR), new StandardAnalyzer(Version.LUCENE_CURRENT), true, IndexWriter.MaxFieldLength.LIMITED);
- 28. Lucene: Indexing loop over SQL result while (rs.next()) { Document doc = new Document(); doc.add(new Field("PostId", rs.getString("PostId"), Field.Store.YES, Field.Index.NO)); doc.add(new Field("Title", rs.getString("Title"), Field.Store.YES, Field.Index.ANALYZED)); doc.add(new Field("Body", rs.getString("Body"), Field.Store.YES, Field.Index.ANALYZED)); doc.add(new Field("Tags", rs.getString("Tags"), Field.Store.YES, Field.Index.ANALYZED)); writer.addDocument(doc); each row is } a Document writer.optimize(); writer.close(); with four Fields finish and close index
- 29. Lucene: Querying • Parse a Lucene query define fields String[] fields = new String[3]; fields[0] = “title”; fields[1] = “body”; fields[2] = “tags”; Query q = new MultiFieldQueryParser(fields, new StandardAnalyzer()).parse(‘performance’); • Execute the query parse search query Searcher s = new IndexSearcher(indexName); Hits h = s.search(q); time: 80 milliseconds
- 30.
- 31. Sphinx Search • Embeddedfull-text search engine • Started in 2001 • GPLv2 license • Good database integration
- 32. Sphinx Search: How to use 1. Edit configuration file 2. Index the data 3. Query the index 4. Issues
- 33. Sphinx Search: sphinx.conf source stackoverflowsrc { type = pgsql sql_host = localhost sql_user = postgres sql_pass = xxxx sql_db = stackoverflow sql_query = SELECT PostId, Title, Body, Tags FROM Posts sql_query_info = SELECT * FROM Posts WHERE PostId=$id }
- 34. Sphinx Search: sphinx.conf index stackoverflow { source = stackoverflowsrc path = /opt/local/var/db/sphinx/stackoverflow }
- 35. Sphinx Search: Building index indexer -c sphinx.conf stackoverflow collected 1242365 docs, 720.5 MB sorted 88.3 Mhits, 100.0% done total 1242365 docs, 720452944 bytes total 357.647 sec, 2014423.75 bytes/sec, 3473.72 docs/sec time: 5 min 57 sec
- 36. Sphinx Search: Querying index search -c sphinx.conf -i stackoverflow -b “sql & performance” time: 8 milliseconds
- 37. Sphinx Search: Issues • Index updates are as expensive as rebuilding the index from scratch • Maintain “main” index plus “delta” index for recent changes • Merge indexes periodically • Not all data fits into this model
- 38.
- 39. Inverted index searchable words Posts Tags TagTypes intersection of words / Posts
- 40. Inverted index: Updated ERDiagram
- 41. Inverted index: Data definition CREATE TABLE TagTypes ( TagId SERIAL PRIMARY KEY, Tag VARCHAR(50) NOT NULL ); CREATE UNIQUE INDEX TagTypes_Tag_index ON TagTypes(Tag); CREATE TABLE Tags ( PostId INT NOT NULL, TagId INT NOT NULL, PRIMARY KEY (PostId, TagId), FOREIGN KEY (PostId) REFERENCES Posts (PostId), FOREIGN KEY (TagId) REFERENCES TagTypes (TagId) ); CREATE INDEX Tags_PostId_index ON Tags(PostId); CREATE INDEX Tags_TagId_index ON Tags(TagId);
- 42. Inverted index: Indexing INSERT INTO Tags (PostId, TagId) SELECT p.PostId, t.TagId FROM Posts p JOIN TagTypes t ON (p.Tags LIKE ‘%<’ || t.Tag || ‘>%’); 90 seconds per tag!!
- 43. Inverted index: Querying SELECT p.* FROM Posts p JOIN Tags t USING (PostId) JOIN TagTypes tt USING (TagId) WHERE tt.Tag = ‘performance’; 40 milliseconds
- 44.
- 45. Search engine services: GoogleCustom Search Engine • http://www.google.com/cse/ • DEMO ➪ http://www.karwin.com/demo/gcse-demo.html even big web sites use this solution
- 46. Search engine services: Is it right for you? • Your site is public and allows external index • Search is a non-critical feature for you • Search results are satisfactory • You need to offload search processing
- 47. Comparison: Time toBuild Index LIKE predicate none PostgreSQL / GIN 40 min Sphinx Search 6 min Apache Lucene 9 min Inverted index high Google / Yahoo! offline
- 48. Comparison: Index Storage LIKEpredicate none PostgreSQL / GIN 532 MB Sphinx Search 533 MB Apache Lucene 1071 MB Inverted index 101 MB Google / Yahoo! offline
- 49. Comparison: Query Speed LIKEpredicate 90+ sec PostgreSQL / GIN 20 ms Sphinx Search 8 ms Apache Lucene 80 ms Inverted index 40 ms Google / Yahoo! *
- 50. Comparison: Bottom-Line indexing storage query solution LIKE predicate none none 11,250x SQL PostgreSQL / GIN 7x 5.3x 2.5x RDBMS Sphinx Search 1x * 5.3x 1x 3rd party Apache Lucene 1.5x 10x 10x 3rd party Inverted index high 1x 5x SQL Google / Yahoo! offline offline * Service
- 51. Copyright 2009 BillKarwin www.slideshare.net/billkarwin Released under a Creative Commons 3.0 License: http://creativecommons.org/licenses/by-nc-nd/3.0/ You are free to share - to copy, distribute and transmit this work, under the following conditions: Attribution. Noncommercial. No Derivative Works. You must attribute this You may not use this work You may not alter, work to Bill Karwin. for commercial purposes. transform, or build upon this work.