explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cfS5

Settings
# exclusive inclusive rows x rows loops node
1. 0.007 194,290.093 ↑ 1.0 50 1

Limit (cost=717,056,698.74..717,056,712.38 rows=50 width=1,292) (actual time=194,289.695..194,290.093 rows=50 loops=1)

  • Buffers: shared hit=17817469
2. 0.307 194,290.086 ↑ 1,704.0 50 1

Unique (cost=717,056,698.74..717,079,937.55 rows=85,200 width=1,292) (actual time=194,289.695..194,290.086 rows=50 loops=1)

  • Buffers: shared hit=17817469
3. 11.788 194,289.779 ↑ 1,094.1 708 1

Sort (cost=717,056,698.74..717,058,635.31 rows=774,627 width=1,292) (actual time=194,289.693..194,289.779 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=17817469
4. 65.549 194,277.991 ↑ 168.4 4,600 1

Hash Left Join (cost=29,930.83..716,731,292.05 rows=774,627 width=1,292) (actual time=388.517..194,277.991 rows=4,600 loops=1)

  • Hash Cond: (dm_billingitemavailabletobill_facts.clientid = clients0.id)
  • Buffers: shared hit=17817469
5. 185.170 386.496 ↑ 168.4 4,600 1

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

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

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

  • Buffers: shared hit=17050
7. 123.229 123.229 ↑ 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.118..123.229 rows=774,017 loops=1)

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

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

9. 0.170 0.346 ↑ 1.0 426 1

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

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

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

  • Buffers: shared hit=7
11.          

SubPlan (forHash Left Join)

12. 95,192.400 193,710.600 ↑ 1.0 1 4,600

Aggregate (cost=911.89..911.90 rows=1 width=32) (actual time=42.111..42.111 rows=1 loops=4,600)

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

Index Scan using dbatest on dm_billingitemavailabletobill_facts lingitemavailabletobill_facts1 (cost=0.42..906.47 rows=2,168 width=3) (actual time=0.013..21.417 rows=65,298 loops=4,600)

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

Aggregate (cost=1.89..1.90 rows=1 width=32) (actual time=0.007..0.007 rows=1 loops=4,600)

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

Index Scan using dbatest on dm_billingitemavailabletobill_facts lingitemavailabletobill_facts3 (cost=0.42..1.88 rows=1 width=3) (actual time=0.005..0.005 rows=0 loops=4,600)

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

Aggregate (cost=1.89..1.90 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=4,600)

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

Index Scan using dbatest on dm_billingitemavailabletobill_facts lingitemavailabletobill_facts4 (cost=0.42..1.88 rows=1 width=3) (actual time=0.002..0.002 rows=0 loops=4,600)

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

Aggregate (cost=1.89..1.90 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=4,600)

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

Index Scan using dbatest on dm_billingitemavailabletobill_facts lingitemavailabletobill_facts5 (cost=0.42..1.88 rows=1 width=3) (actual time=0.002..0.002 rows=0 loops=4,600)

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

Aggregate (cost=1.89..1.90 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=4,600)

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

Index Scan using dbatest on dm_billingitemavailabletobill_facts lingitemavailabletobill_facts6 (cost=0.42..1.88 rows=1 width=3) (actual time=0.002..0.002 rows=0 loops=4,600)

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

Aggregate (cost=1.89..1.90 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=4,600)

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

Index Scan using dbatest on dm_billingitemavailabletobill_facts lingitemavailabletobill_facts7 (cost=0.42..1.88 rows=1 width=3) (actual time=0.002..0.002 rows=0 loops=4,600)

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

Aggregate (cost=1.89..1.90 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=4,600)

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

Index Scan using dbatest on dm_billingitemavailabletobill_facts lingitemavailabletobill_facts8 (cost=0.42..1.88 rows=1 width=3) (actual time=0.002..0.002 rows=0 loops=4,600)

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

Aggregate (cost=1.89..1.90 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=4,600)

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

Index Scan using dbatest on dm_billingitemavailabletobill_facts lingitemavailabletobill_facts9 (cost=0.42..1.88 rows=1 width=3) (actual time=0.002..0.002 rows=0 loops=4,600)

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