explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mghx

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 542.360 ↓ 6.0 6 1

Group (cost=100,587.06..124,965.67 rows=1 width=643) (actual time=450.354..542.360 rows=6 loops=1)

  • Group Key: dl.name, dlitem.id
2. 8.886 542.357 ↓ 6.0 6 1

Nested Loop Semi Join (cost=100,587.06..124,965.67 rows=1 width=643) (actual time=450.353..542.357 rows=6 loops=1)

  • Join Filter: ((((doc.content -> 'model'::text) -> (dl.name)::text) ->> 'acronym'::text) = ((dlitem.content -> 'properties'::text) ->> 'acronym'::text))
  • Rows Removed by Join Filter: 20,925
3. 0.035 7.876 ↓ 7.0 7 1

Nested Loop (cost=23.71..1,328.32 rows=1 width=651) (actual time=0.524..7.876 rows=7 loops=1)

4. 4.637 7.767 ↓ 18.5 37 1

Nested Loop (cost=23.43..1,327.52 rows=2 width=651) (actual time=0.485..7.767 rows=37 loops=1)

  • Join Filter: (dl.id = dlitem.data_list_id)
  • Rows Removed by Join Filter: 20,474
5. 3.130 3.130 ↑ 1.0 20,511 1

Index Scan using "data_list_itemPK" on data_list_item dlitem (cost=0.29..966.43 rows=20,557 width=631) (actual time=0.006..3.130 rows=20,511 loops=1)

6. 0.000 0.000 ↑ 1.0 1 20,511

Materialize (cost=23.14..52.75 rows=1 width=36) (actual time=0.000..0.000 rows=1 loops=20,511)

7. 0.007 0.304 ↑ 1.0 1 1

Hash Join (cost=23.14..52.74 rows=1 width=36) (actual time=0.167..0.304 rows=1 loops=1)

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

Seq Scan on data_list_config_item_group dlgc (cost=0.00..29.45 rows=38 width=16) (actual time=0.010..0.145 rows=31 loops=1)

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

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

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

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

  • Filter: ((name)::text = 'trecho'::text)
  • Rows Removed by Filter: 1,243
11. 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
12. 10.435 525.595 ↑ 4.9 2,990 7

Materialize (cost=100,563.34..121,987.03 rows=14,666 width=666) (actual time=64.233..75.085 rows=2,990 loops=7)

13. 37.172 515.160 ↓ 1.4 20,074 1

Hash Semi Join (cost=100,563.34..120,666.70 rows=14,666 width=666) (actual time=449.630..515.160 rows=20,074 loops=1)

  • Hash Cond: (doc.id = v.document_id)
14. 28.689 28.689 ↓ 1.0 20,074 1

Seq Scan on document doc (cost=0.00..14,647.69 rows=20,012 width=674) (actual time=0.009..28.689 rows=20,074 loops=1)

  • Filter: (project_id = 104)
  • Rows Removed by Filter: 102,701
15. 44.730 449.299 ↓ 1.4 459,070 1

Hash (cost=95,082.69..95,082.69 rows=334,052 width=8) (actual time=449.299..449.299 rows=459,070 loops=1)

  • Buckets: 131,072 Batches: 8 Memory Usage: 3,231kB
16. 125.989 404.569 ↓ 1.4 459,070 1

Hash Join (cost=25,056.08..95,082.69 rows=334,052 width=8) (actual time=117.137..404.569 rows=459,070 loops=1)

  • Hash Cond: (vcl.version_id = v.id)
17. 165.553 191.683 ↓ 1.4 459,070 1

Bitmap Heap Scan on vcl (cost=10,694.48..72,356.88 rows=334,052 width=8) (actual time=29.837..191.683 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=32,190
18. 0.002 26.130 ↓ 0.0 0 1

BitmapOr (cost=10,694.48..10,694.48 rows=586,378 width=0) (actual time=26.130..26.130 rows=0 loops=1)

19. 0.732 0.732 ↓ 1.0 15,385 1

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

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

Bitmap Index Scan on vcl_profile_idx (cost=0.00..10,252.97 rows=571,705 width=0) (actual time=25.396..25.396 rows=573,295 loops=1)

  • Index Cond: (profile_id = ANY ('{144,155,1492,1948,146,2012,149,147,153,150,1493,2286}'::bigint[]))
21. 37.276 86.897 ↑ 1.0 237,760 1

Hash (cost=10,228.60..10,228.60 rows=237,760 width=16) (actual time=86.897..86.897 rows=237,760 loops=1)

  • Buckets: 131,072 Batches: 4 Memory Usage: 3,817kB
22. 49.621 49.621 ↑ 1.0 237,760 1

Seq Scan on version v (cost=0.00..10,228.60 rows=237,760 width=16) (actual time=0.006..49.621 rows=237,760 loops=1)

Planning time : 0.865 ms
Execution time : 543.550 ms