CS194-3/CS16x Introduction to Systems Lecture 26 Elementary Information Retrieval: Scalable Boolean Text Search

December 3, 2007 Prof. Anthony D. Joseph http://www.cs.berkeley.edu/~adj/cs16x

Goals for Today

• Join techniques – Nested-loops join – Sort-merge join • Boolean text search – You’ve already seen a parallel version of this in

Project 2 (from the systems view) Note: Some slides and/or pictures in the following are adapted from slides ©2005 Silberschatz, Galvin, and Gagne. Slides courtesy of Kubiatowicz, AJ Shankar, George Necula, Alex Aiken, Eric Brewer, Ras Bodik, Ion Stoica, Doug Tygar, and David Wagner.

12/3/07 Joseph CS194-3/16x ©UCB Fall 2007 Lec 26.2

Schema for Examples

Sailors (sid: integer, sname: string, rating: integer, age: real) Reserves (sid: integer, bid: integer, day: dates, rname: string)

• Sailors: – Each tuple is 50 bytes long, 80 tuples per page, 500

pages.

– |S|=500, p

S =80.

• Reserves: – Each tuple is 40 bytes, 100 tuples per page, 1000

pages.

– |R|=1000, p

R =100.

12/3/07 Joseph CS194-3/16x ©UCB Fall 2007 Lec 26.3

Joins

SELECT FROM WHERE * Reserves R1, Sailors S1 R1.sid=S1.sid

• Joins are very common • R 

S is large; so, R



S followed by a selection is inefficient

• Many approaches to reduce join cost • Join techniques we will cover today:

1. Nested-loops join 2. Sort-merge join 12/3/07 Joseph CS194-3/16x ©UCB Fall 2007 Lec 26.4

Simple Nested Loops Join

foreach tuple r in R do foreach tuple s in S do if r i == s j then add to result

Cost =

(p R *|R|)*(p S *|S|)+|R|

=

100 *1000* 80 *500+1000 IOs

– At 10ms/IO,

Total time: ???

• What if smaller relation (S) was “outer”? – Cost =

(p S *|S|)*|R| + |S| = 80 *500*1000 + 500 IOs

• What assumptions are being made here? • What is cost if one relation can fit entirely in memory?

12/3/07 Joseph CS194-3/16x ©UCB Fall 2007 Lec 26.5

Page-Oriented Nested Loops Join

foreach page foreach b R page in R do b S in S do foreach tuple foreach tuple if r i r in b R do s in b S do == s j then add to result

Cost =

|R|*|S| + |R|

=

1000*500 + 1000

• If smaller relation (S) is outer, cost = 500*1000 + 500 • Much better than naïve per-tuple approach!

12/3/07 Joseph CS194-3/16x ©UCB Fall 2007 Lec 26.6

Sort-Merge Join

Example: SELECT FROM WHERE * Reserves R1, Sailors S1 R1.sid=S1.sid

1. Sort R on join attr(s) 2. Sort S on join attr(s) 3. Scan sorted-R and sorted-S in tandem, to find matches

sid 22 28 31 44 58

12/3/07

sname dustin yuppy lubber guppy rusty sid bid day rating 7 9 8 5 10 age 45.0

35.0

55.5

35.0

35.0

28 103 12/4/07 28 103 11/3/07 31 101 10/10/07 31 102 10/12/07 31 101 10/11/07 58 103 11/12/07

Joseph CS194-3/16x ©UCB Fall 2007

rname guppy yuppy dustin lubber lubber dustin

Lec 26.7

Cost of Sort-Merge Join

• Cost: Sort R + Sort S + (|R|+|S|) – But in worst case, last term could be |R|*|S| (very unlikely!) – Q: what is worst case?

Suppose B = 35 buffer pages:

• Both R and S can be sorted in 2 passes • Total join cost = 4*1000 + 4*500 + (1000 + 500) =

7500 Suppose B = 300 buffer pages:

• Again, both R and S sorted in 2 passes • Total join cost =

7500 12/3/07 Joseph CS194-3/16x ©UCB Fall 2007 Lec 26.8

Other Considerations …

1.

An important refinement:

Do the join during the final merging pass of sort !

• If have enough memory, can do:

1. Read R and write out sorted runs 2. Read S and write out sorted runs



Cost = 3*|R| + 3*|S| 2.

Sort-merge join an especially good choice if:

– One or both inputs are

already sorted on join attribute(s)

– Output is

required to be sorted on join attributes(s) Q: how to take these savings into account?

12/3/07 Joseph CS194-3/16x ©UCB Fall 2007

(stay tuned …)

Lec 26.9

BREAK

Information Retrieval: History

• A research field traditionally separate from

Databases

– Hans P. Luhn, IBM, 1959: “Keyword in Context (KWIC)” – G. Salton at Cornell in the 60’s/70’s: SMART » Around the same time as relational DB revolution – Tons of research since then » Especially in the web era • Products traditionally separate – Originally, document management systems for libraries,

government, law, etc.

– Gained prominence in recent years due to web search » Still used for non-web document management – “Enterprise

search” and “Desktop search” 12/3/07 Joseph CS194-3/16x ©UCB Fall 2007 Lec 26.11

Today: Simple (naïve!) IR

• Boolean Search on keywords • Goal: – See how IR relates to the tools we use in relational DBs • We’ll skip: – Text-oriented storage formats – Intelligent result ranking – Parallelism (you’ve seen this already in Project 2) » Critical for modern relational DBs too – Various bells and whistles (lots of little ones!) » Engineering the specifics of (written) human language • E.g., dealing with tense and plurals • E.g., identifying synonyms and related words • E.g., disambiguating multiple meanings of a word • E.g., clustering output

12/3/07 Joseph CS194-3/16x ©UCB Fall 2007 Lec 26.12

Information Retrieval vs. DBMS

• Seem like very different beasts

Information Retrieval Imprecise Semantics Keyword search Unstructured data format Read-Mostly. Add docs occasionally Page through top k results DBMS Precise Semantics SQL Structured data Expect reasonable number of updates Generate full answer

• Under the hood, not as different as they might

seem

– But in practice, you have to choose between the

two today

12/3/07 Joseph CS194-3/16x ©UCB Fall 2007 Lec 26.13

IR’s “Bag of Words” Model

• Typical IR data model: – Each document is just a bag of words (“terms”) • Detail 1: “Stop Words” – Certain words are not helpful, so not placed in the

bag

– e.g., real words like “the” – e.g., HTML tags like

• Detail 2: “Stemming” – Using language-specific rules, convert words to

basic form

– e.g., “surfing”, “surfed” –> “surf” – Unfortunately have to do this for each language » Yuck!

12/3/07 Joseph CS194-3/16x ©UCB Fall 2007 Lec 26.14

Boolean Text Search

• Find all documents that match a Boolean

containment expression:

– “Windows”

AND (“Glass” OR “Door”) AND NOT “Microsoft”

• Note: query terms may also be filtered via

stemming and stop words

• When web search engines say “10,000 documents

found”, that’s the Boolean search result size

– More or less ;-)

12/3/07 Joseph CS194-3/16x ©UCB Fall 2007 Lec 26.15

Text “Indexes”

• When IR folks say “text index”… – Usually mean more than what DB people mean • In DB terms, both “tables” and indexes – Really a logical schema (i.e., tables) – With a physical schema (i.e., indexes) – Usually not stored in a DBMS » Tables implemented as files in a file system » We’ll talk more about this decision soon

12/3/07 Joseph CS194-3/16x ©UCB Fall 2007 Lec 26.16

A Simple Relational Text Index (non-Parallel Project 2)

• Given: a corpus of text files

Files(docID string, content string)

• Create and populate a table

InvertedFile(term string, docID string)

• Build a B+-tree or Hash index on

InvertedFile.term

– Keep lists of dup keys sorted by docID » Will provide “interesting orders” later on! – Fancy list compression important, too – Typically called a postings list by IR people • Often called an “inverted file” or “inverted index” – Maps words -> docs, rather than docs -> words • Given this, you can now do single-word text search

queries!

12/3/07 Joseph CS194-3/16x ©UCB Fall 2007 Lec 26.17

An Inverted File

Term docID

• Snippets from: – Old CS186 class

web page

– Old microsoft.com

home page

• Search for – databases – microsoft

12/3/07

data database date day dbms decision demonstrate description design desire developer differ disability discussion division do document document http://www-inst.eecs.berkeley.edu/~cs186 http://www-inst.eecs.berkeley.edu/~cs186 http://www-inst.eecs.berkeley.edu/~cs186 http://www-inst.eecs.berkeley.edu/~cs186 http://www-inst.eecs.berkeley.edu/~cs186 http://www-inst.eecs.berkeley.edu/~cs186 http://www-inst.eecs.berkeley.edu/~cs186 http://www-inst.eecs.berkeley.edu/~cs186 http://www-inst.eecs.berkeley.edu/~cs186 http://www-inst.eecs.berkeley.edu/~cs186 http://www.microsoft.com

http://www-inst.eecs.berkeley.edu/~cs186 http://www.microsoft.com

http://www-inst.eecs.berkeley.edu/~cs186 http://www-inst.eecs.berkeley.edu/~cs186 http://www-inst.eecs.berkeley.edu/~cs186 http://www-inst.eecs.berkeley.edu/~cs186 http://www.microsoft.com

microsoft microsoft midnight midterm minibase million monday http://www.microsoft.com

http://www-inst.eecs.berkeley.edu/~cs186 http://www-inst.eecs.berkeley.edu/~cs186 http://www-inst.eecs.berkeley.edu/~cs186 http://www-inst.eecs.berkeley.edu/~cs186 http://www.microsoft.com

http://www.microsoft.com

more must http://www.microsoft.com

most http://www-inst.eecs.berkeley.edu/~cs186

Joseph CS194-3/16x ©UCB Fall 2007 Lec 26.18

msn http://www.microsoft.com

http://www-inst.eecs.berkeley.edu/~cs186 necessary need network new new news newsgroup http://www-inst.eecs.berkeley.edu/~cs186 http://www-inst.eecs.berkeley.edu/~cs186 http://www.microsoft.com

http://www-inst.eecs.berkeley.edu/~cs186 http://www.microsoft.com

http://www.microsoft.com

http://www-inst.eecs.berkeley.edu/~cs186

IR Buzzwords to Know

• Learning this in the context of relational

foundations is fine, but you need to know the IR lingo!

– Corpus: a collection of documents – Term: an isolated string (searchable unit) – Index: a mechanism mapping terms to documents – Inverted File (= Postings File): a file containing

terms and associated postings lists

– Postings List: a list of pointers (“postings”) to

documents 12/3/07 Joseph CS194-3/16x ©UCB Fall 2007 Lec 26.19

Handling Boolean Logic

• How 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 lists » Remember: postings list per key sorted by docID in index

12/3/07 Joseph CS194-3/16x ©UCB Fall 2007 Lec 26.20

Handling Boolean Logic (cont’d)

• How to do “term1” AND NOT “term2”? – Set subtraction » Also easy because sorted (basically merge join logic again) • How to do “term1” OR NOT “term2” – Union 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?

12/3/07 Joseph CS194-3/16x ©UCB Fall 2007 Lec 26.21

Boolean Search in SQL

“ Windows ” AND (“ Glass ” OR “ Door ”) AND NOT “ Microsoft ”

•

( 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()

• There’s only one SQL query template in Boolean Search – Single-table selects, UNION, INTERSECT, EXCEPT • magic_rank() is the “secret sauce” in the search engines

12/3/07

– Combos of statistics, linguistics, and graph theory (“page

rank”) tricks!

– Why isn’t this a static function (i.e., why do companies

change it)?

Joseph CS194-3/16x ©UCB Fall 2007 Lec 26.22

One Step Fancier: Phrases and “Near”

• Suppose you want a phrase – E.g. “Happy Days” • Different schema: – InvertedFile (term string,

position int

, docID string)

– Use index on term – Postings lists sorted by (docID, position) • Post-process the results – Find “Happy” AND “Days” – Keep results where positions are 1 off » Can be done during merge-join to AND the 2 lists! • Can do a similar thing for “term1” NEAR “term2” – Position < k off – Think about refinement to merge-join…

12/3/07 Joseph CS194-3/16x ©UCB Fall 2007 Lec 26.23

Administrivia

• Project 3 code due Thursday 12/6 • Midterm 3 Exam is Monday 12/10 • Optional topics?

12/3/07 Joseph CS194-3/16x ©UCB Fall 2007 Lec 26.24

Generating Web Search Results Pages

• Data structures: – InvertedFile (term string, position int,

docID int )

– Files(docID int, docID string

, snippet string, …)

– Btree on InvertedFile.term – Btree on Docs.docID • Page generation requires a final join step

between typical query result and Files.docID

– Can do this lazily: use cursor to generate a web

page full of results 12/3/07 Joseph CS194-3/16x ©UCB Fall 2007 Lec 26.25

Updates and Text Search

• Text search engines are designed to be query-mostly – Deletes and modifications are rare – Can postpone updates (nobody notices, no transactions!) » Can work off a union of indexes » Merge them in batch (typically re-bulk-load a new index) • Can’t afford to go offline for an update? – Create a 2nd index on a separate machine – Replace the 1st index with the 2nd! • So, no concurrency control problems – Can compress to search-friendly, update-unfriendly

format

– Can keep postings lists sorted

12/3/07 Joseph CS194-3/16x ©UCB Fall 2007 Lec 26.26

Updates and Text Search (cont’d)

• What if updates are not rare? – Web search on news/sports, blogs, frequently

updated pages?

– Challenge is real-time updating of index • For all these reasons, text search engines and

DBMSs are usually separate products

– Also, text-search engines tune that one SQL

query to death!

– The benefits of a special-case workload

12/3/07 Joseph CS194-3/16x ©UCB Fall 2007 Lec 26.27

Lots more tricks in IR

• How to “rank” the output? – A mix of simple tricks works well – Some fancier tricks can help (use hyperlink graph) • Other ways to help users paw through the output? – Document “clustering” (e.g. Clusty.com) – Document visualization • How to use compression for better I/O performance? – E.g. making postings lists smaller – Try to make things fit in RAM (or in processor caches) • How to deal with synonyms, misspelling,abbreviations? • How to write a good web crawler? • Read the book Managing Gigabytes – By Ian H. Witten, Alistair Moffat, Timothy C. Bell

12/3/07 Joseph CS194-3/16x ©UCB Fall 2007 Lec 26.28

The Basics of IR

• “Inverted files” are the workhorses of all text

search engines

– Just B+-tree or Hash indexes on bag-of-words • Intersect, Union and Set Difference (Except) – Usually implemented via sorting – Or can be done with hash or index joins • Most of the other stuff is not “systems” work – A lot of it is cleverness in dealing with language – Both linguistics and statistics (more the latter!)

12/3/07 Joseph CS194-3/16x ©UCB Fall 2007 Lec 26.29

Revisiting Our IR/DBMS Distinctions

• Semantic Guarantees on Storage • Data Modeling & Query Complexity • Performance goals • For each one, is the difference reasonable and

acceptable?

12/3/07 Joseph CS194-3/16x ©UCB Fall 2007 Lec 26.30

Semantic Guarantees on Storage

• DBMS guarantees transactional semantics – If an inserting transaction commits, a subsequent

query will see the update

– Handles multiple concurrent updates correctly • IR systems do not do this; nobody notices! – Postpone insertions until convenient – No model of correct concurrency – Can even return incorrect answers for various

reasons!

• Is the difference reasonable and acceptable?

12/3/07 Joseph CS194-3/16x ©UCB Fall 2007 Lec 26.31

Data Modeling & Query Complexity

• DBMS supports any schema & queries – But requires you to define schema – And SQL is hard to figure out for the average

citizen

• IR supports only one schema & query – No schema design required (unstructured text) – Trivial (natural?) query language for simple tasks – No data correlation or analysis capabilities --

“search” only

• Is the difference reasonable and acceptable?

12/3/07 Joseph CS194-3/16x ©UCB Fall 2007 Lec 26.32

Performance Goals

• DBMS supports general SELECT – plus mix of INSERT, UPDATE, DELETE – general purpose engine must always perform “well” • IR systems expect only one stylized SELECT – plus delayed INSERT, unusual DELETE, no UPDATE. – special purpose, must run super-fast on “The Query” – users rarely look at the full answer in Boolean Search » Postpone any work you can to subsequent index joins » But make sure you can rank! • Is the difference reasonable and acceptable?

12/3/07 Joseph CS194-3/16x ©UCB Fall 2007 Lec 26.33

Summary

• IR & Relational systems share basic building

blocks for scalability

– IR internal representation is relational! – Equality indexes (B-trees) – Iterators – “Join” algorithms, esp. merge-join – “Join” ordering and selectivity estimation • IR constrains queries, schema, promises on

semantics

– Affects storage format, indexing and concurrency

control

– Affects join algorithms & selectivity estimation

12/3/07 Joseph CS194-3/16x ©UCB Fall 2007 Lec 26.34

Summary (cont’d)

• IR has different performance goals – Ranking and best answers fast • Many challenges in IR related to “text

engineering”

– But don’t tend to change the scalability

infrastructure 12/3/07 Joseph CS194-3/16x ©UCB Fall 2007 Lec 26.35