1. /* ****************************************** */
  2. /* First query, WHERE clause in first SELECT: */
  3. /* ****************************************** */
  4.  
  5. EXPLAIN ANALYZE SELECT p.*, e.title FROM
  6.   (SELECT * FROM Person WHERE name LIKE '%Mueller%') AS p
  7.     INNER JOIN Authorship AS au ON au.authorId = p.id
  8.     INNER JOIN Entry AS e ON e.id = au.entryId;
  9.  
  10. /* Results in the following plan: */
  11.  
  12. SELECT
  13.     P.NAME,
  14.     P.ID,
  15.     E.TITLE
  16. FROM PUBLIC.ENTRY E
  17.     /* PUBLIC.ENTRY.tableScan */
  18.     /* scanCount: 2695954 */
  19. INNER JOIN PUBLIC.AUTHORSHIP AU
  20.     /* PUBLIC.IDX961B0AA1: ENTRYID = E.ID */
  21.     ON 1=1
  22.     /* WHERE E.ID = AU.ENTRYID
  23.     */
  24.     /* scanCount: 8177488 */
  25. INNER JOIN (
  26.     SELECT
  27.         PERSON.NAME,
  28.         PERSON.ID
  29.     FROM PUBLIC.PERSON
  30.         /* PUBLIC.PERSON.tableScan */
  31.     WHERE NAME LIKE '%Mueller%'
  32. ) P
  33.     /* SELECT
  34.         PERSON.NAME,
  35.         PERSON.ID
  36.     FROM PUBLIC.PERSON
  37.         /++ PUBLIC.IDX11710372: ID IS ?1 ++/
  38.     WHERE (PERSON.ID IS ?1)
  39.         AND (NAME LIKE '%Mueller%'): ID = AU.AUTHORID
  40.      */
  41.     ON 1=1
  42.     /* scanCount: 5482261 */
  43. WHERE (E.ID = AU.ENTRYID)
  44.     AND (AU.AUTHORID = P.ID)
  45. /*
  46. total: 3643329
  47. AUTHORSHIP.IDX961B0AA1 read: 58258 (1%)
  48. ENTRY.ENTRY_DATA read: 456203 (12%)
  49. PERSON.IDX11710372 read: 1275231 (35%)
  50. PERSON.PERSON_DATA read: 1853637 (50%)
  51. */
  52.  
  53. /* ****************************************** */
  54. /* Second query, WHERE clause after the JOIN: */
  55. /* ****************************************** */
  56.  
  57. EXPLAIN ANALYZE SELECT p.*, e.title FROM Person AS p
  58.   INNER JOIN Authorship AS au ON au.authorId = p.id
  59.   INNER JOIN Entry AS e ON e.id = au.entryId
  60. WHERE p.name LIKE '%Mueller%';
  61.  
  62. /* Results in the following plan: */
  63.  
  64. SELECT
  65.     P.NAME,
  66.     P.ID,
  67.     E.TITLE
  68. FROM PUBLIC.ENTRY E
  69.     /* PUBLIC.ENTRY.tableScan */
  70.     /* scanCount: 2695954 */
  71. INNER JOIN PUBLIC.AUTHORSHIP AU
  72.     /* PUBLIC.IDX961B0AA1: ENTRYID = E.ID */
  73.     ON 1=1
  74.     /* WHERE E.ID = AU.ENTRYID
  75.     */
  76.     /* scanCount: 8177488 */
  77. INNER JOIN PUBLIC.PERSON P
  78.     /* PUBLIC.IDX11710372: ID = AU.AUTHORID */
  79.     ON 1=1
  80.     /* scanCount: 10963070 */
  81. WHERE (P.NAME LIKE '%Mueller%')
  82.     AND ((E.ID = AU.ENTRYID)
  83.     AND (AU.AUTHORID = P.ID))
  84. /*
  85. total: 3643367
  86. AUTHORSHIP.IDX961B0AA1 read: 58258 (1%)
  87. ENTRY.ENTRY_DATA read: 456203 (12%)
  88. PERSON.IDX11710372 read: 1275251 (35%)
  89. PERSON.PERSON_DATA read: 1853655 (50%)
  90. */
  91.  
  92. /* ****************************** */
  93. /* Third query, using nested INs: */
  94. /* ****************************** */
  95.  
  96. EXPLAIN ANALYZE SELECT title FROM Entry e WHERE id IN (
  97.   SELECT entryId FROM Authorship WHERE authorId IN (
  98.     SELECT id FROM Person WHERE name LIKE '%Mueller%'
  99.   )
  100. )
  101.  
  102. /* Results in the following plan: */
  103.  
  104. SELECT
  105.     TITLE
  106. FROM PUBLIC.ENTRY E
  107.     /* PUBLIC.IDXDC5030D: ID IN(SELECT
  108.         ENTRYID
  109.     FROM PUBLIC.AUTHORSHIP
  110.         /++ PUBLIC.AUTHORSHIP.tableScan ++/
  111.         /++ scanCount: 5481536 ++/
  112.     WHERE AUTHORID IN(
  113.         SELECT
  114.             ID
  115.         FROM PUBLIC.PERSON
  116.             /++ PUBLIC.PERSON.tableScan ++/
  117.             /++ scanCount: 990482 ++/
  118.         WHERE NAME LIKE '%Mueller%'))
  119.      */
  120.     /* scanCount: 727 */
  121. WHERE ID IN(
  122.     SELECT
  123.         ENTRYID
  124.     FROM PUBLIC.AUTHORSHIP
  125.         /* PUBLIC.AUTHORSHIP.tableScan */
  126.         /* scanCount: 5481536 */
  127.     WHERE AUTHORID IN(
  128.         SELECT
  129.             ID
  130.         FROM PUBLIC.PERSON
  131.             /* PUBLIC.PERSON.tableScan */
  132.             /* scanCount: 990482 */
  133.         WHERE NAME LIKE '%Mueller%'))
  134. /*
  135. total: 81664
  136. AUTHORSHIP.AUTHORSHIP_DATA read: 61653 (75%)
  137. ENTRY.ENTRY_DATA read: 1208 (1%)
  138. ENTRY.IDXDC5030D read: 804 (0%)
  139. PERSON.PERSON_DATA read: 17999 (22%)
  140. */