explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3jB

Settings
# exclusive inclusive rows x rows loops node
1. 0.010 19.998 ↑ 1.0 1 1

Aggregate (cost=16,014.65..16,014.66 rows=1 width=8) (actual time=19.996..19.998 rows=1 loops=1)

2. 0.010 19.988 ↑ 124.1 7 1

Limit (cost=16,001.62..16,003.79 rows=869 width=8) (actual time=19.975..19.988 rows=7 loops=1)

3. 0.028 19.978 ↑ 124.1 7 1

Sort (cost=16,001.62..16,003.79 rows=869 width=8) (actual time=19.973..19.978 rows=7 loops=1)

  • Sort Key: o.id DESC
  • Sort Method: quicksort Memory: 25kB
4. 0.124 19.950 ↑ 124.1 7 1

Hash Semi Join (cost=532.29..15,959.20 rows=869 width=8) (actual time=9.312..19.950 rows=7 loops=1)

  • Hash Cond: (o.id = op."orderRef")
5. 3.501 13.253 ↑ 124.1 7 1

Bitmap Heap Scan on "OrderDO" o (cost=112.54..15,523.15 rows=869 width=8) (actual time=2.635..13.253 rows=7 loops=1)

  • Recheck Cond: ("shopRef" = ANY ('{0,500,1,200,210,220,230,120,100,300,110,111}'::bigint[]))
  • Filter: ((((id)::character varying)::text ~~ 'A45%'::text) OR (("shopOrderNo")::text ~~ 'A45%'::text) OR (("shopCustomerOrderNo")::text ~~ 'A45%'::text) OR (SubPlan 1))
  • Rows Removed by Filter: 2327
  • Heap Blocks: exact=143
6. 0.416 0.416 ↓ 1.4 2,413 1

Bitmap Index Scan on "index_OrderDO_shopRef" (cost=0.00..112.32 rows=1,730 width=0) (actual time=0.416..0.416 rows=2,413 loops=1)

  • Index Cond: ("shopRef" = ANY ('{0,500,1,200,210,220,230,120,100,300,110,111}'::bigint[]))
7.          

SubPlan (for Bitmap Heap Scan)

8. 9.336 9.336 ↓ 0.0 0 2,334

Index Scan using "index_OrderPropertyDO_orderRef" on "OrderPropertyDO" (cost=0.29..17.28 rows=1 width=8) (actual time=0.004..0.004 rows=0 loops=2,334)

  • Index Cond: ("orderRef" = o.id)
  • Filter: (((key)::text = ANY ('{myOrderNo,SAPOrder}'::text[])) AND ((value)::text ~~ 'SAP45%'::text))
  • Rows Removed by Filter: 5
9. 2.613 6.573 ↓ 1.3 3,793 1

Hash (cost=382.26..382.26 rows=2,999 width=8) (actual time=6.572..6.573 rows=3,793 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 181kB
10. 3.960 3.960 ↓ 1.3 3,793 1

Seq Scan on "OrderPosDO" op (cost=0.00..382.26 rows=2,999 width=8) (actual time=0.010..3.960 rows=3,793 loops=1)

  • Filter: ("supplierRef" = ANY ('{1,3000,1211,1212,1000,2000,1200,1210,1100}'::bigint[]))
Planning time : 0.919 ms
Execution time : 20.101 ms