explain.depesz.com

PostgreSQL's explain analyze made readable

Result: YB7A

Settings
# exclusive inclusive rows x rows loops node
1. 0.046 4,727.920 ↑ 1.0 800 1

Limit (cost=223,776.54..223,778.54 rows=800 width=297) (actual time=4,727.844..4,727.920 rows=800 loops=1)

2. 122.426 4,727.874 ↑ 287.7 800 1

Sort (cost=223,776.54..224,351.93 rows=230,156 width=297) (actual time=4,727.843..4,727.874 rows=800 loops=1)

  • Sort Key: i.tags DESC
  • Sort Method: top-N heapsort Memory: 462kB
3. 3,753.774 4,605.448 ↑ 1.1 218,815 1

Merge Right Join (cost=67,257.34..211,527.80 rows=230,156 width=297) (actual time=199.201..4,605.448 rows=218,815 loops=1)

  • Merge Cond: (attr.bo_instance_id = i.id)
  • Filter: ((SubPlan 1) AND (SubPlan 2))
  • Rows Removed by Filter: 13782
4. 130.343 130.343 ↑ 4.2 232,597 1

Index Scan using idx_attrib_touchstone_bo_instance_id on attrib_touchstone attr (cost=0.42..121,671.79 rows=974,185 width=730) (actual time=0.006..130.343 rows=232,597 loops=1)

5. 23.587 260.937 ↓ 1.0 232,597 1

Materialize (cost=67,256.92..68,410.33 rows=230,682 width=70) (actual time=199.164..260.937 rows=232,597 loops=1)

6. 136.008 237.350 ↓ 1.0 232,597 1

Sort (cost=67,256.92..67,833.62 rows=230,682 width=70) (actual time=199.162..237.350 rows=232,597 loops=1)

  • Sort Key: i.id
  • Sort Method: external merge Disk: 18736kB
7. 101.342 101.342 ↓ 1.0 232,597 1

Seq Scan on bo_instance i (cost=0.00..37,244.29 rows=230,682 width=70) (actual time=0.167..101.342 rows=232,597 loops=1)

  • Filter: ((NOT is_deleted) AND (bo_class_id = 7))
  • Rows Removed by Filter: 13003
8.          

SubPlan (forMerge Right Join)

9. 232.597 232.597 ↑ 100.0 1 232,597

Function Scan on unnest elem (cost=0.00..1.25 rows=100 width=0) (actual time=0.001..0.001 rows=1 loops=232,597)

  • Filter: ((elem)::text ~~* 'S%'::text)
  • Rows Removed by Filter: 0
10. 227.797 227.797 ↑ 100.0 1 227,797

Function Scan on unnest elem_1 (cost=0.00..1.25 rows=100 width=0) (actual time=0.001..0.001 rows=1 loops=227,797)

  • Filter: ((elem)::text ~~* 'R%'::text)
  • Rows Removed by Filter: 0
Planning time : 0.421 ms
Execution time : 4,731.448 ms