explain.depesz.com

PostgreSQL's explain analyze made readable

Result: lFe7

Settings
# exclusive inclusive rows x rows loops node
1. 0.096 423.353 ↑ 8.3 6 1

Group (cost=134,495.60..134,495.97 rows=50 width=643) (actual time=423.193..423.353 rows=6 loops=1)

  • Group Key: dl.name, dlitem.id
2.          

CTE docs

3. 12.937 316.038 ↓ 2.0 20,074 1

Nested Loop (cost=131,850.48..133,357.57 rows=10,006 width=810) (actual time=258.908..316.038 rows=20,074 loops=1)

4. 67.349 262.953 ↓ 100.4 20,074 1

HashAggregate (cost=131,850.07..131,852.07 rows=200 width=8) (actual time=258.881..262.953 rows=20,074 loops=1)

  • Group Key: vcl.document_id
5. 164.949 195.604 ↓ 1.4 459,070 1

Bitmap Heap Scan on vcl (cost=18,682.48..131,014.93 rows=334,053 width=8) (actual time=34.685..195.604 rows=459,070 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,610
  • Heap Blocks: exact=35,253
6. 0.001 30.655 ↓ 0.0 0 1

BitmapOr (cost=18,682.48..18,682.48 rows=586,380 width=0) (actual time=30.655..30.655 rows=0 loops=1)

7. 1.316 1.316 ↓ 1.0 15,385 1

Bitmap Index Scan on vcl_profile_idx (cost=0.00..482.49 rows=14,674 width=0) (actual time=1.316..1.316 rows=15,385 loops=1)

  • Index Cond: (profile_id IS NULL)
8. 29.338 29.338 ↓ 1.0 573,295 1

Bitmap Index Scan on vcl_profile_idx (cost=0.00..18,032.97 rows=571,706 width=0) (actual time=29.338..29.338 rows=573,295 loops=1)

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

Index Scan using "documentPK" on document doc (cost=0.42..7.52 rows=1 width=810) (actual time=0.002..0.002 rows=1 loops=20,074)

  • Index Cond: (id = vcl.document_id)
  • Filter: (project_id = 104)
10. 0.311 423.257 ↓ 24.1 1,204 1

Sort (cost=1,138.03..1,138.16 rows=50 width=643) (actual time=423.192..423.257 rows=1,204 loops=1)

  • Sort Key: dlitem.id
  • Sort Method: quicksort Memory: 287kB
11. 58.662 422.946 ↓ 24.1 1,204 1

Nested Loop (cost=53.04..1,136.62 rows=50 width=643) (actual time=261.358..422.946 rows=1,204 loops=1)

  • Join Filter: ((((docs.content -> 'model'::text) -> (dl.name)::text) ->> 'acronym'::text) = ((dlitem.content -> 'properties'::text) ->> 'acronym'::text))
  • Rows Removed by Join Filter: 139,314
12. 0.032 3.588 ↓ 7.0 7 1

Nested Loop (cost=53.04..686.35 rows=1 width=643) (actual time=0.797..3.588 rows=7 loops=1)

13. 1.401 3.482 ↓ 18.5 37 1

Hash Join (cost=52.75..685.55 rows=2 width=643) (actual time=0.605..3.482 rows=37 loops=1)

  • Hash Cond: (dlitem.data_list_id = dl.id)
14. 1.527 1.527 ↑ 1.0 20,511 1

Seq Scan on data_list_item dlitem (cost=0.00..555.57 rows=20,557 width=631) (actual time=0.006..1.527 rows=20,511 loops=1)

15. 0.002 0.554 ↑ 1.0 1 1

Hash (cost=52.74..52.74 rows=1 width=28) (actual time=0.554..0.554 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
16. 0.013 0.552 ↑ 1.0 1 1

Hash Join (cost=23.14..52.74 rows=1 width=28) (actual time=0.284..0.552 rows=1 loops=1)

  • Hash Cond: (dlgc.data_list_id = dl.id)
17. 0.277 0.277 ↑ 1.2 31 1

Seq Scan on data_list_config_item_group dlgc (cost=0.00..29.45 rows=38 width=8) (actual time=0.015..0.277 rows=31 loops=1)

  • Filter: (config_item_group_id = 104)
  • Rows Removed by Filter: 1,213
18. 0.002 0.262 ↑ 3.0 1 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
19. 0.260 0.260 ↑ 3.0 1 1

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

  • Filter: ((name)::text = 'trecho'::text)
  • Rows Removed by Filter: 1,243
20. 0.074 0.074 ↓ 0.0 0 37

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

  • Index Cond: (id = dlitem.parent_id)
  • Filter: ((content -> 'properties'::text) = '{"name": "Seccionamento", "acronym": "SC"}'::jsonb)
  • Rows Removed by Filter: 1
21. 360.696 360.696 ↓ 2.0 20,074 7

CTE Scan on docs (cost=0.00..200.12 rows=10,006 width=32) (actual time=36.990..51.528 rows=20,074 loops=7)

Planning time : 1.286 ms
Execution time : 425.249 ms