explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9CoS

Settings
# exclusive inclusive rows x rows loops node
1. 0.026 58,581.680 ↑ 1.0 400 1

Limit (cost=11,041,935.77..11,041,936.77 rows=400 width=27) (actual time=58,581.600..58,581.680 rows=400 loops=1)

2.          

CTE picklist_0

3. 0.468 0.468 ↑ 1.0 13 1

Seq Scan on t233_opportunitystagenamepicklistdim (cost=0.00..95.83 rows=13 width=4) (actual time=0.014..0.468 rows=13 loops=1)

  • Filter: (correlated_value = ANY ('{"06 - Won, Deploy & Expand",Lost,"6 - Won, Deploy & Expand",Close:Duplicate,"HP Not Pursued","06 - Won, Deploy & Expand",Error,"HPE Not Pursued","Closed - Won","06 - Won. Deploy & Expand",Won,Duplicate,"Closed - Lost"}'::text[]))
  • Rows Removed by Filter: 1218
4. 22.897 58,581.654 ↑ 25,627.2 400 1

Sort (cost=11,041,839.94..11,067,467.13 rows=10,250,875 width=27) (actual time=58,581.598..58,581.654 rows=400 loops=1)

  • Sort Key: ((t236_opp_line_item_fact_1.c824_tier1_sub_total * COALESCE(t539_clari_conversion_rate.conversion_rate, '1'::double precision))) DESC NULLS LAST
  • Sort Method: top-N heapsort Memory: 56kB
5. 787.308 58,558.757 ↑ 72.5 141,426 1

Merge Join (cost=10,223,192.11..10,547,550.12 rows=10,250,875 width=27) (actual time=51,176.891..58,558.757 rows=141,426 loops=1)

  • Merge Cond: (t236_opp_line_item_fact.sid = t236_opp_line_item_fact_1.sid)
6. 1,054.088 23,004.461 ↑ 6.4 418,251 1

Merge Join (cost=5,591,259.21..5,710,362.08 rows=2,670,984 width=27) (actual time=18,753.855..23,004.461 rows=418,251 loops=1)

  • Merge Cond: (t236_opp_line_item_fact.sid = t200_opportunitylineitemdim.sid)
7. 242.652 6,159.080 ↑ 5.2 418,251 1

Sort (cost=2,492,366.33..2,497,796.04 rows=2,171,885 width=4) (actual time=6,062.047..6,159.080 rows=418,251 loops=1)

  • Sort Key: t236_opp_line_item_fact.sid
  • Sort Method: external merge Disk: 5696kB
8. 437.267 5,916.428 ↑ 5.2 418,251 1

Hash Join (cost=846,469.85..2,204,381.83 rows=2,171,885 width=4) (actual time=1,684.077..5,916.428 rows=418,251 loops=1)

  • Hash Cond: (t236_opp_line_item_fact.c804_opp_ownerid = t166_userrolehierarchy.sid)
9. 3,802.446 4,663.015 ↑ 1.1 2,156,650 1

Bitmap Heap Scan on t236_opp_line_item_fact (cost=449,119.68..1,758,604.33 rows=2,277,325 width=8) (actual time=866.145..4,663.015 rows=2,156,650 loops=1)

  • Recheck Cond: ((c805_opp_close_date >= 20181101) AND (c805_opp_close_date <= 20190131) AND (start_stamp <= '1541055600000'::bigint) AND (end_stamp > '1541055600000'::bigint))
  • Rows Removed by Index Recheck: 10005408
  • Filter: ((NOT deleted) AND (NOT (hashed SubPlan 2)))
  • Rows Removed by Filter: 345694
  • Heap Blocks: exact=23240 lossy=320342
10. 860.092 860.092 ↑ 1.9 2,502,615 1

Bitmap Index Scan on ix_t236_opp_line_item_fact_closedate_timestamp (cost=0.00..448,550.06 rows=4,677,673 width=0) (actual time=860.092..860.092 rows=2,502,615 loops=1)

  • Index Cond: ((c805_opp_close_date >= 20181101) AND (c805_opp_close_date <= 20190131) AND (start_stamp <= '1541055600000'::bigint) AND (end_stamp > '1541055600000'::bigint))
11.          

SubPlan (forBitmap Heap Scan)

12. 0.477 0.477 ↑ 1.0 13 1

CTE Scan on picklist_0 cte0 (cost=0.00..0.26 rows=13 width=4) (actual time=0.018..0.477 rows=13 loops=1)

13. 16.757 816.146 ↑ 1.6 121,491 1

Hash (cost=394,163.96..394,163.96 rows=194,176 width=4) (actual time=816.146..816.146 rows=121,491 loops=1)

  • Buckets: 131072 Batches: 4 Memory Usage: 2095kB
14. 15.837 799.389 ↑ 1.6 121,491 1

Unique (cost=387,640.19..394,163.96 rows=194,176 width=4) (actual time=767.832..799.389 rows=121,491 loops=1)

15. 70.489 783.552 ↑ 10.0 130,938 1

Sort (cost=387,640.19..390,902.08 rows=1,304,755 width=4) (actual time=767.831..783.552 rows=130,938 loops=1)

  • Sort Key: t166_userrolehierarchy.sid
  • Sort Method: external merge Disk: 1784kB
16. 205.895 713.063 ↑ 10.0 130,938 1

Hash Join (cost=59,414.95..237,267.93 rows=1,304,755 width=4) (actual time=161.711..713.063 rows=130,938 loops=1)

  • Hash Cond: (t166_userrolehierarchy.c662_user_role_id = t67_userroledim.sid)
17. 353.537 493.361 ↓ 1.0 1,329,432 1

Bitmap Heap Scan on t166_userrolehierarchy (cost=38,046.87..196,214.19 rows=1,304,755 width=8) (actual time=147.233..493.361 rows=1,329,432 loops=1)

  • Recheck Cond: ((c663_ancestor_role_id = ANY ('{436,247157}'::integer[])) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=40198
18. 139.824 139.824 ↓ 1.0 1,337,326 1

Bitmap Index Scan on ix_t166_userrolehierarchy_ancestor_end_start_timestamp (cost=0.00..37,720.68 rows=1,304,755 width=0) (actual time=139.824..139.824 rows=1,337,326 loops=1)

  • Index Cond: ((c663_ancestor_role_id = ANY ('{436,247157}'::integer[])) AND (end_stamp = '32503680000000'::bigint))
19. 1.458 13.807 ↑ 9.3 8,397 1

Hash (cost=20,088.70..20,088.70 rows=77,950 width=4) (actual time=13.807..13.807 rows=8,397 loops=1)

  • Buckets: 131072 Batches: 2 Memory Usage: 1178kB
20. 7.462 12.349 ↑ 9.3 8,397 1

Bitmap Heap Scan on t67_userroledim (cost=4,199.73..20,088.70 rows=77,950 width=4) (actual time=5.268..12.349 rows=8,397 loops=1)

  • Recheck Cond: ((end_stamp = '32503680000000'::bigint) AND ((c377_portaltype IS NULL) OR (c377_portaltype = 'None'::text)))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer))
  • Rows Removed by Filter: 6119
  • Heap Blocks: exact=1800
21. 4.887 4.887 ↑ 5.4 14,568 1

Bitmap Index Scan on ix_t67_userroledim_sid_end_start_timestamp_partial (cost=0.00..4,180.24 rows=78,913 width=0) (actual time=4.887..4.887 rows=14,568 loops=1)

  • Index Cond: (end_stamp = '32503680000000'::bigint)
22. 937.797 15,791.293 ↑ 1.1 9,724,364 1

Materialize (cost=3,098,892.33..3,153,349.17 rows=10,891,367 width=23) (actual time=12,691.794..15,791.293 rows=9,724,364 loops=1)

23. 8,994.757 14,853.496 ↑ 1.1 9,724,364 1

Sort (cost=3,098,892.33..3,126,120.75 rows=10,891,367 width=23) (actual time=12,691.791..14,853.496 rows=9,724,364 loops=1)

  • Sort Key: t200_opportunitylineitemdim.sid
  • Sort Method: external merge Disk: 323272kB
24. 5,858.739 5,858.739 ↑ 1.1 9,724,458 1

Seq Scan on t200_opportunitylineitemdim (cost=0.00..1,379,153.23 rows=10,891,367 width=23) (actual time=0.154..5,858.739 rows=9,724,458 loops=1)

  • Filter: ((NOT deleted) AND (start_stamp <= '1541055600000'::bigint) AND (end_stamp > '1541055600000'::bigint))
  • Rows Removed by Filter: 8991476
25. 676.187 34,766.988 ↑ 1.0 7,592,655 1

Materialize (cost=4,631,932.89..4,670,307.64 rows=7,674,949 width=20) (actual time=32,422.954..34,766.988 rows=7,592,655 loops=1)

26. 8,832.003 34,090.801 ↑ 1.0 7,592,655 1

Sort (cost=4,631,932.89..4,651,120.27 rows=7,674,949 width=20) (actual time=32,422.951..34,090.801 rows=7,592,655 loops=1)

  • Sort Key: t236_opp_line_item_fact_1.sid
  • Sort Method: external merge Disk: 325648kB
27. 1,662.435 25,258.798 ↓ 1.3 9,897,882 1

Merge Right Join (cost=3,327,081.70..3,439,439.25 rows=7,674,949 width=20) (actual time=19,610.287..25,258.798 rows=9,897,882 loops=1)

  • Merge Cond: ((t539_clari_conversion_rate.sid = t236_opp_line_item_fact_1.c821_opp_currency_code) AND (t539_clari_conversion_rate.rate_date = t236_opp_line_item_fact_1.c805_opp_close_date))
28. 7.164 84.300 ↓ 1.0 103,479 1

Merge Append (cost=0.31..9,572.47 rows=103,474 width=16) (actual time=0.032..84.300 rows=103,479 loops=1)

  • Sort Key: t539_clari_conversion_rate.sid, t539_clari_conversion_rate.rate_date
29. 0.005 0.008 ↓ 0.0 0 1

Sort (cost=0.01..0.02 rows=1 width=16) (actual time=0.008..0.008 rows=0 loops=1)

  • Sort Key: t539_clari_conversion_rate.sid, t539_clari_conversion_rate.rate_date
  • Sort Method: quicksort Memory: 25kB
30. 0.003 0.003 ↓ 0.0 0 1

Seq Scan on t539_clari_conversion_rate (cost=0.00..0.00 rows=1 width=16) (actual time=0.003..0.003 rows=0 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
31. 77.128 77.128 ↓ 1.0 103,479 1

Index Scan using t539_clari_conversion_rate_usd_sidratedateunique on t539_clari_conversion_rate_usd (cost=0.29..8,279.02 rows=103,473 width=16) (actual time=0.024..77.128 rows=103,479 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
32. 1,377.904 23,512.063 ↓ 1.3 9,897,882 1

Materialize (cost=3,327,081.38..3,365,456.13 rows=7,674,949 width=20) (actual time=19,609.108..23,512.063 rows=9,897,882 loops=1)

33. 10,588.338 22,134.159 ↓ 1.3 9,897,882 1

Sort (cost=3,327,081.38..3,346,268.76 rows=7,674,949 width=20) (actual time=19,609.102..22,134.159 rows=9,897,882 loops=1)

  • Sort Key: t236_opp_line_item_fact_1.c821_opp_currency_code, t236_opp_line_item_fact_1.c805_opp_close_date
  • Sort Method: external merge Disk: 286920kB
34. 11,545.821 11,545.821 ↓ 1.3 9,897,882 1

Seq Scan on t236_opp_line_item_fact t236_opp_line_item_fact_1 (cost=0.00..2,134,587.74 rows=7,674,949 width=20) (actual time=0.201..11,545.821 rows=9,897,882 loops=1)

  • Filter: ((NOT deleted) AND (start_stamp <= '1549007999999'::bigint) AND (end_stamp > '1549007999999'::bigint) AND ((c805_opp_close_date < 20181101) OR (c805_opp_close_date > 20190131)))
  • Rows Removed by Filter: 36609874