explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6Ibd

Settings
# exclusive inclusive rows x rows loops node
1. 0.015 2,262.277 ↓ 6.0 6 1

Group (cost=139,476.77..144,004.30 rows=1 width=643) (actual time=1,598.662..2,262.277 rows=6 loops=1)

  • Group Key: dl.name, dlitem.id
2. 0.046 2,262.262 ↓ 6.0 6 1

Nested Loop (cost=139,476.77..144,004.29 rows=1 width=643) (actual time=1,598.659..2,262.262 rows=6 loops=1)

3. 117.647 2,261.886 ↓ 33.0 33 1

Nested Loop Semi Join (cost=139,476.48..144,003.89 rows=1 width=643) (actual time=1,595.337..2,261.886 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.362 18.552 ↓ 37.0 37 1

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

  • Join Filter: (dl.id = dlitem.data_list_id)
  • Rows Removed by Join Filter: 19,874
5. 9.190 9.190 ↑ 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.190 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.013 0.679 ↑ 1.0 1 1

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

  • Hash Cond: (dlgc.data_list_id = dl.id)
8. 0.342 0.342 ↑ 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.342 rows=31 loops=1)

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

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

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

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

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

Nested Loop (cost=139,453.05..140,960.14 rows=10,048 width=658) (actual time=43.060..57.451 rows=2,327 loops=37)

12. 233.956 1,633.698 ↓ 11.6 2,327 37

HashAggregate (cost=139,452.64..139,454.64 rows=200 width=8) (actual time=43.053..44.154 rows=2,327 loops=37)

  • Group Key: vcl.document_id
13. 1,119.564 1,399.742 ↓ 1.4 459,719 1

Bitmap Heap Scan on vcl (cost=25,422.07..138,615.39 rows=334,899 width=8) (actual time=298.560..1,399.742 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[])))
  • Rows Removed by Index Recheck: 1,143,570
  • Filter: ((office_id IS NULL) OR (office_id = 119))
  • Rows Removed by Filter: 129,850
  • Heap Blocks: exact=41,305 lossy=26,639
14. 0.002 280.178 ↓ 0.0 0 1

BitmapOr (cost=25,422.07..25,422.07 rows=587,866 width=0) (actual time=280.178..280.178 rows=0 loops=1)

15. 5.637 5.637 ↓ 2.1 31,348 1

Bitmap Index Scan on vcl_profile_id_version_id_idx (cost=0.00..662.76 rows=14,711 width=0) (actual time=5.637..5.637 rows=31,348 loops=1)

  • Index Cond: (profile_id IS NULL)
16. 274.539 274.539 ↓ 2.0 1,152,451 1

Bitmap Index Scan on vcl_profile_id_version_id_idx (cost=0.00..24,591.86 rows=573,156 width=0) (actual time=274.539..274.539 rows=1,152,451 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.330 0.330 ↓ 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.010..0.010 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.248 ms
Execution time : 2,262.415 ms