explain.depesz.com

PostgreSQL's explain analyze made readable

Result: oAMr

Settings
# exclusive inclusive rows x rows loops node
1. 0.008 132,112.281 ↑ 1.0 1 1

Aggregate (cost=8,567,593.06..8,567,593.07 rows=1 width=8) (actual time=132,112.281..132,112.281 rows=1 loops=1)

2. 348.074 132,112.273 ↑ 235,070.7 3 1

Hash Join (cost=8,199,708.63..8,565,830.03 rows=705,212 width=0) (actual time=127,078.032..132,112.273 rows=3 loops=1)

  • Hash Cond: ((COALESCE(asi.parent_attributesetinstance_id, asi.m_attributesetinstance_id))::text = (aiob.m_attributesetinstance_id)::text)
3. 5,160.629 26,892.243 ↑ 1.0 1,099,212 1

Hash Left Join (cost=1,572,614.68..1,909,375.33 rows=1,099,299 width=66) (actual time=21,226.833..26,892.243 rows=1,099,212 loops=1)

  • Hash Cond: ((sd.m_attributesetinstance_id)::text = (asi.m_attributesetinstance_id)::text)
4. 507.511 507.511 ↑ 1.0 1,099,212 1

Seq Scan on m_storage_detail sd (cost=0.00..56,032.99 rows=1,099,299 width=33) (actual time=0.013..507.511 rows=1,099,212 loops=1)

5. 7,477.772 21,224.103 ↑ 1.0 22,235,038 1

Hash (cost=1,033,909.19..1,033,909.19 rows=22,243,319 width=66) (actual time=21,224.103..21,224.103 rows=22,235,038 loops=1)

  • Buckets: 65,536 Batches: 1,024 Memory Usage: 2,026kB
6. 13,746.331 13,746.331 ↑ 1.0 22,235,038 1

Seq Scan on m_attributesetinstance asi (cost=0.00..1,033,909.19 rows=22,243,319 width=66) (actual time=0.005..13,746.331 rows=22,235,038 loops=1)

7. 0.000 104,871.956 ↑ 30,228.0 3 1

Hash (cost=6,625,251.40..6,625,251.40 rows=90,684 width=33) (actual time=104,871.956..104,871.956 rows=3 loops=1)

  • Buckets: 65,536 Batches: 2 Memory Usage: 513kB
8. 12.866 104,875.155 ↑ 30,228.0 3 1

Gather (cost=660,260.87..6,625,251.40 rows=90,684 width=33) (actual time=104,870.365..104,875.155 rows=3 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
9. 103,992.808 104,862.289 ↑ 37,785.0 1 3 / 3

Parallel Bitmap Heap Scan on m_attributeinstance aiob (cost=659,260.87..6,615,183.00 rows=37,785 width=33) (actual time=104,862.284..104,862.289 rows=1 loops=3)

  • Recheck Cond: ((m_attribute_id)::text = '06F13AFBAAFB4B908B849905155BF1D2'::text)
  • Rows Removed by Index Recheck: 50,146,643
  • Filter: (btrim((value)::text) = '55555'::text)
  • Rows Removed by Filter: 6,092,454
  • Heap Blocks: exact=12,990 lossy=1,859,395
10. 869.481 869.481 ↓ 1.0 18,289,237 1 / 3

Bitmap Index Scan on em_dgpr_attribute_ix (cost=0.00..659,238.19 rows=18,136,750 width=0) (actual time=2,608.443..2,608.443 rows=18,289,237 loops=1)

  • Index Cond: ((m_attribute_id)::text = '06F13AFBAAFB4B908B849905155BF1D2'::text)
Planning time : 4.416 ms
Execution time : 132,115.692 ms