explain.depesz.com

PostgreSQL's explain analyze made readable

Result: fmDa1

Settings
# exclusive inclusive rows x rows loops node
1. 0.084 245.354 ↑ 7.3 6 1

Group (cost=127,748.51..127,748.84 rows=44 width=643) (actual time=245.198..245.354 rows=6 loops=1)

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

CTE docs

3. 1.700 135.271 ↓ 2.3 20,074 1

Nested Loop Semi Join (cost=0.43..126,667.52 rows=8,743 width=810) (actual time=0.016..135.271 rows=20,074 loops=1)

4. 33.201 33.201 ↓ 1.0 20,074 1

Seq Scan on document doc (cost=0.00..14,647.69 rows=20,012 width=810) (actual time=0.005..33.201 rows=20,074 loops=1)

  • Filter: (project_id = 104)
  • Rows Removed by Filter: 102,701
5. 100.370 100.370 ↑ 6.0 1 20,074

Index Scan using vcl_document_id_idx on vcl (cost=0.43..24.56 rows=6 width=8) (actual time=0.005..0.005 rows=1 loops=20,074)

  • Index Cond: (document_id = doc.id)
  • Filter: (((office_id IS NULL) OR (office_id = 119)) AND ((profile_id IS NULL) OR (profile_id = ANY ('{144,155,1492,1948,146,2012,149,147,153,150,1493,2286}'::bigint[]))))
  • Rows Removed by Filter: 3
6. 0.293 245.270 ↓ 27.4 1,204 1

Sort (cost=1,080.99..1,081.10 rows=44 width=643) (actual time=245.197..245.270 rows=1,204 loops=1)

  • Sort Key: dlitem.id
  • Sort Method: quicksort Memory: 287kB
7. 58.229 244.977 ↓ 27.4 1,204 1

Nested Loop (cost=53.04..1,079.79 rows=44 width=643) (actual time=0.782..244.977 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
8. 0.022 3.187 ↓ 7.0 7 1

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

9. 1.336 3.091 ↓ 18.5 37 1

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

  • Hash Cond: (dlitem.data_list_id = dl.id)
10. 1.553 1.553 ↑ 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.003..1.553 rows=20,511 loops=1)

11. 0.001 0.202 ↑ 1.0 1 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
12. 0.005 0.201 ↑ 1.0 1 1

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

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

  • Filter: (config_item_group_id = 104)
  • Rows Removed by Filter: 1,213
14. 0.001 0.094 ↑ 3.0 1 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
15. 0.093 0.093 ↑ 3.0 1 1

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

  • Filter: ((name)::text = 'trecho'::text)
  • Rows Removed by Filter: 1,243
16. 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
17. 183.561 183.561 ↓ 2.3 20,074 7

CTE Scan on docs (cost=0.00..174.86 rows=8,743 width=32) (actual time=0.005..26.223 rows=20,074 loops=7)

Planning time : 0.577 ms
Execution time : 247.413 ms