1 / 40

Ranking in DB

Ranking in DB. Laks V.S. Lakshmanan Depf. of CS UBC. Why ranking in query answering? 1/3. Mutimedia data – fuzzy querying: e.g., “find top 2 red objects with a soft texture”. Overall score. Combine scores. Why ranking? 2/3.

lbarba
Download Presentation

Ranking in DB

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. Ranking in DB Laks V.S. Lakshmanan Depf. of CS UBC

  2. Why ranking in query answering? 1/3 • Mutimedia data – fuzzy querying: e.g., “find top 2 red objects with a soft texture”. Overall score Combine scores

  3. Why ranking? 2/3 • IR: “find top 5 documents relevant to `computational’, `neuroscience’ and `brain theory’. • IR systems maintain full text indexes; inverted lists of docs w.r.t. each keyword. • Same Q/A paradigm as before.

  4. Why ranking? 3/3 • Data stream, e.g., of network flow data: “find 10 users with the max. BW consumption and max. #packets communicated”. • In a social net, find 5 items tagged as most relevant to “lawn mowing” by user’s friends. • etc. • Fagin et al. – pioneering papers PODS’96, 01, TODS 2003. Burgeoned into a field now. • Focus on middleware algorithm, which given a score combo. function, computes top-K answers by probing diff. subsystems (or ranked lists).

  5. Computational model • Naïve method. • How to compute top-K efficiently? • Access methods: • Sorted access (sequential access) [SA]. • Random access [RA]. • Diff. optimization metrics: • Overall running time of algorithm. • SA < RA: minimize RAs. • RA not possible#: avoid RAs. • Combined optimization. • Has led to a variety of algorithms. • Memory vs. disk model. #: typical in IR systems.

  6. Fagin’s Algorithm (FA) • m lists sorted by descending scores. • Access (SA) all lists in parallel. • For each new object seen, fetch scores from other lists by RA. Overall score t(x) = t(x1, …, xm). Store (obj, score) in set Y. • Remember each object seen (under SA) in all lists in set H. • Repeat until |H| >= K. • For each seen object, do RA on lists as needed to find “missing” scores. Compute score of x as t(x) = t(x1, …, xm). • Sort Y in descending order of scores, breaking ties arbitrarily, and output top K.

  7. Example of FA Answers seen in >=1 list, i.e., Y unsorted. A B C D E F G H I J C(0.95) E(1.00) J(1.00) B(0.90) C(0.95) J(0.80) G(0.95) D(0.70) G(0.85) E(0.85) H(0.90) H(0.80) H(0.65) B(0.85) E(0.75) G(0.75) G(0.60) D(0.80) B(0.55) C(0.70) B(0.75) I(0.70) D(0.65) F(0.60) I(0.50) A(0.65) E(0.45) I(0.55) A(0.60) A(0.50) D(0.40) J(0.55) F(0.40) F(0.45) Answers seen (under SA) in all 4 lists, i.e., H. A(0.30) J(0.30) F(0.50) I(0.30)

  8. Example of FA Answers seen in >=1 list, i.e., Y unsorted. A B C D E F G H I J C(0.95) E(1.00) J(1.00) B(0.90) C(0.95) J(0.80) G(0.95) D(0.70) G(0.85) E(0.85) H(0.90) H(0.80) H(0.65) B(0.85) E(0.75) G(0.75) G(0.60) D(0.80) B(0.55) C(0.70) B(0.75) I(0.70) D(0.65) F(0.60) I(0.50) A(0.65) E(0.45) I(0.55) A(0.60) A(0.50) D(0.40) J(0.55) F(0.40) F(0.45) Answers seen (under SA) in all 4 lists, i.e., H. A(0.30) J(0.30) F(0.50) I(0.30)

  9. Example of FA Answers seen in >=1 list, i.e., Y unsorted. A B C D E F G H I J C(0.95) E(1.00) J(1.00) B(0.90) C(0.95) J(0.80) G(0.95) D(0.70) G(0.85) E(0.85) H(0.90) H(0.80) H(0.65) B(0.85) E(0.75) G(0.75) G(0.60) D(0.80) 3.30 B(0.55) C(0.70) B(0.75) I(0.70) D(0.65) F(0.60) I(0.50) A(0.65) E(0.45) I(0.55) A(0.60) A(0.50) D(0.40) J(0.55) F(0.40) F(0.45) Answers seen (under SA) in all 4 lists, i.e., H. A(0.30) J(0.30) F(0.50) I(0.30)

  10. Example of FA Answers seen in >=1 list, i.e., Y unsorted. A B C D E F G H I J C(0.95) E(1.00) J(1.00) B(0.90) C(0.95) J(0.80) G(0.95) D(0.70) G(0.85) E(0.85) H(0.90) H(0.80) H(0.65) B(0.85) E(0.75) G(0.75) G(0.60) D(0.80) 3.30 B(0.55) C(0.70) B(0.75) I(0.70) D(0.65) F(0.60) I(0.50) A(0.65) 2.65 E(0.45) I(0.55) A(0.60) A(0.50) D(0.40) J(0.55) F(0.40) F(0.45) Answers seen (under SA) in all 4 lists, i.e., H. A(0.30) J(0.30) F(0.50) I(0.30)

  11. Example of FA Answers seen in >=1 list, i.e., Y unsorted. A B C D E F G H I J C(0.95) E(1.00) J(1.00) 3.40 B(0.90) C(0.95) J(0.80) G(0.95) D(0.70) G(0.85) E(0.85) H(0.90) 3.05 H(0.80) H(0.65) B(0.85) E(0.75) G(0.75) G(0.60) D(0.80) 3.30 B(0.55) C(0.70) B(0.75) I(0.70) D(0.65) F(0.60) I(0.50) A(0.65) 2.65 E(0.45) I(0.55) A(0.60) A(0.50) D(0.40) J(0.55) F(0.40) F(0.45) Answers seen (under SA) in all 4 lists, i.e., H. A(0.30) J(0.30) F(0.50) I(0.30)

  12. Example of FA Answers seen in >=1 list, i.e., Y unsorted. A B C D E F G H I J 3.05 C(0.95) E(1.00) J(1.00) 3.40 B(0.90) C(0.95) J(0.80) G(0.95) D(0.70) G(0.85) E(0.85) H(0.90) 3.05 H(0.80) H(0.65) B(0.85) E(0.75) G(0.75) G(0.60) D(0.80) 3.15 3.30 B(0.55) C(0.70) B(0.75) I(0.70) D(0.65) F(0.60) I(0.50) A(0.65) 2.65 E(0.45) I(0.55) A(0.60) A(0.50) D(0.40) J(0.55) F(0.40) F(0.45) Answers seen (under SA) in all 4 lists, i.e., H. A(0.30) J(0.30) F(0.50) I(0.30)

  13. Example of FA Answers seen in >=1 list, i.e., Y unsorted. A B C D E F G H I J 3.05 C(0.95) E(1.00) J(1.00) 3.40 B(0.90) C(0.95) J(0.80) G(0.95) 2.55 D(0.70) G(0.85) E(0.85) H(0.90) 3.05 H(0.80) H(0.65) B(0.85) E(0.75) G(0.75) G(0.60) D(0.80) 3.15 3.30 B(0.55) C(0.70) B(0.75) I(0.70) D(0.65) F(0.60) I(0.50) A(0.65) 2.65 E(0.45) I(0.55) A(0.60) A(0.50) D(0.40) J(0.55) F(0.40) F(0.45) Answers seen (under SA) in all 4 lists, i.e., H. A(0.30) J(0.30) F(0.50) I(0.30)

  14. Example of FA Answers seen in >=1 list, i.e., Y unsorted. A B C D E F G H I J 3.05 C(0.95) E(1.00) J(1.00) 3.40 B(0.90) C(0.95) J(0.80) G(0.95) 2.55 D(0.70) G(0.85) E(0.85) H(0.90) 3.05 H(0.80) H(0.65) B(0.85) E(0.75) G(0.75) G(0.60) D(0.80) 3.15 3.30 B(0.55) C(0.70) B(0.75) I(0.70) D(0.65) F(0.60) I(0.50) A(0.65) 2.65 E(0.45) I(0.55) A(0.60) A(0.50) D(0.40) J(0.55) F(0.40) F(0.45) Answers seen (under SA) in all 4 lists, i.e., H. A(0.30) J(0.30) F(0.50) I(0.30) H

  15. Example of FA Answers seen in >=1 list, i.e., Y unsorted. A B C D E F G H I J 3.05 C(0.95) E(1.00) J(1.00) 3.40 B(0.90) C(0.95) J(0.80) G(0.95) 2.55 D(0.70) G(0.85) E(0.85) H(0.90) 3.05 H(0.80) H(0.65) B(0.85) E(0.75) G(0.75) G(0.60) D(0.80) 3.15 3.30 B(0.55) C(0.70) B(0.75) I(0.70) D(0.65) F(0.60) I(0.50) A(0.65) 2.65 E(0.45) I(0.55) A(0.60) A(0.50) D(0.40) J(0.55) F(0.40) F(0.45) Answers seen (under SA) in all 4 lists, i.e., H. A(0.30) J(0.30) F(0.50) I(0.30) H, G

  16. Example of FA Answers seen in >=1 list, i.e., Y unsorted. A B C D E F G H I J 3.05 C(0.95) E(1.00) J(1.00) 3.40 B(0.90) C(0.95) J(0.80) G(0.95) 2.55 D(0.70) G(0.85) E(0.85) H(0.90) 3.05 H(0.80) H(0.65) B(0.85) E(0.75) G(0.75) G(0.60) D(0.80) 3.15 3.30 B(0.55) C(0.70) B(0.75) I(0.70) 2.05 D(0.65) F(0.60) I(0.50) A(0.65) 2.65 E(0.45) I(0.55) A(0.60) A(0.50) D(0.40) J(0.55) F(0.40) F(0.45) Answers seen (under SA) in all 4 lists, i.e., H. A(0.30) J(0.30) F(0.50) I(0.30) H, G, B, C |H| = 4.

  17. FA Example concluded • A, F – not seen in any list. Yet, we are sure they can’t make it to top-4. Why? • Based on where the cursors are now, what’s the max. possible score for A, F? • What assumptions are being made about t()? • FA is shown to be optimal with very high probability [Fagin: PODS 1996]. • But can be beaten by other algorithms on specific inputs. • What about buffer size?

  18. Threshold Algorithm • Do parallel SA on all m lists. • For each new object x, fetch its scores from other lists and compute overall score. • If |Buffer| < K add x to Buffer; • Else if score(x) <= k-th score in buffer, toss; • Else replace bottom of buffer with (x, score(x)). • Stop when threshold <= k-th score in buffer. • Threshold := t(worst score seen on L1, …, worst score seen on Lm). • Output the top-K objects & scores (in buffer).

  19. TA Example A B C D E F G H I J C(0.95) E(1.00) J(1.00) B(0.90) C(0.95) J(0.80) G(0.95) D(0.70) G(0.85) E(0.85) H(0.90) H(0.80) H(0.65) B(0.85) E(0.75) G(0.75) G(0.60) D(0.80) B(0.55) C(0.70) B(0.75) I(0.70) D(0.65) F(0.60) I(0.50) A(0.65) E(0.45) I(0.55) A(0.60) A(0.50) D(0.40) J(0.55) F(0.40) F(0.45) A(0.30) J(0.30) F(0.50) I(0.30)

  20. TA Example A B C D E F G H I J C(0.95) E(1.00) J(1.00) B(0.90) C(0.95) J(0.80) G(0.95) D(0.70) G(0.85) E(0.85) H(0.90) H(0.80) H(0.65) B(0.85) E(0.75) G(0.75) G(0.60) D(0.80) B(0.55) C(0.70) B(0.75) I(0.70) D(0.65) F(0.60) I(0.50) A(0.65) E(0.45) I(0.55) A(0.60) A(0.50) D(0.40) J(0.55) F(0.40) F(0.45) A(0.30) J(0.30) F(0.50) I(0.30)

  21. TA Example A B C D E F G H I J C(0.95) E(1.00) J(1.00) B(0.90) C(0.95) J(0.80) G(0.95) D(0.70) G(0.85) E(0.85) H(0.90) H(0.80) H(0.65) B(0.85) E(0.75) G(0.75) G(0.60) D(0.80) B(0.55) C(0.70) 3.30 B(0.75) I(0.70) D(0.65) F(0.60) I(0.50) A(0.65) E(0.45) I(0.55) A(0.60) A(0.50) Threshold Bar: D(0.40) J(0.55) F(0.40) F(0.45) A(0.30) J(0.30) x1x2x3x4 0.95 1.00 0.95 1.00 F(0.50) I(0.30)

  22. TA Example A B C D E F G H I J C(0.95) E(1.00) J(1.00) 3.40 B(0.90) C(0.95) J(0.80) G(0.95) D(0.70) G(0.85) E(0.85) H(0.90) 3.05 H(0.80) H(0.65) B(0.85) E(0.75) G(0.75) G(0.60) D(0.80) B(0.55) C(0.70) 3.30 B(0.75) I(0.70) D(0.65) F(0.60) I(0.50) A(0.65) 2.65 E(0.45) I(0.55) A(0.60) A(0.50) Threshold Bar: T = 3.90. D(0.40) J(0.55) F(0.40) F(0.45) A(0.30) J(0.30) x1x2x3x4 0.95 1.00 0.95 1.00 F(0.50) I(0.30)

  23. TA Example A B C D E F G H I J C(0.95) E(1.00) 3.05 X J(1.00) 3.40 B(0.90) C(0.95) J(0.80) G(0.95) D(0.70) G(0.85) E(0.85) H(0.90) 3.05 H(0.80) H(0.65) B(0.85) E(0.75) G(0.75) G(0.60) D(0.80) 3.15 B(0.55) C(0.70) 3.30 B(0.75) I(0.70) D(0.65) F(0.60) I(0.50) A(0.65) 2.65 X E(0.45) I(0.55) A(0.60) A(0.50) Threshold Bar: T=3.60. D(0.40) J(0.55) F(0.40) F(0.45) A(0.30) J(0.30) x1x2x3x4 0.900.950.800.95 F(0.50) I(0.30)

  24. TA Example A B C D E F G H I J C(0.95) E(1.00) 3.05 X J(1.00) 3.40 B(0.90) C(0.95) J(0.80) G(0.95) 2.55 X D(0.70) G(0.85) E(0.85) H(0.90) 3.05 H(0.80) H(0.65) B(0.85) E(0.75) G(0.75) G(0.60) D(0.80) 3.15 B(0.55) C(0.70) 3.30 B(0.75) I(0.70) D(0.65) F(0.60) I(0.50) A(0.65) 2.65 X E(0.45) I(0.55) A(0.60) A(0.50) Threshold Bar: T=3.30. D(0.40) J(0.55) F(0.40) F(0.45) A(0.30) J(0.30) x1x2x3x4 0.85 0.85 0.70 0.90 F(0.50) I(0.30)

  25. TA Example A B C D E F G H I J C(0.95) E(1.00) 3.05 X J(1.00) 3.40 B(0.90) C(0.95) J(0.80) G(0.95) 2.55 X D(0.70) G(0.85) E(0.85) H(0.90) 3.05 H(0.80) H(0.65) B(0.85) E(0.75) G(0.75) G(0.60) D(0.80) 3.15 B(0.55) C(0.70) 3.30 B(0.75) I(0.70) D(0.65) F(0.60) I(0.50) A(0.65) 2.65 X E(0.45) I(0.55) A(0.60) A(0.50) Threshold Bar: T=3.10. D(0.40) J(0.55) F(0.40) F(0.45) A(0.30) J(0.30) x1x2x3x4 0.80 0.80 0.65 0.85 F(0.50) I(0.30)

  26. TA Example A B C D E F G H I J C(0.95) E(1.00) 3.05 X J(1.00) 3.40 B(0.90) C(0.95) J(0.80) G(0.95) 2.55 X D(0.70) G(0.85) E(0.85) H(0.90) 3.05 H(0.80) H(0.65) B(0.85) E(0.75) G(0.75) G(0.60) D(0.80) 3.15 B(0.55) C(0.70) 3.30 B(0.75) I(0.70) D(0.65) F(0.60) I(0.50) A(0.65) 2.65 X E(0.45) I(0.55) A(0.60) A(0.50) Threshold Bar: T=2.90. ==> can stop! D(0.40) J(0.55) F(0.40) F(0.45) A(0.30) J(0.30) x1x2x3x4 0.75 0.75 0.60 0.80 F(0.50) I(0.30)

  27. TA Remarks • What properties do we require of t() for TA to be correct? • How large does the buffer ever get with TA? What happened with FA? • Performance guarantee of TA (instance optimality): • D – class of DBs; A – class of algorithms; A A is instance optimal provided BA, DD, cost(A,D) = c.cost(B,D) + c’, for some fixed constants c, c’. • c = optimality ratio. • TA is instance optimal over algo’s not making wild guesses.

  28. No Random Access Algorithm • What if RA > SA or RA wasn’t allowed? • Do SA on all lists in parallel. At depth d: • Maintain worst scores x1, …, xm. • x any object seen in lists {1, …, i}. • Best(x) = t(x1, …, xi, xi+1, …, xm). • Worst(x) = t(x1, …, xi, 0, …, 0). • TopK contains K objects with max worst scores at depth d. Break ties using Best. M = k-th Worst score in TopK. • Object y is viable if Best(y) > M. • Stop when TopK contains >=K distinct objects and no object outside TopK is viable. Return TopK.

  29. NRA Example A B C D E F G H I J C(0.95) E(1.00) J(1.00) [0.95, 3.90] B(0.90) C(0.95) J(0.80) G(0.95) D(0.70) G(0.85) E(0.85) H(0.90) [1.00, 3.90] H(0.80) H(0.65) B(0.85) E(0.75) G(0.75) G(0.60) D(0.80) [0.95, 3.90] B(0.55) C(0.70) B(0.75) I(0.70) D(0.65) F(0.60) I(0.50) A(0.65) [1.00, 3.90] E(0.45) I(0.55) A(0.60) A(0.50) D(0.40) J(0.55) F(0.40) F(0.45) A(0.30) J(0.30) F(0.50) I(0.30)

  30. NRA Example A B C D E F G H I J C(0.95) E(1.00) [0.90, 3.60] J(1.00) [1.90, 3.75] B(0.90) C(0.95) J(0.80) G(0.95) D(0.70) G(0.85) E(0.85) H(0.90) [1.00, 3.65] H(0.80) H(0.65) B(0.85) [0.95, 3.60] E(0.75) G(0.75) G(0.60) D(0.80) [0.95, 3.65] B(0.55) C(0.70) B(0.75) I(0.70) D(0.65) F(0.60) I(0.50) A(0.65) [1.80, 3.65] E(0.45) I(0.55) A(0.60) A(0.50) D(0.40) J(0.55) F(0.40) F(0.45) A(0.30) J(0.30) F(0.50) I(0.30)

  31. NRA Features • What sort of t() do we need to assume, for NRA to work correctly? • How large can the buffers get? • How does the amount of bookkeeping compare with TA? • NRA is instance optimal over algo’s not making RA

  32. Combined optimization • What if we are told cost(RA) = .cost(SA)? • Can we find algo’s better than NRA and TA in this case? • Combined algorithm = CA. (See Fagin et al.’s paper for details.)

  33. Worrying about I/O cost • Based on Bast et al. VLDB 2006. • Inverted lists of (itemID, score) entries in desc. score order, as usual, but on disk. • Blocks sorted by itemID; across blocks still in desc. score order. •  Inverted Block Index (IBI) Algorithm. • What is an IBI?

  34. A Motivating Example List 1 List 2 List 3 Doc17 : 0.8 Doc25 : 0.7 Doc83 : 0.9 Doc78 : 0.2 Doc38 : 0.5 Doc17 : 0.7 . Doc14 : 0.5 Doc61 : 0.3 · Doc83 : 0.5 · · · · · Doc17 : 0.2 · · · · Round 1 (SA on 1,2,3) Doc17 : [0.8 , 2.4] Doc25 : [0.7 , 2.4] Doc83 : [0.9 , 2.4] unseen: ≤ 2.4

  35. A Motivating Example List 1 List 2 List 3 Doc17 : 0.8 Doc25 : 0.7 Doc83 : 0.9 Doc78 : 0.2 Doc38 : 0.5 Doc17 : 0.7 . Doc14 : 0.5 Doc61 : 0.3 · Doc83 : 0.5 · · · · · Doc17 : 0.2 · · · · Round 2 (SA on 1,2,3) Doc17 : [1.5 , 2.0] Doc25 : [0.7 , 1.6] Doc83 : [0.9 , 1.6] unseen: ≤ 1.4 Round 1 (SA on 1,2,3) Doc17 : [0.8 , 2.4] Doc25 : [0.7 , 2.4] Doc83 : [0.9 , 2.4] unseen: ≤ 2.4

  36. A Motivating Example List 1 List 2 List 3 Doc17 : 0.8 Doc25 : 0.7 Doc83 : 0.9 Doc78 : 0.2 Doc38 : 0.5 Doc17 : 0.7 . Doc14 : 0.5 Doc61 : 0.3 · Doc83 : 0.5 · · · · · Doc17 : 0.2 · · · · Round 2 (SA on 1,2,3) Doc17 : [1.5 , 2.0] Doc25 : [0.7 , 1.6] Doc83 : [0.9 , 1.6] unseen: ≤ 1.4 Round 3 (SA on 2,2,3!) Doc17 : [1.5 , 2.0] Doc83 : [1.4 , 1.6] unseen: ≤ 1.0 Round 1 (SA on 1,2,3) Doc17 : [0.8 , 2.4] Doc25 : [0.7 , 2.4] Doc83 : [0.9 , 2.4] unseen: ≤ 2.4

  37. A Motivating Example List 1 List 2 List 3 Doc17 : 0.8 Doc25 : 0.7 Doc83 : 0.9 Doc78 : 0.2 Doc38 : 0.5 Doc17 : 0.7 . Doc14 : 0.5 Doc61 : 0.3 · Doc83 : 0.5 · · · · · Doc17 : 0.2 · · · · Round 2 (SA on 1,2,3) Doc17 : [1.5 , 2.0] Doc25 : [0.7 , 1.6] Doc83 : [0.9 , 1.6] unseen: ≤ 1.4 Round 1 (SA on 1,2,3) Doc17 : [0.8 , 2.4] Doc25 : [0.7 , 2.4] Doc83 : [0.9 , 2.4] unseen: ≤ 2.4 Round 3 (SA on 2,2,3!) Doc17 : [1.5 , 2.0] Doc83 : [1.4 , 1.6] unseen: ≤ 1.0 Note deviation from round-robin. Round 4 (RA for Doc17) Doc17 : 1.7 all others < 1.7 done!

  38. IBI Algorithm • Same setting as NRA/CA, except use IBI. • Maintain two lists: Top-K items (T = d1, …, dk) and StillHaveASHot (SHASH) (S = dk+1, …, dk+q) items. • Pos_i = curr cursor position on list Li. • high_i = score in Li at curr cursor position (upper bounds score of unseen items). • For items d in S: • Which attr scores are known E(d). • Which attr scores are unknown E~(d). • Worst(d) = total score from E(d). • Best(d) = Worst(d) +  {high_i(d) | i E~(d)}. (Exactly as Fagin.)

  39. IBI Algorithm (contd.) • In each round, compute: • min-k = min{Worst(d) | d  T}. • bestscore that any unseen doc can have = sum of all high_i’s. • For dj  S: def_j = min-k – worst(d_j). [denotes deficit below qualification level for top-k.] • T sorted in desc. Worst(); S sorted in desc. Best(). [sorting on (score, ItemID) for fast processing.] • Invatiant: min-k >= max{Worst(d) | d  S}. • Termination: when min-k >= max{Best(d) | d  S}. • Can remove an obj from S whenever its Best <= min-k.  stop when S = {}. • Early termination AND minimal bookkeeping are BOTH important for performance.

  40. More on IBI Framework • Instead of scheduling SAs using RR, use a differential approach for diff. lists based on expected score reductions at future cursor positions (Knapsack). • Do SA*RA*. • Order RAs based on estimated Prob[dj can get into top-k answers].

More Related