explain.depesz.com

PostgreSQL's explain analyze made readable

Result: YRYt

Settings
# exclusive inclusive rows x rows loops node
1. 12.674 308.212 ↓ 439.2 5,270 1

Sort (cost=5,862.54..5,862.57 rows=12 width=1,466) (actual time=307.608..308.212 rows=5,270 loops=1)

  • Sort Key: m.parent_module_id NULLS FIRST, m.order_num, m.id
  • Sort Method: quicksort Memory: 2297kB
2. 17.163 295.538 ↓ 439.2 5,270 1

HashAggregate (cost=5,862.09..5,862.21 rows=12 width=1,466) (actual time=291.190..295.538 rows=5,270 loops=1)

  • Group Key: m.id, (NULL::integer), m.default_cid, m.parent_module_id, m.name, m.title, m.url, m.description, m.has_write_operation, m.is_public, m.allow_only_admin, m.allow_only_psi_admin, m.is_hidden, ((m.options)::text), m.order_num, m.updated_by, m.updated_on, m.created_by, m.created_on, (NULL::integer), m.order_num, m.has_write_operation
3. 1.051 278.375 ↓ 444.6 5,335 1

Append (cost=400.32..5,861.43 rows=12 width=1,466) (actual time=18.911..278.375 rows=5,335 loops=1)

4. 2.927 36.273 ↓ 2,089.0 2,089 1

Nested Loop Left Join (cost=400.32..541.62 rows=1 width=222) (actual time=18.910..36.273 rows=2,089 loops=1)

  • Filter: (ri.id IS NULL)
  • Rows Removed by Filter: 346
5. 1.695 28.614 ↓ 2,366.0 2,366 1

Nested Loop Left Join (cost=399.90..539.21 rows=1 width=363) (actual time=18.866..28.614 rows=2,366 loops=1)

  • Filter: (rg.id IS NULL)
  • Rows Removed by Filter: 51
6. 2.609 22.085 ↓ 75.5 2,417 1

Hash Right Join (cost=399.62..473.78 rows=32 width=363) (actual time=18.813..22.085 rows=2,417 loops=1)

  • Hash Cond: (pm.module_id = m.id)
  • Filter: (pm.ps_product_id IS NULL)
  • Rows Removed by Filter: 816
7. 0.748 0.748 ↑ 1.0 3,894 1

Seq Scan on product_modules pm (cost=0.00..63.94 rows=3,894 width=8) (actual time=0.026..0.748 rows=3,894 loops=1)

8. 2.841 18.728 ↓ 62.8 2,761 1

Hash (cost=399.07..399.07 rows=44 width=363) (actual time=18.728..18.728 rows=2,761 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1113kB
9. 15.887 15.887 ↓ 62.8 2,761 1

Index Scan using pk_modules on modules m (cost=0.42..399.07 rows=44 width=363) (actual time=0.047..15.887 rows=2,761 loops=1)

  • Index Cond: (id < 1000000)
  • Filter: ((is_published = 1) AND (((options ->> 'country_codes'::text) IS NULL) OR (jsonb_array_length(((options ->> 'country_codes'::text))::jsonb) = 0) OR (((options ->> 'country_codes'::text))::jsonb ? 'US'::text)))
  • Rows Removed by Filter: 561
10. 4.834 4.834 ↓ 0.0 0 2,417

Index Scan using uk_report_groups_cid_module_id on report_groups rg (cost=0.29..2.03 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=2,417)

  • Index Cond: ((cid = 235) AND (module_id = m.id))
11. 4.732 4.732 ↓ 0.0 0 2,366

Index Scan using idx_report_instances_cid_module_id on report_instances ri (cost=0.42..2.39 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=2,366)

  • Index Cond: ((cid = 235) AND (module_id = m.id))
12. 1.427 189.928 ↓ 970.7 2,912 1

Subquery Scan on "*SELECT* 2" (cost=3,187.37..3,306.78 rows=3 width=236) (actual time=38.632..189.928 rows=2,912 loops=1)

13. 126.076 188.501 ↓ 970.7 2,912 1

Nested Loop (cost=3,187.37..3,306.75 rows=3 width=236) (actual time=38.630..188.501 rows=2,912 loops=1)

14. 5.508 43.569 ↓ 87.3 4,714 1

Hash Right Join (cost=3,187.09..3,265.64 rows=54 width=585) (actual time=37.805..43.569 rows=4,714 loops=1)

  • Hash Cond: (pm_1.module_id = m_1.id)
  • Filter: (pm_1.ps_product_id IS NULL)
  • Rows Removed by Filter: 607
15. 0.451 0.451 ↑ 1.0 3,894 1

Seq Scan on product_modules pm_1 (cost=0.00..63.94 rows=3,894 width=8) (actual time=0.012..0.451 rows=3,894 loops=1)

16. 4.331 37.610 ↓ 70.6 5,227 1

Hash (cost=3,186.16..3,186.16 rows=74 width=585) (actual time=37.610..37.610 rows=5,227 loops=1)

  • Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1365kB
17. 4.845 33.279 ↓ 70.6 5,227 1

Merge Join (cost=40.48..3,186.16 rows=74 width=585) (actual time=2.063..33.279 rows=5,227 loops=1)

  • Merge Cond: (ri_1.module_id = m_1.id)
18. 13.092 13.092 ↑ 1.3 5,388 1

Index Scan using idx_report_instances_cid_module_id on report_instances ri_1 (cost=0.42..2,635.80 rows=6,784 width=248) (actual time=0.018..13.092 rows=5,388 loops=1)

  • Index Cond: (cid = 235)
19. 15.342 15.342 ↑ 1.4 7,965 1

Index Scan using pk_modules on modules m_1 (cost=0.42..62,267.39 rows=11,002 width=341) (actual time=0.040..15.342 rows=7,965 loops=1)

  • Filter: ((is_published = 1) AND (((options ->> 'country_codes'::text) IS NULL) OR (jsonb_array_length(((options ->> 'country_codes'::text))::jsonb) = 0) OR (((options ->> 'country_codes'::text))::jsonb ? 'US'::text)))
  • Rows Removed by Filter: 561
20. 18.856 18.856 ↑ 1.0 1 4,714

Index Scan using pk_report_groups on report_groups rg_1 (cost=0.29..0.75 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=4,714)

  • Index Cond: ((cid = 235) AND (id = ri_1.report_group_id))
  • Filter: (report_group_type_id <> 3)
  • Rows Removed by Filter: 0
21. 0.105 30.152 ↓ 23.0 161 1

Subquery Scan on "*SELECT* 3" (cost=1,360.86..1,441.27 rows=7 width=236) (actual time=16.468..30.152 rows=161 loops=1)

22. 13.536 30.047 ↓ 23.0 161 1

Hash Right Join (cost=1,360.86..1,441.20 rows=7 width=236) (actual time=16.466..30.047 rows=161 loops=1)

  • Hash Cond: (pm_2.module_id = m_2.id)
  • Filter: (pm_2.ps_product_id IS NULL)
  • Rows Removed by Filter: 7
23. 0.449 0.449 ↑ 1.0 3,894 1

Seq Scan on product_modules pm_2 (cost=0.00..63.94 rows=3,894 width=8) (actual time=0.010..0.449 rows=3,894 loops=1)

24. 0.250 16.062 ↓ 17.6 158 1

Hash (cost=1,360.75..1,360.75 rows=9 width=1,423) (actual time=16.062..16.062 rows=158 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 121kB
25. 0.962 15.812 ↓ 17.6 158 1

Merge Join (cost=23.36..1,360.75 rows=9 width=1,423) (actual time=1.181..15.812 rows=158 loops=1)

  • Merge Cond: (rg_2.module_id = m_2.id)
26. 1.230 1.230 ↑ 5.1 168 1

Index Scan using uk_report_groups_cid_module_id on report_groups rg_2 (cost=0.29..825.86 rows=854 width=1,086) (actual time=0.021..1.230 rows=168 loops=1)

  • Index Cond: (cid = 235)
  • Filter: (report_group_type_id <> ALL ('{2,3}'::integer[]))
  • Rows Removed by Filter: 747
27. 13.620 13.620 ↑ 1.4 7,602 1

Index Scan using pk_modules on modules m_2 (cost=0.42..62,267.39 rows=11,002 width=341) (actual time=0.031..13.620 rows=7,602 loops=1)

  • Filter: ((is_published = 1) AND (((options ->> 'country_codes'::text) IS NULL) OR (jsonb_array_length(((options ->> 'country_codes'::text))::jsonb) = 0) OR (((options ->> 'country_codes'::text))::jsonb ? 'US'::text)))
  • Rows Removed by Filter: 561
28. 0.116 20.971 ↓ 173.0 173 1

Subquery Scan on "*SELECT* 4" (cost=492.92..571.74 rows=1 width=236) (actual time=2.977..20.971 rows=173 loops=1)

29. 18.570 20.855 ↓ 173.0 173 1

Hash Right Join (cost=492.92..571.73 rows=1 width=236) (actual time=2.975..20.855 rows=173 loops=1)

  • Hash Cond: (pm_3.module_id = m_3.id)
  • Filter: (pm_3.ps_product_id IS NULL)
30. 0.406 0.406 ↑ 1.0 3,894 1

Seq Scan on product_modules pm_3 (cost=0.00..63.94 rows=3,894 width=8) (actual time=0.009..0.406 rows=3,894 loops=1)

31. 0.193 1.879 ↓ 86.5 173 1

Hash (cost=492.90..492.90 rows=2 width=706) (actual time=1.879..1.879 rows=173 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 96kB
32. 0.214 1.686 ↓ 86.5 173 1

Nested Loop (cost=0.42..492.90 rows=2 width=706) (actual time=0.069..1.686 rows=173 loops=1)

33. 0.953 0.953 ↑ 1.0 173 1

Seq Scan on report_new_instances ri_2 (cost=0.00..58.24 rows=178 width=365) (actual time=0.040..0.953 rows=173 loops=1)

  • Filter: ((deleted_by IS NULL) AND (cid = 235))
  • Rows Removed by Filter: 238
34. 0.519 0.519 ↑ 1.0 1 173

Index Scan using pk_modules on modules m_3 (cost=0.42..2.44 rows=1 width=341) (actual time=0.003..0.003 rows=1 loops=173)

  • Index Cond: (id = ri_2.module_id)
  • Filter: ((is_published = 1) AND (((options ->> 'country_codes'::text) IS NULL) OR (jsonb_array_length(((options ->> 'country_codes'::text))::jsonb) = 0) OR (((options ->> 'country_codes'::text))::jsonb ? 'US'::text)))
Planning time : 19.427 ms