explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Ie43

Settings
# exclusive inclusive rows x rows loops node
1. 0.007 183,086.558 ↑ 1.0 50 1

Limit (cost=710,650,533.45..710,650,547.09 rows=50 width=1,292) (actual time=183,086.267..183,086.558 rows=50 loops=1)

  • Buffers: shared hit=17817462 read=7
2. 0.217 183,086.551 ↑ 1,704.0 50 1

Unique (cost=710,650,533.45..710,673,772.26 rows=85,200 width=1,292) (actual time=183,086.266..183,086.551 rows=50 loops=1)

  • Buffers: shared hit=17817462 read=7
3. 11.713 183,086.334 ↑ 1,094.1 708 1

Sort (cost=710,650,533.45..710,652,470.02 rows=774,627 width=1,292) (actual time=183,086.265..183,086.334 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=17817462 read=7
4. 72.967 183,074.621 ↑ 168.4 4,600 1

Hash Left Join (cost=29,930.83..710,325,126.76 rows=774,627 width=1,292) (actual time=356.951..183,074.621 rows=4,600 loops=1)

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

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

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

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

  • Buffers: shared hit=17050
7. 117.319 117.319 ↑ 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.120..117.319 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.122 9.412 ↑ 1.0 426 1

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

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

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

  • Buffers: shared read=7
11.          

SubPlan (forHash Left Join)

12. 88,329.200 182,555.600 ↑ 1.0 1 4,600

Aggregate (cost=906.69..906.70 rows=1 width=32) (actual time=39.686..39.686 rows=1 loops=4,600)

  • Buffers: shared hit=17703812
13. 94,226.400 94,226.400 ↓ 30.3 65,298 4,600

Index Scan using dbatest on dm_billingitemavailabletobill_facts lingitemavailabletobill_facts1 (cost=0.42..901.29 rows=2,156 width=3) (actual time=0.014..20.484 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. 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=13800
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=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. 9.200 13.800 ↑ 1.0 1 4,600

Aggregate (cost=1.45..1.46 rows=1 width=32) (actual time=0.003..0.003 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 : 5.789 ms
Execution time : 183,091.181 ms