explain.depesz.com

PostgreSQL's explain analyze made readable

Result: QWQb

Settings
# exclusive inclusive rows x rows loops node
1. 0.104 310.842 ↑ 8.3 6 1

Group (cost=132,698.60..132,698.98 rows=50 width=643) (actual time=310.690..310.842 rows=6 loops=1)

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

CTE docs

3. 0.000 243.238 ↓ 1.1 11,385 1

Nested Loop (cost=130,053.49..131,560.57 rows=10,006 width=810) (actual time=210.258..243.238 rows=11,385 loops=1)

4. 30.717 212.261 ↓ 56.9 11,385 1

HashAggregate (cost=130,053.07..130,055.07 rows=200 width=8) (actual time=210.236..212.261 rows=11,385 loops=1)

  • Group Key: vcl.document_id
5. 154.149 181.544 ↓ 5.7 189,544 1

Bitmap Heap Scan on vcl (cost=18,041.26..129,970.17 rows=33,159 width=8) (actual time=31.473..181.544 rows=189,544 loops=1)

  • Recheck Cond: (profile_id = ANY ('{144,155,1492,1948,146,2012,149,147,153,150,1493,2286}'::bigint[]))
  • Filter: (office_id = 119)
  • Rows Removed by Filter: 383,751
  • Heap Blocks: exact=34,835
6. 27.395 27.395 ↓ 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=27.395..27.395 rows=573,295 loops=1)

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

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

  • Index Cond: (id = vcl.document_id)
  • Filter: (project_id = 104)
8. 0.273 310.738 ↓ 24.1 1,204 1

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

  • Sort Key: dlitem.id
  • Sort Method: quicksort Memory: 287kB
9. 36.560 310.465 ↓ 24.1 1,204 1

Nested Loop (cost=53.04..1,136.62 rows=50 width=643) (actual time=212.252..310.465 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: 78,491
10. 0.017 3.082 ↓ 7.0 7 1

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

11. 1.291 2.991 ↓ 18.5 37 1

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

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

13. 0.000 0.198 ↑ 1.0 1 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
14. 0.006 0.198 ↑ 1.0 1 1

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

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

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

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
17. 0.091 0.091 ↑ 3.0 1 1

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

  • Filter: ((name)::text = 'trecho'::text)
  • Rows Removed by Filter: 1,243
18. 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
19. 270.823 270.823 ↓ 1.1 11,385 7

CTE Scan on docs (cost=0.00..200.12 rows=10,006 width=32) (actual time=30.040..38.689 rows=11,385 loops=7)

Planning time : 0.570 ms
Execution time : 311.969 ms