explain.depesz.com

PostgreSQL's explain analyze made readable

Result: R1b1

Settings
# exclusive inclusive rows x rows loops node
1. 0.020 1,391.748 ↓ 6.0 6 1

Group (cost=124,760.77..129,288.30 rows=1 width=643) (actual time=739.100..1,391.748 rows=6 loops=1)

  • Group Key: dl.name, dlitem.id
2. 0.043 1,391.728 ↓ 6.0 6 1

Nested Loop (cost=124,760.77..129,288.29 rows=1 width=643) (actual time=739.095..1,391.728 rows=6 loops=1)

3. 112.692 1,391.388 ↓ 33.0 33 1

Nested Loop Semi Join (cost=124,760.48..129,287.89 rows=1 width=643) (actual time=736.376..1,391.388 rows=33 loops=1)

  • Join Filter: ((((doc.content -> 'model'::text) -> (dl.name)::text) ->> 'acronym'::text) = ((dlitem.content -> 'properties'::text) ->> 'acronym'::text))
  • Rows Removed by Join Filter: 86,069
4. 9.258 18.809 ↓ 37.0 37 1

Nested Loop (cost=23.43..1,285.47 rows=1 width=643) (actual time=0.883..18.809 rows=37 loops=1)

  • Join Filter: (dl.id = dlitem.data_list_id)
  • Rows Removed by Join Filter: 19,874
5. 9.551 9.551 ↑ 1.0 19,911 1

Index Scan using "data_list_itemPK" on data_list_item dlitem (cost=0.29..939.44 rows=19,911 width=631) (actual time=0.010..9.551 rows=19,911 loops=1)

6. 0.000 0.000 ↑ 1.0 1 19,911

Materialize (cost=23.14..47.37 rows=1 width=28) (actual time=0.000..0.000 rows=1 loops=19,911)

7. 0.016 0.575 ↑ 1.0 1 1

Hash Join (cost=23.14..47.36 rows=1 width=28) (actual time=0.320..0.575 rows=1 loops=1)

  • Hash Cond: (dlgc.data_list_id = dl.id)
8. 0.272 0.272 ↑ 1.0 31 1

Seq Scan on data_list_config_item_group dlgc (cost=0.00..24.10 rows=31 width=8) (actual time=0.022..0.272 rows=31 loops=1)

  • Filter: (config_item_group_id = 104)
  • Rows Removed by Filter: 1,177
9. 0.003 0.287 ↑ 3.0 1 1

Hash (cost=23.10..23.10 rows=3 width=20) (actual time=0.287..0.287 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
10. 0.284 0.284 ↑ 3.0 1 1

Seq Scan on data_list dl (cost=0.00..23.10 rows=3 width=20) (actual time=0.011..0.284 rows=1 loops=1)

  • Filter: ((name)::text = 'trecho'::text)
  • Rows Removed by Filter: 1,207
11. 53.413 1,259.887 ↑ 4.3 2,327 37

Nested Loop (cost=124,737.05..126,244.14 rows=10,048 width=658) (actual time=19.862..34.051 rows=2,327 loops=37)

12. 225.625 775.964 ↓ 11.6 2,327 37

HashAggregate (cost=124,736.64..124,738.64 rows=200 width=8) (actual time=19.855..20.972 rows=2,327 loops=37)

  • Group Key: vcl.document_id
13. 462.971 550.339 ↓ 1.4 459,719 1

Bitmap Heap Scan on vcl (cost=10,706.07..123,899.39 rows=334,899 width=8) (actual time=101.152..550.339 rows=459,719 loops=1)

  • Recheck Cond: ((profile_id IS NULL) OR (profile_id = ANY ('{144,155,1492,1948,146,2012,149,147,153,150,1493,2286}'::bigint[])))
  • Filter: ((office_id IS NULL) OR (office_id = 119))
  • Rows Removed by Filter: 129,850
  • Heap Blocks: exact=35,362
14. 0.002 87.368 ↓ 0.0 0 1

BitmapOr (cost=10,706.07..10,706.07 rows=587,866 width=0) (actual time=87.368..87.368 rows=0 loops=1)

15. 1.902 1.902 ↓ 1.0 15,385 1

Bitmap Index Scan on vcl_profile_id_idx (cost=0.00..274.76 rows=14,711 width=0) (actual time=1.902..1.902 rows=15,385 loops=1)

  • Index Cond: (profile_id IS NULL)
16. 85.464 85.464 ↓ 1.0 574,184 1

Bitmap Index Scan on vcl_profile_id_idx (cost=0.00..10,263.86 rows=573,156 width=0) (actual time=85.464..85.464 rows=574,184 loops=1)

  • Index Cond: (profile_id = ANY ('{144,155,1492,1948,146,2012,149,147,153,150,1493,2286}'::bigint[]))
17. 430.510 430.510 ↑ 1.0 1 86,102

Index Scan using "documentPK" on document doc (cost=0.42..7.52 rows=1 width=666) (actual time=0.005..0.005 rows=1 loops=86,102)

  • Index Cond: (id = vcl.document_id)
  • Filter: (project_id = 104)
18. 0.297 0.297 ↓ 0.0 0 33

Index Scan using "data_list_itemPK" on data_list_item dlitemparent (cost=0.29..0.39 rows=1 width=8) (actual time=0.009..0.009 rows=0 loops=33)

  • Index Cond: (id = dlitem.parent_id)
  • Filter: ((content -> 'properties'::text) = '{"name": "Seccionamento", "acronym": "SC"}'::jsonb)
  • Rows Removed by Filter: 1
Planning time : 1.078 ms
Execution time : 1,391.879 ms