API tools faq
paste
Advertisement
Guest User

MongoDB Find Explain

a guest
Feb 23rd, 2015
273
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 14.34 KB | None | 0 0
raw download clone embed print report
  1. ############################
  2. ##### Query A Explain ######
  3. ############################
  4.  
  5. db.google_raw_id.aggregate([{
  6. $match: {
  7. client_id: 143,
  8. campaign_name: 'SC - Dating For Seniors',
  9. date: {
  10. $gte: ISODate("2014-01-10T00:00:00.0Z"),
  11. $lte: ISODate("2015-01-10T00:00:00.0Z")
  12. }
  13. }
  14. }, {
  15. $group: {
  16. _id: "$campaign_name",
  17. cost: {$sum: "$cost"},
  18. clicks: {$sum: "$clicks"},
  19. impressions: {$sum: "$impressions"}
  20. }
  21. }], {explain: true} )
  22.  
  23. Result:
  24.  
  25. {
  26. "splitPipeline" : {
  27. "shardsPart" : [
  28. {
  29. "$match" : {
  30. "client_id" : 143,
  31. "campaign_name" : "SC - Dating For Seniors",
  32. "date" : {
  33. "$gte" : ISODate("2014-01-10T00:00:00Z"),
  34. "$lte" : ISODate("2015-01-10T00:00:00Z")
  35. }
  36. }
  37. },
  38. {
  39. "$group" : {
  40. "_id" : "$campaign_name",
  41. "cost" : {
  42. "$sum" : "$cost"
  43. },
  44. "clicks" : {
  45. "$sum" : "$clicks"
  46. },
  47. "impressions" : {
  48. "$sum" : "$impressions"
  49. }
  50. }
  51. }
  52. ],
  53. "mergerPart" : [
  54. {
  55. "$group" : {
  56. "_id" : "$$ROOT._id",
  57. "cost" : {
  58. "$sum" : "$$ROOT.cost"
  59. },
  60. "clicks" : {
  61. "$sum" : "$$ROOT.clicks"
  62. },
  63. "impressions" : {
  64. "$sum" : "$$ROOT.impressions"
  65. },
  66. "$doingMerge" : true
  67. }
  68. }
  69. ]
  70. },
  71. "shards" : {
  72. "shard0000" : {
  73. "host" : "192.168.1.16:27018",
  74. "stages" : [
  75. {
  76. "$cursor" : {
  77. "query" : {
  78. "client_id" : 143,
  79. "campaign_name" : "SC - Dating For Seniors",
  80. "date" : {
  81. "$gte" : ISODate("2014-01-10T00:00:00Z"),
  82. "$lte" : ISODate("2015-01-10T00:00:00Z")
  83. }
  84. },
  85. "fields" : {
  86. "campaign_name" : 1,
  87. "clicks" : 1,
  88. "cost" : 1,
  89. "impressions" : 1,
  90. "_id" : 0
  91. },
  92. "queryPlanner" : {
  93. "plannerVersion" : 1,
  94. "namespace" : "optimization.google_raw_id",
  95. "indexFilterSet" : false,
  96. "parsedQuery" : {
  97. "$and" : [
  98. {
  99. "date" : {
  100. "$lte" : ISODate("2015-01-10T00:00:00Z")
  101. }
  102. },
  103. {
  104. "campaign_name" : {
  105. "$eq" : "SC - Dating For Seniors"
  106. }
  107. },
  108. {
  109. "client_id" : {
  110. "$eq" : 143
  111. }
  112. },
  113. {
  114. "date" : {
  115. "$gte" : ISODate("2014-01-10T00:00:00Z")
  116. }
  117. }
  118. ]
  119. },
  120. "winningPlan" : {
  121. "stage" : "CACHED_PLAN",
  122. "inputStage" : {
  123. "stage" : "SHARDING_FILTER",
  124. "inputStage" : {
  125. "stage" : "FETCH",
  126. "filter" : {
  127. "$and" : [
  128. {
  129. "date" : {
  130. "$lte" : ISODate("2015-01-10T00:00:00Z")
  131. }
  132. },
  133. {
  134. "client_id" : {
  135. "$eq" : 143
  136. }
  137. },
  138. {
  139. "date" : {
  140. "$gte" : ISODate("2014-01-10T00:00:00Z")
  141. }
  142. }
  143. ]
  144. },
  145. "inputStage" : {
  146. "stage" : "IXSCAN",
  147. "keyPattern" : {
  148. "campaign_name" : NumberLong(1)
  149. },
  150. "indexName" : "campaign_name_1",
  151. "isMultiKey" : false,
  152. "direction" : "forward",
  153. "indexBounds" : {
  154. "campaign_name" : [
  155. "[\"SC - Dating For Seniors\", \"SC - Dating For Seniors\"]"
  156. ]
  157. }
  158. }
  159. }
  160. }
  161. },
  162. "rejectedPlans" : [ ]
  163. }
  164. }
  165. },
  166. {
  167. "$group" : {
  168. "_id" : "$campaign_name",
  169. "cost" : {
  170. "$sum" : "$cost"
  171. },
  172. "clicks" : {
  173. "$sum" : "$clicks"
  174. },
  175. "impressions" : {
  176. "$sum" : "$impressions"
  177. }
  178. }
  179. }
  180. ]
  181. },
  182. "shard0001" : {
  183. "host" : "192.168.1.17:27018",
  184. "stages" : [
  185. {
  186. "$cursor" : {
  187. "query" : {
  188. "client_id" : 143,
  189. "campaign_name" : "SC - Dating For Seniors",
  190. "date" : {
  191. "$gte" : ISODate("2014-01-10T00:00:00Z"),
  192. "$lte" : ISODate("2015-01-10T00:00:00Z")
  193. }
  194. },
  195. "fields" : {
  196. "campaign_name" : 1,
  197. "clicks" : 1,
  198. "cost" : 1,
  199. "impressions" : 1,
  200. "_id" : 0
  201. },
  202. "queryPlanner" : {
  203. "plannerVersion" : 1,
  204. "namespace" : "optimization.google_raw_id",
  205. "indexFilterSet" : false,
  206. "parsedQuery" : {
  207. "$and" : [
  208. {
  209. "date" : {
  210. "$lte" : ISODate("2015-01-10T00:00:00Z")
  211. }
  212. },
  213. {
  214. "campaign_name" : {
  215. "$eq" : "SC - Dating For Seniors"
  216. }
  217. },
  218. {
  219. "client_id" : {
  220. "$eq" : 143
  221. }
  222. },
  223. {
  224. "date" : {
  225. "$gte" : ISODate("2014-01-10T00:00:00Z")
  226. }
  227. }
  228. ]
  229. },
  230. "winningPlan" : {
  231. "stage" : "CACHED_PLAN",
  232. "inputStage" : {
  233. "stage" : "SHARDING_FILTER",
  234. "inputStage" : {
  235. "stage" : "FETCH",
  236. "filter" : {
  237. "campaign_name" : {
  238. "$eq" : "SC - Dating For Seniors"
  239. }
  240. },
  241. "inputStage" : {
  242. "stage" : "IXSCAN",
  243. "keyPattern" : {
  244. "client_id" : 1,
  245. "date" : 1
  246. },
  247. "indexName" : "client_id_1_date_1",
  248. "isMultiKey" : false,
  249. "direction" : "forward",
  250. "indexBounds" : {
  251. "client_id" : [
  252. "[143.0, 143.0]"
  253. ],
  254. "date" : [
  255. "[new Date(1389312000000), new Date(1420848000000)]"
  256. ]
  257. }
  258. }
  259. }
  260. }
  261. },
  262. "rejectedPlans" : [ ]
  263. }
  264. }
  265. },
  266. {
  267. "$group" : {
  268. "_id" : "$campaign_name",
  269. "cost" : {
  270. "$sum" : "$cost"
  271. },
  272. "clicks" : {
  273. "$sum" : "$clicks"
  274. },
  275. "impressions" : {
  276. "$sum" : "$impressions"
  277. }
  278. }
  279. }
  280. ]
  281. }
  282. },
  283. "ok" : 1
  284. }
  285.  
  286. ############################
  287. ##### Query B Explain ######
  288. ############################
  289.  
  290. db.google_raw_id.aggregate([{
  291. $match: {
  292. client_id: 143,
  293. date: {
  294. $gte: ISODate("2014-01-10T00:00:00.0Z"),
  295. $lte: ISODate("2015-01-10T00:00:00.0Z")
  296. }
  297. }
  298. }, {
  299. $group: {
  300. _id: "$campaign_name",
  301. cost: {$sum: "$cost"},
  302. clicks: {$sum: "$clicks"},
  303. impressions: {$sum: "$impressions"}
  304. }
  305. }, {
  306. $match: {
  307. _id: 'SC - Dating For Seniors',
  308. }
  309. }], {explain: true})
  310.  
  311.  
  312. Result:
  313. {
  314. "splitPipeline" : {
  315. "shardsPart" : [
  316. {
  317. "$match" : {
  318. "client_id" : 143,
  319. "date" : {
  320. "$gte" : ISODate("2014-01-10T00:00:00Z"),
  321. "$lte" : ISODate("2015-01-10T00:00:00Z")
  322. }
  323. }
  324. },
  325. {
  326. "$group" : {
  327. "_id" : "$campaign_name",
  328. "cost" : {
  329. "$sum" : "$cost"
  330. },
  331. "clicks" : {
  332. "$sum" : "$clicks"
  333. },
  334. "impressions" : {
  335. "$sum" : "$impressions"
  336. }
  337. }
  338. }
  339. ],
  340. "mergerPart" : [
  341. {
  342. "$group" : {
  343. "_id" : "$$ROOT._id",
  344. "cost" : {
  345. "$sum" : "$$ROOT.cost"
  346. },
  347. "clicks" : {
  348. "$sum" : "$$ROOT.clicks"
  349. },
  350. "impressions" : {
  351. "$sum" : "$$ROOT.impressions"
  352. },
  353. "$doingMerge" : true
  354. }
  355. },
  356. {
  357. "$match" : {
  358. "_id" : "SC - Dating For Seniors"
  359. }
  360. }
  361. ]
  362. },
  363. "shards" : {
  364. "shard0000" : {
  365. "host" : "192.168.1.16:27018",
  366. "stages" : [
  367. {
  368. "$cursor" : {
  369. "query" : {
  370. "client_id" : 143,
  371. "date" : {
  372. "$gte" : ISODate("2014-01-10T00:00:00Z"),
  373. "$lte" : ISODate("2015-01-10T00:00:00Z")
  374. }
  375. },
  376. "fields" : {
  377. "campaign_name" : 1,
  378. "clicks" : 1,
  379. "cost" : 1,
  380. "impressions" : 1,
  381. "_id" : 0
  382. },
  383. "queryPlanner" : {
  384. "plannerVersion" : 1,
  385. "namespace" : "optimization.google_raw_id",
  386. "indexFilterSet" : false,
  387. "parsedQuery" : {
  388. "$and" : [
  389. {
  390. "date" : {
  391. "$lte" : ISODate("2015-01-10T00:00:00Z")
  392. }
  393. },
  394. {
  395. "client_id" : {
  396. "$eq" : 143
  397. }
  398. },
  399. {
  400. "date" : {
  401. "$gte" : ISODate("2014-01-10T00:00:00Z")
  402. }
  403. }
  404. ]
  405. },
  406. "winningPlan" : {
  407. "stage" : "SHARDING_FILTER",
  408. "inputStage" : {
  409. "stage" : "FETCH",
  410. "filter" : {
  411. "$and" : [
  412. {
  413. "date" : {
  414. "$lte" : ISODate("2015-01-10T00:00:00Z")
  415. }
  416. },
  417. {
  418. "date" : {
  419. "$gte" : ISODate("2014-01-10T00:00:00Z")
  420. }
  421. }
  422. ]
  423. },
  424. "inputStage" : {
  425. "stage" : "IXSCAN",
  426. "keyPattern" : {
  427. "client_id" : NumberLong(1)
  428. },
  429. "indexName" : "client_id_1",
  430. "isMultiKey" : false,
  431. "direction" : "forward",
  432. "indexBounds" : {
  433. "client_id" : [
  434. "[143.0, 143.0]"
  435. ]
  436. }
  437. }
  438. }
  439. },
  440. "rejectedPlans" : [
  441. {
  442. "stage" : "FETCH",
  443. "inputStage" : {
  444. "stage" : "SHARDING_FILTER",
  445. "inputStage" : {
  446. "stage" : "IXSCAN",
  447. "keyPattern" : {
  448. "client_id" : 1,
  449. "date" : 1
  450. },
  451. "indexName" : "client_id_1_date_1",
  452. "isMultiKey" : false,
  453. "direction" : "forward",
  454. "indexBounds" : {
  455. "client_id" : [
  456. "[143.0, 143.0]"
  457. ],
  458. "date" : [
  459. "[new Date(1389312000000), new Date(1420848000000)]"
  460. ]
  461. }
  462. }
  463. }
  464. },
  465. {
  466. "stage" : "SHARDING_FILTER",
  467. "inputStage" : {
  468. "stage" : "FETCH",
  469. "filter" : {
  470. "client_id" : {
  471. "$eq" : 143
  472. }
  473. },
  474. "inputStage" : {
  475. "stage" : "IXSCAN",
  476. "keyPattern" : {
  477. "date" : NumberLong(-1)
  478. },
  479. "indexName" : "date_-1",
  480. "isMultiKey" : false,
  481. "direction" : "forward",
  482. "indexBounds" : {
  483. "date" : [
  484. "[new Date(1420848000000), new Date(1389312000000)]"
  485. ]
  486. }
  487. }
  488. }
  489. }
  490. ]
  491. }
  492. }
  493. },
  494. {
  495. "$group" : {
  496. "_id" : "$campaign_name",
  497. "cost" : {
  498. "$sum" : "$cost"
  499. },
  500. "clicks" : {
  501. "$sum" : "$clicks"
  502. },
  503. "impressions" : {
  504. "$sum" : "$impressions"
  505. }
  506. }
  507. }
  508. ]
  509. },
  510. "shard0001" : {
  511. "host" : "192.168.1.17:27018",
  512. "stages" : [
  513. {
  514. "$cursor" : {
  515. "query" : {
  516. "client_id" : 143,
  517. "date" : {
  518. "$gte" : ISODate("2014-01-10T00:00:00Z"),
  519. "$lte" : ISODate("2015-01-10T00:00:00Z")
  520. }
  521. },
  522. "fields" : {
  523. "campaign_name" : 1,
  524. "clicks" : 1,
  525. "cost" : 1,
  526. "impressions" : 1,
  527. "_id" : 0
  528. },
  529. "queryPlanner" : {
  530. "plannerVersion" : 1,
  531. "namespace" : "optimization.google_raw_id",
  532. "indexFilterSet" : false,
  533. "parsedQuery" : {
  534. "$and" : [
  535. {
  536. "date" : {
  537. "$lte" : ISODate("2015-01-10T00:00:00Z")
  538. }
  539. },
  540. {
  541. "client_id" : {
  542. "$eq" : 143
  543. }
  544. },
  545. {
  546. "date" : {
  547. "$gte" : ISODate("2014-01-10T00:00:00Z")
  548. }
  549. }
  550. ]
  551. },
  552. "winningPlan" : {
  553. "stage" : "SHARDING_FILTER",
  554. "inputStage" : {
  555. "stage" : "FETCH",
  556. "filter" : {
  557. "$and" : [
  558. {
  559. "date" : {
  560. "$lte" : ISODate("2015-01-10T00:00:00Z")
  561. }
  562. },
  563. {
  564. "date" : {
  565. "$gte" : ISODate("2014-01-10T00:00:00Z")
  566. }
  567. }
  568. ]
  569. },
  570. "inputStage" : {
  571. "stage" : "IXSCAN",
  572. "keyPattern" : {
  573. "client_id" : NumberLong(1)
  574. },
  575. "indexName" : "client_id_1",
  576. "isMultiKey" : false,
  577. "direction" : "forward",
  578. "indexBounds" : {
  579. "client_id" : [
  580. "[143.0, 143.0]"
  581. ]
  582. }
  583. }
  584. }
  585. },
  586. "rejectedPlans" : [
  587. {
  588. "stage" : "FETCH",
  589. "inputStage" : {
  590. "stage" : "SHARDING_FILTER",
  591. "inputStage" : {
  592. "stage" : "IXSCAN",
  593. "keyPattern" : {
  594. "client_id" : 1,
  595. "date" : 1
  596. },
  597. "indexName" : "client_id_1_date_1",
  598. "isMultiKey" : false,
  599. "direction" : "forward",
  600. "indexBounds" : {
  601. "client_id" : [
  602. "[143.0, 143.0]"
  603. ],
  604. "date" : [
  605. "[new Date(1389312000000), new Date(1420848000000)]"
  606. ]
  607. }
  608. }
  609. }
  610. },
  611. {
  612. "stage" : "SHARDING_FILTER",
  613. "inputStage" : {
  614. "stage" : "FETCH",
  615. "filter" : {
  616. "client_id" : {
  617. "$eq" : 143
  618. }
  619. },
  620. "inputStage" : {
  621. "stage" : "IXSCAN",
  622. "keyPattern" : {
  623. "date" : NumberLong(-1)
  624. },
  625. "indexName" : "date_-1",
  626. "isMultiKey" : false,
  627. "direction" : "forward",
  628. "indexBounds" : {
  629. "date" : [
  630. "[new Date(1420848000000), new Date(1389312000000)]"
  631. ]
  632. }
  633. }
  634. }
  635. }
  636. ]
  637. }
  638. }
  639. },
  640. {
  641. "$group" : {
  642. "_id" : "$campaign_name",
  643. "cost" : {
  644. "$sum" : "$cost"
  645. },
  646. "clicks" : {
  647. "$sum" : "$clicks"
  648. },
  649. "impressions" : {
  650. "$sum" : "$impressions"
  651. }
  652. }
  653. }
  654. ]
  655. }
  656. },
  657. "ok" : 1
  658. }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!