explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ycn4

Settings
# exclusive inclusive rows x rows loops node
1. 0.012 11.192 ↑ 1.0 1 1

Aggregate (cost=8,778.43..8,778.44 rows=1 width=8) (actual time=11.191..11.192 rows=1 loops=1)

2. 0.010 11.180 ↑ 68.2 8 1

Limit (cost=8,770.24..8,771.61 rows=546 width=8) (actual time=11.166..11.180 rows=8 loops=1)

3. 0.032 11.170 ↑ 68.2 8 1

Sort (cost=8,770.24..8,771.61 rows=546 width=8) (actual time=11.164..11.170 rows=8 loops=1)

  • Sort Key: o.id DESC
  • Sort Method: quicksort Memory: 25kB
4. 0.378 11.138 ↑ 68.2 8 1

Hash Semi Join (cost=250.95..8,745.42 rows=546 width=8) (actual time=6.487..11.138 rows=8 loops=1)

  • Hash Cond: (o.id = op."orderRef")
5. 2.108 6.943 ↑ 68.2 8 1

Bitmap Heap Scan on "OrderDO" o (cost=71.30..8,556.21 rows=546 width=8) (actual time=2.314..6.943 rows=8 loops=1)

  • Recheck Cond: ("shopRef" = ANY ('{0,500,1,200,210,220,230,120,100,300,110,111}'::bigint[]))
  • Filter: ((((id)::character varying)::text ~~ '%456%'::text) OR (("shopOrderNo")::text ~~ '%456%'::text) OR (SubPlan 1))
  • Rows Removed by Filter: 1494
  • Heap Blocks: exact=60
6. 0.338 0.338 ↓ 2.2 2,280 1

Bitmap Index Scan on "index_OrderDO_shopRef" (cost=0.00..71.16 rows=1,040 width=0) (actual time=0.338..0.338 rows=2,280 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. 4.497 4.497 ↓ 0.0 0 1,499

Index Scan using "index_OrderPropertyDO_orderRef" on "OrderPropertyDO" (cost=0.28..15.84 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=1,499)

  • Index Cond: ("orderRef" = o.id)
  • Filter: (((key)::text = ANY ('{myOrderNo,SAPOrder}'::text[])) AND ((value)::text ~~ '%456%'::text))
  • Rows Removed by Filter: 2
9. 1.401 3.817 ↓ 1.3 2,407 1

Hash (cost=157.34..157.34 rows=1,785 width=8) (actual time=3.817..3.817 rows=2,407 loops=1)

  • Buckets: 4096 (originally 2048) Batches: 1 (originally 1) Memory Usage: 127kB
10. 2.416 2.416 ↓ 1.3 2,407 1

Seq Scan on "OrderPosDO" op (cost=0.00..157.34 rows=1,785 width=8) (actual time=0.013..2.416 rows=2,407 loops=1)

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