Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- /* ****************************************** */
- /* 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%)
- */
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement