explain.depesz.com

PostgreSQL's explain analyze made readable

Result: lMTT

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 163,154.971 ↑ 1.0 50 1

Limit (cost=1,520,156,146.06..1,520,156,159.70 rows=50 width=1,292) (actual time=163,154.688..163,154.971 rows=50 loops=1)

  • Buffers: shared hit=17817465 read=7
2. 0.215 163,154.968 ↑ 1,704.0 50 1

Unique (cost=1,520,156,146.06..1,520,179,384.87 rows=85,200 width=1,292) (actual time=163,154.687..163,154.968 rows=50 loops=1)

  • Buffers: shared hit=17817465 read=7
3. 11.258 163,154.753 ↑ 1,094.1 708 1

Sort (cost=1,520,156,146.06..1,520,158,082.63 rows=774,627 width=1,292) (actual time=163,154.685..163,154.753 rows=708 loops=1)

  • Sort Key: ((clients0.name)::character varying(255)) COLLATE "en_US", clients0.id, ((clients0.slug)::character varying(255)) COLLATE "en_US", ((SubPlan 1)), ((SubPlan 2)), ((SubPlan 3)), ((SubPlan 4)), ((SubPlan 5)), ((SubPlan 6)), ((SubPlan 7)), ((SubPlan 8))
  • Sort Method: quicksort Memory: 719kB
  • Buffers: shared hit=17817465 read=7
4. 85.933 163,143.495 ↑ 168.4 4,600 1

Hash Left Join (cost=29,930.83..1,519,830,739.37 rows=774,627 width=1,292) (actual time=516.823..163,143.495 rows=4,600 loops=1)

  • Hash Cond: (dm_billingitemavailabletobill_facts.clientid = clients0.id)
  • Buffers: shared hit=17817462 read=7
5. 236.281 446.542 ↑ 168.4 4,600 1

HashAggregate (cost=29,914.24..37,660.51 rows=774,627 width=12) (actual time=439.348..446.542 rows=4,600 loops=1)

  • Group Key: dm_billingitemavailabletobill_facts.clientid, dm_billingitemavailabletobill_facts.projectid, (0)
  • Buffers: shared hit=17050
6. 67.067 210.261 ↑ 1.0 774,017 1

Append (cost=0.00..24,104.54 rows=774,627 width=12) (actual time=0.124..210.261 rows=774,017 loops=1)

  • Buffers: shared hit=17050
7. 143.192 143.192 ↑ 1.0 774,017 1

Seq Scan on dm_billingitemavailabletobill_facts (cost=0.00..16,342.17 rows=774,017 width=12) (actual time=0.124..143.192 rows=774,017 loops=1)

  • Buffers: shared hit=17050
8. 0.002 0.002 ↓ 0.0 0 1

Seq Scan on dm_billingentry_facts (cost=0.00..16.10 rows=610 width=12) (actual time=0.002..0.002 rows=0 loops=1)

9. 0.193 74.620 ↑ 1.0 426 1

Hash (cost=11.26..11.26 rows=426 width=40) (actual time=74.620..74.620 rows=426 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 39kB
  • Buffers: shared read=7
10. 74.427 74.427 ↑ 1.0 426 1

Seq Scan on clients clients0 (cost=0.00..11.26 rows=426 width=40) (actual time=37.611..74.427 rows=426 loops=1)

  • Buffers: shared read=7
11.          

SubPlan (forHash Left Join)

12. 78,269.000 162,458.200 ↑ 1.0 1 4,600

Aggregate (cost=1,951.71..1,951.72 rows=1 width=32) (actual time=35.317..35.317 rows=1 loops=4,600)

  • Buffers: shared hit=17703812
13. 84,189.200 84,189.200 ↓ 30.1 65,298 4,600

Index Scan using dbatest on dm_billingitemavailabletobill_facts lingitemavailabletobill_facts1 (cost=0.42..1,946.29 rows=2,168 width=3) (actual time=0.011..18.302 rows=65,298 loops=4,600)

  • Index Cond: ((COALESCE(clientid, 0) = COALESCE(dm_billingitemavailabletobill_facts.clientid, 0)) AND (currencyid = 1))
  • Buffers: shared hit=17703812
14. 9.200 23.000 ↑ 1.0 1 4,600

Aggregate (cost=1.45..1.46 rows=1 width=32) (actual time=0.005..0.005 rows=1 loops=4,600)

  • Buffers: shared hit=13800
15. 13.800 13.800 ↓ 0.0 0 4,600

Index Scan using ixdbiafcurrencyid on dm_billingitemavailabletobill_facts lingitemavailabletobill_facts3 (cost=0.42..1.45 rows=1 width=3) (actual time=0.003..0.003 rows=0 loops=4,600)

  • Index Cond: (currencyid = 2)
  • Filter: (COALESCE(clientid, 0) = COALESCE(dm_billingitemavailabletobill_facts.clientid, 0))
  • Buffers: shared hit=13800
16. 4.600 9.200 ↑ 1.0 1 4,600

Aggregate (cost=1.45..1.46 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=4,600)

  • Buffers: shared hit=13800
17. 4.600 4.600 ↓ 0.0 0 4,600

Index Scan using ixdbiafcurrencyid on dm_billingitemavailabletobill_facts lingitemavailabletobill_facts4 (cost=0.42..1.45 rows=1 width=3) (actual time=0.001..0.001 rows=0 loops=4,600)

  • Index Cond: (currencyid = 3)
  • Filter: (COALESCE(clientid, 0) = COALESCE(dm_billingitemavailabletobill_facts.clientid, 0))
  • Buffers: shared hit=13800
18. 4.600 9.200 ↑ 1.0 1 4,600

Aggregate (cost=1.45..1.46 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=4,600)

  • Buffers: shared hit=13800
19. 4.600 4.600 ↓ 0.0 0 4,600

Index Scan using ixdbiafcurrencyid on dm_billingitemavailabletobill_facts lingitemavailabletobill_facts5 (cost=0.42..1.45 rows=1 width=3) (actual time=0.001..0.001 rows=0 loops=4,600)

  • Index Cond: (currencyid = 4)
  • Filter: (COALESCE(clientid, 0) = COALESCE(dm_billingitemavailabletobill_facts.clientid, 0))
  • Buffers: shared hit=13800
20. 4.600 9.200 ↑ 1.0 1 4,600

Aggregate (cost=1.45..1.46 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=4,600)

  • Buffers: shared hit=13800
21. 4.600 4.600 ↓ 0.0 0 4,600

Index Scan using ixdbiafcurrencyid on dm_billingitemavailabletobill_facts lingitemavailabletobill_facts6 (cost=0.42..1.45 rows=1 width=3) (actual time=0.001..0.001 rows=0 loops=4,600)

  • Index Cond: (currencyid = 5)
  • Filter: (COALESCE(clientid, 0) = COALESCE(dm_billingitemavailabletobill_facts.clientid, 0))
  • Buffers: shared hit=13800
22. 4.600 9.200 ↑ 1.0 1 4,600

Aggregate (cost=1.45..1.46 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=4,600)

  • Buffers: shared hit=13800
23. 4.600 4.600 ↓ 0.0 0 4,600

Index Scan using ixdbiafcurrencyid on dm_billingitemavailabletobill_facts lingitemavailabletobill_facts7 (cost=0.42..1.45 rows=1 width=3) (actual time=0.001..0.001 rows=0 loops=4,600)

  • Index Cond: (currencyid = 6)
  • Filter: (COALESCE(clientid, 0) = COALESCE(dm_billingitemavailabletobill_facts.clientid, 0))
  • Buffers: shared hit=13800
24. 4.600 9.200 ↑ 1.0 1 4,600

Aggregate (cost=1.45..1.46 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=4,600)

  • Buffers: shared hit=13800
25. 4.600 4.600 ↓ 0.0 0 4,600

Index Scan using ixdbiafcurrencyid on dm_billingitemavailabletobill_facts lingitemavailabletobill_facts8 (cost=0.42..1.45 rows=1 width=3) (actual time=0.001..0.001 rows=0 loops=4,600)

  • Index Cond: (currencyid = 7)
  • Filter: (COALESCE(clientid, 0) = COALESCE(dm_billingitemavailabletobill_facts.clientid, 0))
  • Buffers: shared hit=13800
26. 4.600 9.200 ↑ 1.0 1 4,600

Aggregate (cost=1.45..1.46 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=4,600)

  • Buffers: shared hit=13800
27. 4.600 4.600 ↓ 0.0 0 4,600

Index Scan using ixdbiafcurrencyid on dm_billingitemavailabletobill_facts lingitemavailabletobill_facts9 (cost=0.42..1.45 rows=1 width=3) (actual time=0.001..0.001 rows=0 loops=4,600)

  • Index Cond: (currencyid = 8)
  • Filter: (COALESCE(clientid, 0) = COALESCE(dm_billingitemavailabletobill_facts.clientid, 0))
  • Buffers: shared hit=13800