11 boolean search
Post on 02-Sep-2015
225 Views
Preview:
DESCRIPTION
TRANSCRIPT
-
Elementary IR: Scalable Boolean Text Search
(Compare with R & G 27.1-3)
-
Information Retrieval: HistoryA research field traditionally separate from DatabasesHans P. Luhn, IBM, 1959: Keyword in Context (KWIC)G. Salton at Cornell in the 60s/70s: SMARTAround the same time as relational DB revolutionTons of research since thenEspecially in the web eraProducts traditionally separateOriginally, document management systems for libraries, government, law, etc.Gained prominence in recent years due to web searchStill used for non-web document management. (Enterprise search).
-
Today: Simple (nave!) IRBoolean Search on keywordsGoal:Show that you already have the tools to do this from your study of relational DBsWell skip:Text-oriented storage formatsIntelligent result ranking (hopefully later!)ParallelismCritical for modern relational DBs tooVarious bells and whistles (lots of little ones!)Engineering the specifics of (written) human languageE.g. dealing with tense and pluralsE.g. identifying synonyms and related wordsE.g. disambiguating multiple meanings of a wordE.g. clustering output
-
IR vs. DBMSSeem like very different beasts
Under the hood, not as different as they might seemBut in practice, you have to choose between the 2 today
IRDBMSImprecise SemanticsPrecise SemanticsKeyword searchSQLUnstructured data formatStructured dataRead-Mostly. Add docs occasionallyExpect reasonable number of updatesPage through top k resultsGenerate full answer
-
IRs Bag of Words ModelTypical IR data model:Each document is just a bag of words (terms)Detail 1: Stop WordsCertain words are not helpful, so not placed in the bage.g. real words like the e.g. HTML tags like Detail 2: StemmingUsing language-specific rules, convert words to basic form e.g. surfing, surfed --> surfUnfortunately have to do this for each languageYuck!
-
Boolean Text SearchFind all documents that match a Boolean containment expression:Windows AND (Glass OR Door) AND NOT MicrosoftNote: query terms are also filtered via stemming and stop wordsWhen web search engines say 10,000 documents found, thats the Boolean search result sizeMore or less ;-)
-
Text IndexesWhen IR folks say text indexusually mean more than what DB people meanIn our terms, both tables and indexesReally a logical schema (i.e. tables)With a physical schema (i.e. indexes)Usually not stored in a DBMSTables implemented as files in a file systemWell talk more about this decision soon
-
A Simple Relational Text IndexGiven: a corpus of text filesFiles(docID string, content string)Create and populate a tableInvertedFile(term string, docID string)Build a B+-tree or Hash index on InvertedFile.termSomething like Alternative 3 critical here!! Keep lists of dup keys sorted by docIDWill provide interesting orders later on!Fancy list compression important, tooTypically called a postings list by IR peopleNote: URL instead of RID, the web is your heap file!Can also cache pages and use RIDsThis is often called an inverted file or inverted indexMaps from words -> docs, rather than docs -> words
Given this, you can now do single-word text search queries!
-
An Inverted FileSnippets from:Old class web pageOld microsoft.com home pageSearch fordatabasesmicrosoft
TermdocID
Sheet1
termdocURL
datahttp://www-inst.eecs.berkeley.edu/~cs186
databasehttp://www-inst.eecs.berkeley.edu/~cs186
datehttp://www-inst.eecs.berkeley.edu/~cs186
dayhttp://www-inst.eecs.berkeley.edu/~cs186
dbmshttp://www-inst.eecs.berkeley.edu/~cs186
decisionhttp://www-inst.eecs.berkeley.edu/~cs186
demonstratehttp://www-inst.eecs.berkeley.edu/~cs186
descriptionhttp://www-inst.eecs.berkeley.edu/~cs186
designhttp://www-inst.eecs.berkeley.edu/~cs186
desirehttp://www-inst.eecs.berkeley.edu/~cs186
developerhttp://www.microsoft.com
differhttp://www-inst.eecs.berkeley.edu/~cs186
disabilityhttp://www.microsoft.com
discussionhttp://www-inst.eecs.berkeley.edu/~cs186
divisionhttp://www-inst.eecs.berkeley.edu/~cs186
dohttp://www-inst.eecs.berkeley.edu/~cs186
documenthttp://www-inst.eecs.berkeley.edu/~cs186
documenthttp://www.microsoft.com
microsofthttp://www.microsoft.com
microsofthttp://www-inst.eecs.berkeley.edu/~cs186
midnighthttp://www-inst.eecs.berkeley.edu/~cs186
midtermhttp://www-inst.eecs.berkeley.edu/~cs186
minibasehttp://www-inst.eecs.berkeley.edu/~cs186
millionhttp://www.microsoft.com
Mondayhttp://www.microsoft.com
morehttp://www.microsoft.com
mosthttp://www-inst.eecs.berkeley.edu/~cs186
mshttp://www-inst.eecs.berkeley.edu/~cs186
msnhttp://www.microsoft.com
musthttp://www-inst.eecs.berkeley.edu/~cs186
necessaryhttp://www-inst.eecs.berkeley.edu/~cs186
needhttp://www-inst.eecs.berkeley.edu/~cs186
networkhttp://www.microsoft.com
newhttp://www-inst.eecs.berkeley.edu/~cs186
newhttp://www.microsoft.com
newshttp://www.microsoft.com
newsgrouphttp://www-inst.eecs.berkeley.edu/~cs186
newsletterhttp://www.microsoft.com
nowhttp://www.microsoft.com
ofhttp://www.microsoft.com
officehttp://www.microsoft.com
phttp://www.microsoft.com
pacifichttp://www.microsoft.com
pagehttp://www.microsoft.com
partnerhttp://www.microsoft.com
personalhttp://www.microsoft.com
portalhttp://www.microsoft.com
presshttp://www.microsoft.com
privacyhttp://www.microsoft.com
producthttp://www.microsoft.com
professionalhttp://www.microsoft.com
http://www.microsoft.com
http://www.microsoft.com
http://www-inst.eecs.berkeley.edu/~cs186
http://www-inst.eecs.berkeley.edu/~cs186
http://www.microsoft.com
Sheet2
Sheet3
Sheet1
termdocURL
datahttp://www-inst.eecs.berkeley.edu/~cs186
databasehttp://www-inst.eecs.berkeley.edu/~cs186
datehttp://www-inst.eecs.berkeley.edu/~cs186
dayhttp://www-inst.eecs.berkeley.edu/~cs186
dbmshttp://www-inst.eecs.berkeley.edu/~cs186
decisionhttp://www-inst.eecs.berkeley.edu/~cs186
demonstratehttp://www-inst.eecs.berkeley.edu/~cs186
descriptionhttp://www-inst.eecs.berkeley.edu/~cs186
designhttp://www-inst.eecs.berkeley.edu/~cs186
desirehttp://www-inst.eecs.berkeley.edu/~cs186
developerhttp://www.microsoft.com
differhttp://www-inst.eecs.berkeley.edu/~cs186
disabilityhttp://www.microsoft.com
discussionhttp://www-inst.eecs.berkeley.edu/~cs186
divisionhttp://www-inst.eecs.berkeley.edu/~cs186
dohttp://www-inst.eecs.berkeley.edu/~cs186
documenthttp://www-inst.eecs.berkeley.edu/~cs186
documenthttp://www.microsoft.com
microsofthttp://www.microsoft.com
microsofthttp://www-inst.eecs.berkeley.edu/~cs186
midnighthttp://www-inst.eecs.berkeley.edu/~cs186
midtermhttp://www-inst.eecs.berkeley.edu/~cs186
minibasehttp://www-inst.eecs.berkeley.edu/~cs186
millionhttp://www.microsoft.com
mondayhttp://www.microsoft.com
morehttp://www.microsoft.com
mosthttp://www-inst.eecs.berkeley.edu/~cs186
mshttp://www-inst.eecs.berkeley.edu/~cs186
msnhttp://www.microsoft.com
musthttp://www-inst.eecs.berkeley.edu/~cs186
necessaryhttp://www-inst.eecs.berkeley.edu/~cs186
needhttp://www-inst.eecs.berkeley.edu/~cs186
networkhttp://www.microsoft.com
newhttp://www-inst.eecs.berkeley.edu/~cs186
newhttp://www.microsoft.com
newshttp://www.microsoft.com
newsgrouphttp://www-inst.eecs.berkeley.edu/~cs186
newsletterhttp://www.microsoft.com
nowhttp://www.microsoft.com
ofhttp://www.microsoft.com
officehttp://www.microsoft.com
phttp://www.microsoft.com
pacifichttp://www.microsoft.com
pagehttp://www.microsoft.com
partnerhttp://www.microsoft.com
personalhttp://www.microsoft.com
portalhttp://www.microsoft.com
presshttp://www.microsoft.com
privacyhttp://www.microsoft.com
producthttp://www.microsoft.com
professionalhttp://www.microsoft.com
http://www.microsoft.com
http://www.microsoft.com
http://www-inst.eecs.berkeley.edu/~cs186
http://www-inst.eecs.berkeley.edu/~cs186
http://www.microsoft.com
Sheet2
Sheet3
-
Handling Boolean LogicHow to do term1 OR term2?Union of two postings lists (docID sets)!How to do term1 AND term2?Intersection of two postings lists!Can be done via merge-join over postings listsRemember: postings list per key sorted by docID in indexHow to do term1 AND NOT term2?Set subtractionAlso easy because sorted (basically merge join logic again)How to do term1 OR NOT term2Union of term1 and NOT term2.Not term2 = all docs not containing term2. Yuck!Usually not allowed!Query Optimization: what order to handle terms if you have many ANDs?
-
Boolean Search in SQL(SELECT docID FROM InvertedFile WHERE word = window INTERSECT SELECT docID FROM InvertedFile WHERE word = glass OR word = door) EXCEPT SELECT docID FROM InvertedFile WHERE word=Microsoft ORDER BY magic_rank() Theres only one SQL query template in Boolean SearchSingle-table selects, UNION, INTERSECT, EXCEPTmagic_rank() is the secret sauce in the search enginesHopefully well study this later in the semesterCombos of statistics, linguistics, and graph theory tricks!
Windows AND (Glass OR Door) AND NOT Microsoft
-
One step fancier: Phrases and NearSuppose you want a phrase E.g. Happy DaysDifferent schema:InvertedFile (term string, position int, docID string)Alternative 3 index on termPostings lists sorted by (docID, position)Post-process the resultsFind Happy AND DaysKeep results where positions are 1 offCan be done during merge-join to AND the 2 lists!Can do a similar thing for term1 NEAR term2Position < k offThink about refinement to merge-join
-
Somewhat better compressionInvertedFile (term string, position int, docID int)Files(docID int, docID string, snippet string, )Btree on InvertedFile.termBtree on Docs.docID
Requires a final join step between typical query result and Files.docIDCan do this lazily: cursor to generate a page full of results
-
Updates and Text SearchText search engines are designed to be query-mostlyDeletes and modifications are rareCan postpone updates (nobody notices, no transactions!)Can work off a union of indexesMerge them in batch (typically re-bulk-load a new index)Cant afford to go offline for an update?Create a 2nd index on a separate machineReplace the 1st index with the 2nd!So no concurrency control problemsCan compress to search-friendly, update-unfriendly formatCan keep postings lists sortedFor these reasons, text search engines and DBMSs are usually separate productsAlso, text-search engines tune that one SQL query to death!The benefits of a special-case workload.
-
Lots more tricks in IRHow to rank the output?A mix of simple tricks works wellSome fancier tricks can help (use hyperlink graph)Other ways to help users paw through the output?Document clustering (e.g. Clusty.com)Document visualizationHow to use compression for better I/O performance?E.g. making postings lists smallerTry to make things fit in RAMOr in processor cachesHow to deal with synonyms, misspelling, abbreviations? How to write a good web crawler?
Well return to some of these laterThe book Managing Gigabytes covers some of the details
-
Recall From the First Lecture{The Access MethodBuffer ManagementDisk Space ManagementDBOSThe QuerySearch String Modifier
Simple DBMS}Ranking AlgorithmConcurrency and Recovery NeededDBMSSearch Engine
-
You Know The Basics!Inverted files are the workhorses of all text search enginesJust B+-tree or Hash indexes on bag-of-wordsIntersect, Union and Set Difference (Except)Usually implemented via sortingOr can be done with hash or index joinsMost of the other stuff is not systems workA lot of it is cleverness in dealing with languageBoth linguistics and statistics (more the latter!)
-
Revisiting Our IR/DBMS DistinctionsSemantic Guarantees on StorageDBMS guarantees transactional semanticsIf an inserting transaction commits, a subsequent query will see the updateHandles multiple concurrent updates correctlyIR systems do not do this; nobody notices!Postpone insertions until convenientNo model of correct concurrency.Can even return incorrect answers for various reasons!Data Modeling & Query ComplexityDBMS supports any schema & queriesBut requires you to define schemaAnd SQL is hard to figure out for the average citizenIR supports only one schema & queryNo schema design required (unstructured text)Trivial (natural?) query language for simple tasksNo data correlation or analysis capabilities -- search only
-
Revisiting Distinctions, Cont.Performance goalsDBMS supports general SELECTplus mix of INSERT, UPDATE, DELETEgeneral purpose engine must always perform wellIR systems expect only one stylized SELECTplus delayed INSERT, unusual DELETE, no UPDATE.special purpose, must run super-fast on The Queryusers rarely look at the full answer in Boolean SearchPostpone any work you can to subsequent index joinsBut make sure you can rank!
-
SummaryIR & Relational systems share basic building blocks for scalabilityIR internal representation is relational!Equality indexes (B-trees)IteratorsJoin algorithms, esp. merge-joinJoin ordering and selectivity estimationIR constrains queries, schema, promises on semanticsAffects storage format, indexing and concurrency controlAffects join algorithms & selectivity estimationIR has different performance goalsRanking and best answers fastMany challenges in IR related to text engineeringBut dont tend to change the scalability infrastructure
-
IR Buzzwords to Know (so far!)Learning this in the context of relational foundations is fine, but you need to know the IR lingo!Corpus: a collection of documentsTerm: an isolated string (searchable unit)Index: a mechanism mapping terms to documentsInverted File (= Postings File): a file containing terms and associated postings listsPostings List: a list of pointers (postings) to documents
-
Exercise!Implement Boolean search directly in PostgresUsing the schemas and indexes here. Write a simple script to load files.You can ignore stemming and stop-words.Run the SQL versions of Boolean queriesMeasure how slow search is in PostgresIdentify contributing factors in performanceE.g. how much disk space does the postgres version use (including indexes) vs. the raw documents vs. the documents gzipedE.g. is PG identifying the interesting orders in the postings lists? (use EXPLAIN) If not, can you force it to do so?Compare PG to an idealized implementationCalculate the idealized size of the InvertedFile table for your dataUse the cost models for IndexScan and MergeJoin to calculate the expected number of IOs. Distinguish sequential and random Ios.Why is PG slow? Storage overhead? Optimizer smarts?
top related