explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6tpr : Optimization for: plan #b7SJ

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.002 2,108.847 ↑ 499.0 1 1

Limit (cost=211,117.99..211,119.24 rows=499 width=98) (actual time=2,108.846..2,108.847 rows=1 loops=1)

  • Buffers: shared hit=389,382
2. 0.011 2,108.845 ↑ 499.0 1 1

Sort (cost=211,117.99..211,119.24 rows=499 width=98) (actual time=2,108.844..2,108.845 rows=1 loops=1)

  • Sort Key: name
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=389,382
3. 1,837.842 2,108.834 ↑ 499.0 1 1

Bitmap Heap Scan on customer (cost=48,561.78..211,095.63 rows=499 width=98) (actual time=2,090.065..2,108.834 rows=1 loops=1)

  • Recheck Cond: ((resellerid = 247,133) AND ((parentkey = '999999999_247131_247133'::text) OR (parentkey ~~ '999999999\_247131\_247133\%'::text)))
  • Filter: ((customerstatus <> 'Deleted'::text) AND ((parentkey = '999999999_247131_247133'::text) OR (parentkey ~~ '999999999\_247131\_247133\%'::text)) A
  • Rows Removed by Filter: 1,483,671
  • Heap Blocks: exact=373,338
  • Buffers: shared hit=389,382
4. 12.658 270.992 ↓ 0.0 0 1

BitmapAnd (cost=48,561.78..48,561.78 rows=100,375 width=0) (actual time=270.992..270.992 rows=0 loops=1)

  • Buffers: shared hit=16,044
5. 128.818 128.818 ↓ 1.0 1,493,949 1

Bitmap Index Scan on customer_resellerid_key1 (cost=0.00..20,190.12 rows=1,466,891 width=0) (actual time=128.818..128.818 rows=1,493,949 loops=1)

  • Index Cond: (resellerid = 247,133)
  • Buffers: shared hit=4,818
6. 129.504 129.516 ↓ 0.0 0 1

BitmapOr (cost=28,371.28..28,371.28 rows=1,467,188 width=0) (actual time=129.515..129.516 rows=0 loops=1)

  • Buffers: shared hit=11,226
  • -> Bitmap Index Scan on customer_parentkey_idx (cost=0.00..28364.24 rows=1,466,891 width=0) (actual time=129.501..129.501 rows=1,498,794 loops
  • Index Cond: (parentkey = '999999999_247131_247133'::text)
  • Buffers: shared hit=11,222
7. 0.012 0.012 ↓ 0.0 0 1

Bitmap Index Scan on customer_parentkey_idx (cost=0.00..6.79 rows=297 width=0) (actual time=0.011..0.012 rows=0 loops=1)

  • Index Cond: (parentkey = '999999999_247131_247133%'::text)
  • Buffers: shared hit=4
Planning time : 0.197 ms
Execution time : 2,110.018 ms