explain.depesz.com

PostgreSQL's explain analyze made readable

Result: wFCx

Settings
# exclusive inclusive rows x rows loops node
1. 0.007 147,294.118 ↑ 1.0 50 1

Limit (cost=1,490,023,841.09..1,490,023,854.73 rows=50 width=1,292) (actual time=147,293.837..147,294.118 rows=50 loops=1)

  • Buffers: shared hit=21037457 read=11595
2. 0.221 147,294.111 ↑ 1,704.0 50 1

Unique (cost=1,490,023,841.09..1,490,047,079.75 rows=85,200 width=1,292) (actual time=147,293.837..147,294.111 rows=50 loops=1)

  • Buffers: shared hit=21037457 read=11595
3. 9.672 147,293.890 ↑ 1,094.1 708 1

Sort (cost=1,490,023,841.09..1,490,025,777.65 rows=774,622 width=1,292) (actual time=147,293.835..147,293.890 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=21037457 read=11595
4. 38.579 147,284.218 ↑ 168.4 4,600 1

Hash Left Join (cost=29,930.69..1,489,698,434.93 rows=774,622 width=1,292) (actual time=739.796..147,284.218 rows=4,600 loops=1)

  • Hash Cond: (dm_billingitemavailabletobill_facts.clientid = clients0.id)
  • Buffers: shared hit=21037444 read=11595
5. 184.912 715.973 ↑ 168.4 4,600 1

HashAggregate (cost=29,914.10..37,660.32 rows=774,622 width=12) (actual time=709.520..715.973 rows=4,600 loops=1)

  • Group Key: dm_billingitemavailabletobill_facts.clientid, dm_billingitemavailabletobill_facts.projectid, (0)
  • Buffers: shared hit=8448 read=8602
6. 56.466 531.061 ↑ 1.0 774,017 1

Append (cost=0.00..24,104.44 rows=774,622 width=12) (actual time=5.624..531.061 rows=774,017 loops=1)

  • Buffers: shared hit=8448 read=8602
7. 474.593 474.593 ↓ 1.0 774,017 1

Seq Scan on dm_billingitemavailabletobill_facts (cost=0.00..16,342.12 rows=774,012 width=12) (actual time=5.624..474.593 rows=774,017 loops=1)

  • Buffers: shared hit=8448 read=8602
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.103 10.466 ↑ 1.0 426 1

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

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

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

  • Buffers: shared read=7
11.          

SubPlan (forHash Left Join)

12. 57,702.400 146,441.000 ↑ 1.0 1 4,600

Aggregate (cost=1,912.83..1,912.84 rows=1 width=32) (actual time=31.835..31.835 rows=1 loops=4,600)

  • Buffers: shared hit=20932399 read=2983
13. 88,738.600 88,738.600 ↓ 30.5 65,298 4,600

Index Scan using ixdbiafclientidcoalesce on dm_billingitemavailabletobill_facts lingitemavailabletobill_facts1 (cost=0.42..1,907.48 rows=2,138 width=3) (actual time=0.027..19.291 rows=65,298 loops=4,600)

  • Index Cond: (COALESCE(clientid, 0) = COALESCE(dm_billingitemavailabletobill_facts.clientid, 0))
  • Filter: (currencyid = 1)
  • Buffers: shared hit=20932399 read=2983
14. 4.600 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=13797 read=3
15. 18.400 18.400 ↓ 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.004..0.004 rows=0 loops=4,600)

  • Index Cond: (currencyid = 2)
  • Filter: (COALESCE(clientid, 0) = COALESCE(dm_billingitemavailabletobill_facts.clientid, 0))
  • Buffers: shared hit=13797 read=3
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
Planning time : 40.444 ms
Execution time : 147,298.774 ms