/* ****************************************** */
/* 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%)
*/