explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8LRQ

Settings
# exclusive inclusive rows x rows loops node
1. 0.576 10,718.631 ↑ 69,125.4 2,664 1

Merge Left Join (cost=172,244.41..3,404,110.47 rows=184,150,007 width=44) (actual time=10,716.336..10,718.631 rows=2,664 loops=1)

  • Merge Cond: ((date(timezone('UTC-1'::text, to_timestamp(player.createTime)))) = insttable.instdate)
2.          

CTE insttable

3. 15.083 128.258 ↑ 451.1 91 1

GroupAggregate (cost=4,143.37..5,170.90 rows=41,053 width=14) (actual time=111.022..128.258 rows=91 loops=1)

  • Group Key: (date(timezone('UTC-1'::text, to_timestamp(player_1.createTime))))
4. 9.837 113.175 ↑ 1.0 41,213 1

Sort (cost=4,143.37..4,246.40 rows=41,213 width=14) (actual time=110.771..113.175 rows=41,213 loops=1)

  • Sort Key: (date(timezone('UTC-1'::text, to_timestamp(player_1.createTime))))
  • Sort Method: quicksort Memory: 3468kB
5. 103.338 103.338 ↑ 1.0 41,213 1

Seq Scan on player player_1 (cost=0.00..984.23 rows=41,213 width=14) (actual time=0.086..103.338 rows=41,213 loops=1)

6.          

CTE pp

7. 330.629 343.851 ↓ 1.3 53,635 1

Hash Right Join (cost=1,190.29..4,886.78 rows=41,213 width=149) (actual time=12.065..343.851 rows=53,635 loops=1)

  • Hash Cond: (payment_1.playerId = player_2.id)
8. 1.480 1.480 ↑ 1.0 13,543 1

Seq Scan on payment payment_1 (cost=0.00..831.43 rows=13,543 width=147) (actual time=0.001..1.480 rows=13,543 loops=1)

9. 7.033 11.742 ↑ 1.0 41,213 1

Hash (cost=675.13..675.13 rows=41,213 width=14) (actual time=11.742..11.742 rows=41,213 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2387kB
10. 4.709 4.709 ↑ 1.0 41,213 1

Seq Scan on player player_2 (cost=0.00..675.13 rows=41,213 width=14) (actual time=0.003..4.709 rows=41,213 loops=1)

11. 0.863 10,717.851 ↑ 346.9 2,664 1

Merge Left Join (cost=158,219.94..165,150.77 rows=924,047 width=36) (actual time=10,716.276..10,717.851 rows=2,664 loops=1)

  • Merge Cond: (((date(timezone('UTC-1'::text, to_timestamp(player.createTime)))) = pp.ppinstalldate) AND ((date(timezone('UTC-1'::text, to_timestamp(payment.createTime)))) = pp.pppmtdate))
12. 1.258 342.040 ↑ 346.9 2,664 1

Sort (cost=135,602.15..137,912.27 rows=924,047 width=8) (actual time=341.686..342.040 rows=2,664 loops=1)

  • Sort Key: (date(timezone('UTC-1'::text, to_timestamp(player.createTime)))), (date(timezone('UTC-1'::text, to_timestamp(payment.createTime))))
  • Sort Method: quicksort Memory: 221kB
13. 0.422 340.782 ↑ 346.9 2,664 1

Nested Loop (cost=2,081.78..44,040.15 rows=924,047 width=8) (actual time=340.210..340.782 rows=2,664 loops=1)

  • Join Filter: ((date(timezone('UTC-1'::text, to_timestamp(payment.createTime)))) >= (date(timezone('UTC-1'::text, to_timestamp(player.createTime)))))
  • Rows Removed by Join Filter: 703
14. 3.059 97.390 ↑ 147.9 91 1

HashAggregate (cost=580.33..815.82 rows=13,457 width=8) (actual time=97.365..97.390 rows=91 loops=1)

  • Group Key: date(timezone('UTC-1'::text, to_timestamp(payment.createTime)))
15. 94.331 94.331 ↓ 1.0 13,543 1

Index Only Scan using pmt_indx on payment (cost=0.29..546.64 rows=13,475 width=8) (actual time=0.176..94.331 rows=13,543 loops=1)

  • Filter: (date(timezone('UTC-1'::text, to_timestamp(createTime))) <> ('now'::cstring)::date)
  • Heap Fetches: 0
16. 0.123 242.970 ↑ 5.6 37 91

Materialize (cost=1,501.45..1,508.14 rows=206 width=4) (actual time=2.669..2.670 rows=37 loops=91)

17. 2.315 242.847 ↑ 5.6 37 1

HashAggregate (cost=1,501.45..1,505.06 rows=206 width=8) (actual time=242.842..242.847 rows=37 loops=1)

  • Group Key: date(timezone('UTC-1'::text, to_timestamp(player.createTime)))
18. 240.532 240.532 ↓ 69.1 14,241 1

Seq Scan on player (cost=0.00..1,500.93 rows=206 width=8) (actual time=1.527..240.532 rows=14,241 loops=1)

  • Filter: ((date(timezone('UTC-1'::text, to_timestamp(createTime))) >= '2017-01-02'::date) AND (date(timezone('UTC-1'::text, to_timestamp(createTime))) <= '2017-02-07'::date))
  • Rows Removed by Filter: 26972
19. 0.214 10,374.948 ↓ 112.5 2,250 1

Materialize (cost=22,617.79..22,618.09 rows=20 width=36) (actual time=10,374.583..10,374.948 rows=2,250 loops=1)

20. 1.348 10,374.734 ↓ 112.5 2,250 1

Sort (cost=22,617.79..22,617.84 rows=20 width=62) (actual time=10,374.577..10,374.734 rows=2,250 loops=1)

  • Sort Key: pp.ppinstalldate, pp.pppmtdate
  • Sort Method: quicksort Memory: 272kB
21. 9.068 10,373.386 ↓ 112.5 2,250 1

HashAggregate (cost=2,009.96..22,617.36 rows=20 width=62) (actual time=502.788..10,373.386 rows=2,250 loops=1)

  • Group Key: pp.ppinstalldate, pp.pppmtdate, insttable_1.numinstalls
22. 1.339 493.568 ↓ 44.4 9,103 1

Hash Right Join (cost=1,030.34..2,007.40 rows=205 width=62) (actual time=475.231..493.568 rows=9,103 loops=1)

  • Hash Cond: (insttable_1.instdate = pp.ppinstalldate)
23. 128.288 128.288 ↑ 451.1 91 1

CTE Scan on insttable insttable_1 (cost=0.00..821.06 rows=41,053 width=12) (actual time=111.024..128.288 rows=91 loops=1)

24. 1.980 363.941 ↓ 9,103.0 9,103 1

Hash (cost=1,030.33..1,030.33 rows=1 width=54) (actual time=363.941..363.941 rows=9,103 loops=1)

  • Buckets: 16384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 602kB
25. 361.961 361.961 ↓ 9,103.0 9,103 1

CTE Scan on pp (cost=0.00..1,030.33 rows=1 width=54) (actual time=13.277..361.961 rows=9,103 loops=1)

  • Filter: ((rollbackTime IS NULL) AND (NOT test) AND (ppinstalldate >= '2017-01-02'::date) AND (ppinstalldate <= '2017-02-07'::date))
  • Rows Removed by Filter: 44532
26.          

SubPlan (forHashAggregate)

27. 117.000 9,870.750 ↑ 1.0 1 2,250

Aggregate (cost=1,030.33..1,030.35 rows=1 width=46) (actual time=4.387..4.387 rows=1 loops=2,250)

28. 9,753.750 9,753.750 ↓ 151.0 151 2,250

CTE Scan on pp pp1 (cost=0.00..1,030.33 rows=1 width=46) (actual time=0.700..4.335 rows=151 loops=2,250)

  • Filter: ((rollbackTime IS NULL) AND (NOT test) AND (pppmtdate <= pp.pppmtdate) AND (ppinstalldate = pp.ppinstalldate))
  • Rows Removed by Filter: 53484
29. 0.191 0.204 ↑ 15.4 2,666 1

Sort (cost=3,966.79..4,069.42 rows=41,053 width=12) (actual time=0.052..0.204 rows=2,666 loops=1)

  • Sort Key: insttable.instdate
  • Sort Method: quicksort Memory: 29kB
30. 0.013 0.013 ↑ 451.1 91 1

CTE Scan on insttable (cost=0.00..821.06 rows=41,053 width=12) (actual time=0.003..0.013 rows=91 loops=1)