explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BPSL

Settings
# exclusive inclusive rows x rows loops node
1. 0.350 119.013 ↑ 1.0 1 1

Seq Scan on pers_coll personforc0_ (cost=0.00..61,837.45 rows=1 width=52) (actual time=119.012..119.013 rows=1 loops=1)

  • Filter: (id = 1)
  • Rows Removed by Filter: 57
2.          

SubPlan (for Seq Scan)

3. 7.681 118.663 ↑ 1.0 1 1

Aggregate (cost=61,835.71..61,835.72 rows=1 width=32) (actual time=118.663..118.663 rows=1 loops=1)

4. 0.286 0.785 ↓ 2.9 419 1

Hash Left Join (cost=54.91..83.52 rows=146 width=572) (actual time=0.070..0.785 rows=419 loops=1)

  • Hash Cond: (personlist4_.person_id = personforc5_.id)
5. 0.252 0.483 ↓ 2.9 419 1

Hash Left Join (cost=52.60..79.37 rows=146 width=560) (actual time=0.052..0.483 rows=419 loops=1)

  • Hash Cond: (documentfo1_.id = personlist4_.id)
6. 0.030 0.219 ↓ 3.2 39 1

Nested Loop Left Join (cost=4.35..27.52 rows=12 width=548) (actual time=0.029..0.219 rows=39 loops=1)

7. 0.009 0.009 ↓ 20.0 20 1

Seq Scan on doc_coll documentfo1_ (cost=0.00..11.75 rows=1 width=524) (actual time=0.006..0.009 rows=20 loops=1)

  • Filter: (owner_id = personforc0_.id)
8. 0.100 0.180 ↑ 8.0 1 20

Nested Loop Left Join (cost=4.35..15.69 rows=8 width=32) (actual time=0.005..0.009 rows=1 loops=20)

9. 0.020 0.040 ↑ 8.0 1 20

Bitmap Heap Scan on doc_coll_contacts contacts2_ (cost=4.21..14.37 rows=8 width=20) (actual time=0.001..0.002 rows=1 loops=20)

  • Recheck Cond: (documentfo1_.id = id)
  • Heap Blocks: exact=1
10. 0.020 0.020 ↑ 8.0 1 20

Bitmap Index Scan on doc_coll_contacts_pkey (cost=0.00..4.21 rows=8 width=0) (actual time=0.001..0.001 rows=1 loops=20)

  • Index Cond: (documentfo1_.id = id)
11. 0.040 0.040 ↑ 1.0 1 20

Index Scan using pers_coll_pkey on pers_coll personforc3_ (cost=0.14..0.16 rows=1 width=20) (actual time=0.002..0.002 rows=1 loops=20)

  • Index Cond: (contacts2_.person_id = id)
12. 0.005 0.012 ↑ 85.0 20 1

Hash (cost=27.00..27.00 rows=1,700 width=20) (actual time=0.012..0.012 rows=20 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 18kB
13. 0.007 0.007 ↑ 85.0 20 1

Seq Scan on personlist personlist4_ (cost=0.00..27.00 rows=1,700 width=20) (actual time=0.005..0.007 rows=20 loops=1)

14. 0.008 0.016 ↑ 1.0 58 1

Hash (cost=1.58..1.58 rows=58 width=20) (actual time=0.016..0.016 rows=58 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
15. 0.008 0.008 ↑ 1.0 58 1

Seq Scan on pers_coll personforc5_ (cost=0.00..1.58 rows=58 width=20) (actual time=0.004..0.008 rows=58 loops=1)

16.          

SubPlan (for Aggregate)

17. 2.095 14.246 ↑ 1.0 1 419

Aggregate (cost=33.66..33.67 rows=1 width=32) (actual time=0.034..0.034 rows=1 loops=419)

18. 0.834 12.151 ↓ 0.0 0 419

Hash Join (cost=29.23..31.11 rows=255 width=20) (actual time=0.029..0.029 rows=0 loops=419)

  • Hash Cond: (personforc9_.id = somecollec8_.somecollection_id)
19. 0.004 0.004 ↑ 58.0 1 1

Seq Scan on pers_coll personforc9_ (cost=0.00..1.58 rows=58 width=20) (actual time=0.004..0.004 rows=1 loops=1)

20. 0.419 11.313 ↓ 0.0 0 419

Hash (cost=26.05..26.05 rows=255 width=8) (actual time=0.027..0.027 rows=0 loops=419)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
21. 4.190 10.894 ↓ 0.0 0 419

Nested Loop (cost=6.67..26.05 rows=255 width=8) (actual time=0.026..0.026 rows=0 loops=419)

22. 4.573 6.704 ↓ 2.4 19 419

Hash Join (cost=6.52..16.78 rows=8 width=16) (actual time=0.010..0.016 rows=19 loops=419)

  • Hash Cond: (contacts6_.person_id = personforc7_.id)
23. 1.257 2.095 ↓ 2.4 19 419

Bitmap Heap Scan on doc_coll_contacts contacts6_ (cost=4.21..14.37 rows=8 width=8) (actual time=0.004..0.005 rows=19 loops=419)

  • Recheck Cond: (documentfo1_.id = id)
  • Heap Blocks: exact=400
24. 0.838 0.838 ↓ 2.4 19 419

Bitmap Index Scan on doc_coll_contacts_pkey (cost=0.00..4.21 rows=8 width=0) (actual time=0.002..0.002 rows=19 loops=419)

  • Index Cond: (documentfo1_.id = id)
25. 0.006 0.036 ↑ 1.0 58 1

Hash (cost=1.58..1.58 rows=58 width=8) (actual time=0.036..0.036 rows=58 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
26. 0.030 0.030 ↑ 1.0 58 1

Seq Scan on pers_coll personforc7_ (cost=0.00..1.58 rows=58 width=8) (actual time=0.004..0.030 rows=58 loops=1)

27. 0.000 0.000 ↓ 0.0 0 8,000

Index Only Scan using pers_coll_some_coll_pkey on pers_coll_some_coll somecollec8_ (cost=0.15..1.07 rows=9 width=16) (actual time=0.000..0.000 rows=0 loops=8,000)

  • Index Cond: (personforcollections_id = personforc7_.id)
  • Heap Fetches: 0
28. 29.973 73.325 ↑ 1.0 1 419

Aggregate (cost=347.65..347.66 rows=1 width=32) (actual time=0.175..0.175 rows=1 loops=419)

29. 3.352 3.352 ↑ 1.1 19 419

Seq Scan on pers_coll personforc10_ (cost=0.00..1.73 rows=20 width=20) (actual time=0.004..0.008 rows=19 loops=419)

  • Filter: (partner_doc_id = documentfo1_.id)
  • Rows Removed by Filter: 39
30.          

SubPlan (for Aggregate)

31. 0.000 40.000 ↑ 1.0 1 8,000

Aggregate (cost=17.28..17.29 rows=1 width=32) (actual time=0.005..0.005 rows=1 loops=8,000)

32. 31.983 40.000 ↓ 0.0 0 8,000

Hash Join (cost=6.53..17.18 rows=9 width=20) (actual time=0.005..0.005 rows=0 loops=8,000)

  • Hash Cond: (somecollec11_.somecollection_id = personforc12_.id)
33. 8.000 8.000 ↓ 0.0 0 8,000

Bitmap Heap Scan on pers_coll_some_coll somecollec11_ (cost=4.22..14.76 rows=9 width=8) (actual time=0.001..0.001 rows=0 loops=8,000)

  • Recheck Cond: (personforc10_.id = personforcollections_id)
34. 0.000 0.000 ↓ 0.0 0 8,000

Bitmap Index Scan on pers_coll_some_coll_pkey (cost=0.00..4.22 rows=9 width=0) (actual time=0.000..0.000 rows=0 loops=8,000)

  • Index Cond: (personforc10_.id = personforcollections_id)
35. 0.009 0.017 ↑ 1.0 58 1

Hash (cost=1.58..1.58 rows=58 width=20) (actual time=0.017..0.017 rows=58 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
36. 0.008 0.008 ↑ 1.0 58 1

Seq Scan on pers_coll personforc12_ (cost=0.00..1.58 rows=58 width=20) (actual time=0.004..0.008 rows=58 loops=1)

37. 2.095 22.626 ↑ 1.0 1 419

Aggregate (cost=41.59..41.60 rows=1 width=32) (actual time=0.054..0.054 rows=1 loops=419)

38. 1.253 20.531 ↓ 0.0 0 419

Hash Join (cost=37.16..39.04 rows=255 width=20) (actual time=0.049..0.049 rows=0 loops=419)

  • Hash Cond: (personforc17_.id = somecollec16_.somecollection_id)
39. 0.004 0.004 ↑ 58.0 1 1

Seq Scan on pers_coll personforc17_ (cost=0.00..1.58 rows=58 width=20) (actual time=0.004..0.004 rows=1 loops=1)

40. 0.000 19.274 ↓ 0.0 0 419

Hash (cost=33.97..33.97 rows=255 width=8) (actual time=0.046..0.046 rows=0 loops=419)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
41. 6.704 19.274 ↓ 0.0 0 419

Nested Loop (cost=22.87..33.97 rows=255 width=8) (actual time=0.046..0.046 rows=0 loops=419)

  • Join Filter: (personlist14_.person_id = somecollec16_.personforcollections_id)
42. 3.866 12.570 ↓ 2.4 19 419

Hash Join (cost=22.71..24.52 rows=8 width=16) (actual time=0.020..0.030 rows=19 loops=419)

  • Hash Cond: (personforc15_.id = personlist14_.person_id)
43. 2.000 2.000 ↑ 1.0 58 400

Seq Scan on pers_coll personforc15_ (cost=0.00..1.58 rows=58 width=8) (actual time=0.002..0.005 rows=58 loops=400)

44. 1.257 6.704 ↓ 2.4 19 419

Hash (cost=22.61..22.61 rows=8 width=8) (actual time=0.016..0.016 rows=19 loops=419)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
45. 2.933 5.447 ↓ 2.4 19 419

Nested Loop (cost=4.36..22.61 rows=8 width=8) (actual time=0.010..0.013 rows=19 loops=419)

46. 0.838 0.838 ↑ 1.0 1 419

Index Only Scan using doc_coll_pkey on doc_coll documentfo13_ (cost=0.14..8.16 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=419)

  • Index Cond: (id = documentfo1_.id)
  • Heap Fetches: 419
47. 0.838 1.676 ↓ 2.4 19 419

Bitmap Heap Scan on personlist personlist14_ (cost=4.21..14.37 rows=8 width=16) (actual time=0.003..0.004 rows=19 loops=419)

  • Recheck Cond: (id = documentfo1_.id)
  • Heap Blocks: exact=400
48. 0.838 0.838 ↓ 2.4 19 419

Bitmap Index Scan on personlist_pkey (cost=0.00..4.21 rows=8 width=0) (actual time=0.002..0.002 rows=19 loops=419)

  • Index Cond: (id = documentfo1_.id)
49. 0.000 0.000 ↓ 0.0 0 8,000

Index Only Scan using pers_coll_some_coll_pkey on pers_coll_some_coll somecollec16_ (cost=0.15..1.07 rows=9 width=16) (actual time=0.000..0.000 rows=0 loops=8,000)

  • Index Cond: (personforcollections_id = personforc15_.id)
  • Heap Fetches: 0
Planning time : 1.845 ms
Execution time : 119.772 ms