2

While reading Graph Databases book, I came across this excerpt:

enter image description here

I can't figure out why the second query is more expensive than the first. Indeed, both filter on the Bob person.

May anyone explain it?

Improve this question

1 Answer 1

Reset to default
6

The book is assuming that PersonFriend is indexed on PersonID, but not on FriendID. It also seems to assume that Person indexes PersonID and Person independently.

If this is the case, the first query comes back as

  1. {INDEX UNIQUE SCAN Person on Person => 'Bob' get back PersonID}
  2. {INDEX RANGE SCAN PersonFriend on PersonID => PersonIDs for Alice and Zack get back FriendID}
  3. {LOOP {INDEX UNIQUE SCAN Person on PersonID} for Alice and Zack}

Then the second comes back as

  1. {INDEX UNIQUE SCAN Person on Person => 'Bob' get back PersonID}
  2. {TABLE ACCESS FULL PersonFriend FOR FriendID of Bob get back PersonID}
  3. {LOOP {INDEX UNIQUE SCAN Person on PersonID} for friend(s)}

This would be an elementary relational database design error. With a relationship table like this you would always index all keys, and the performance profile for both queries would be indistinguishable (ignoring variations in value distributions, etc).

At this point it sounds like the author is trying to show that relational databases are intrinsically deficient.

Improve this answer
5
  • Thanks for this answer :) I was thinking the same => "The book is assuming that PersonFriend is indexed on PersonID, but not on FriendID". So if I well figure it out, if FriendID was also indexed, both queries would be similar regarding performance. Howerver the book explains that adding another index for this column (FriendId) would be expensive (in memory, etc..). Am I right?
    – Mik378
    Jul 13, 2014 at 15:52
  • 3
    One problem with database "expertise" is that it decays. Indexes typically cost most in add/update/delete performance, but even then, it is practically unnoticeable. The additional storage on an index I also consider unnoticeable. The cost of storage is crazy low now! Concerns like these are fifteen years out-of-date if not more.
    – Andrew Wolfe
    Jul 13, 2014 at 18:13
  • So you mean that the argument provided by the book isn't relevant due to the low cost of a potential index on the FriendId column?
    – Mik378
    Jul 13, 2014 at 18:30
  • 2
    Yes, I wouldn't hesitate for a minute to add the other index. Moreover, if the column has an FK constraint on it you're crazy not to because it increases the cost of deletes to the MASTER (Person) table.
    – Andrew Wolfe
    Jul 15, 2014 at 0:47
  • Understood! Thanks a lot Andrew for this great answer :)
    – Mik378
    Jul 15, 2014 at 6:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.