/* ****************************************** */
/* First query, WHERE clause in first SELECT: */
/* ****************************************** */

EXPLAIN ANALYZE SELECT p.*, e.title FROM
  (SELECT * FROM Person WHERE name LIKE '%Mueller%') AS p 
    INNER JOIN Authorship AS au ON au.authorId = p.id 
    INNER JOIN Entry AS e ON e.id = au.entryId;

/* Results in the following plan: */

SELECT
    P.NAME,
    P.ID,
    E.TITLE
FROM PUBLIC.ENTRY E
    /* PUBLIC.ENTRY.tableScan */
    /* scanCount: 2695954 */
INNER JOIN PUBLIC.AUTHORSHIP AU
    /* PUBLIC.IDX961B0AA1: ENTRYID = E.ID */
    ON 1=1
    /* WHERE E.ID = AU.ENTRYID
    */
    /* scanCount: 8177488 */
INNER JOIN (
    SELECT
        PERSON.NAME,
        PERSON.ID
    FROM PUBLIC.PERSON
        /* PUBLIC.PERSON.tableScan */
    WHERE NAME LIKE '%Mueller%'
) P
    /* SELECT
        PERSON.NAME,
        PERSON.ID
    FROM PUBLIC.PERSON
        /++ PUBLIC.IDX11710372: ID IS ?1 ++/
    WHERE (PERSON.ID IS ?1)
        AND (NAME LIKE '%Mueller%'): ID = AU.AUTHORID
     */
    ON 1=1
    /* scanCount: 5482261 */
WHERE (E.ID = AU.ENTRYID)
    AND (AU.AUTHORID = P.ID)
/*
total: 3643329
AUTHORSHIP.IDX961B0AA1 read: 58258 (1%)
ENTRY.ENTRY_DATA read: 456203 (12%)
PERSON.IDX11710372 read: 1275231 (35%)
PERSON.PERSON_DATA read: 1853637 (50%)
*/

/* ****************************************** */
/* Second query, WHERE clause after the JOIN: */
/* ****************************************** */

EXPLAIN ANALYZE SELECT p.*, e.title FROM Person AS p
  INNER JOIN Authorship AS au ON au.authorId = p.id
  INNER JOIN Entry AS e ON e.id = au.entryId
WHERE p.name like '%Mueller%';

/* Results in the following plan: */

SELECT
    P.NAME,
    P.ID,
    E.TITLE
FROM PUBLIC.ENTRY E
    /* PUBLIC.ENTRY.tableScan */
    /* scanCount: 2695954 */
INNER JOIN PUBLIC.AUTHORSHIP AU
    /* PUBLIC.IDX961B0AA1: ENTRYID = E.ID */
    ON 1=1
    /* WHERE E.ID = AU.ENTRYID
    */
    /* scanCount: 8177488 */
INNER JOIN PUBLIC.PERSON P
    /* PUBLIC.IDX11710372: ID = AU.AUTHORID */
    ON 1=1
    /* scanCount: 10963070 */
WHERE (P.NAME LIKE '%Mueller%')
    AND ((E.ID = AU.ENTRYID)
    AND (AU.AUTHORID = P.ID))
/*
total: 3643367
AUTHORSHIP.IDX961B0AA1 read: 58258 (1%)
ENTRY.ENTRY_DATA read: 456203 (12%)
PERSON.IDX11710372 read: 1275251 (35%)
PERSON.PERSON_DATA read: 1853655 (50%)
*/

/* ****************************** */
/* Third query, using nested INs: */
/* ****************************** */

EXPLAIN ANALYZE SELECT title FROM Entry e WHERE id IN (
  SELECT entryId FROM Authorship WHERE authorId IN (
    SELECT id FROM Person WHERE name LIKE '%Mueller%'
  )
)

/* Results in the following plan: */

SELECT
    TITLE
FROM PUBLIC.ENTRY E
    /* PUBLIC.IDXDC5030D: ID IN(SELECT
        ENTRYID
    FROM PUBLIC.AUTHORSHIP
        /++ PUBLIC.AUTHORSHIP.tableScan ++/
        /++ scanCount: 5481536 ++/
    WHERE AUTHORID IN(
        SELECT
            ID
        FROM PUBLIC.PERSON
            /++ PUBLIC.PERSON.tableScan ++/
            /++ scanCount: 990482 ++/
        WHERE NAME LIKE '%Mueller%'))
     */
    /* scanCount: 727 */
WHERE ID IN(
    SELECT
        ENTRYID
    FROM PUBLIC.AUTHORSHIP
        /* PUBLIC.AUTHORSHIP.tableScan */
        /* scanCount: 5481536 */
    WHERE AUTHORID IN(
        SELECT
            ID
        FROM PUBLIC.PERSON
            /* PUBLIC.PERSON.tableScan */
            /* scanCount: 990482 */
        WHERE NAME LIKE '%Mueller%'))
/*
total: 81664
AUTHORSHIP.AUTHORSHIP_DATA read: 61653 (75%)
ENTRY.ENTRY_DATA read: 1208 (1%)
ENTRY.IDXDC5030D read: 804 (0%)
PERSON.PERSON_DATA read: 17999 (22%)
*/