explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DBg3

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 220.521 ↓ 0.0 0 1

Limit (cost=7.21..289,523.79 rows=10,000 width=8) (actual time=220.520..220.521 rows=0 loops=1)

2. 0.002 220.519 ↓ 0.0 0 1

Merge Semi Join (cost=7.21..503,592.35 rows=17,394 width=8) (actual time=220.518..220.519 rows=0 loops=1)

  • Merge Cond: (o.id = op."orderRef")
3. 81.033 220.517 ↓ 0.0 0 1

Index Scan Backward using "OrderDO_pkey" on "OrderDO" o (cost=0.29..494,778.70 rows=17,434 width=8) (actual time=220.516..220.517 rows=0 loops=1)

  • Filter: (("shopRef" = ANY ('{0,500,1,200,210,220,230,120,100,300,110,111}'::bigint[])) AND ((((id)::character varying)::text ~~ '%123jkgl%'::text) OR (lower(("shopOrderNo")::text) ~~ '%123jkgl%'::text) OR (("shopCustomerOrderNo" IS NOT NULL) (...)
  • Rows Removed by Filter: 34,871
4.          

SubPlan (for Index Scan Backward)

5. 34.871 139.484 ↓ 0.0 0 34,871

Nested Loop Semi Join (cost=0.29..27.84 rows=1 width=8) (actual time=0.004..0.004 rows=0 loops=34,871)

  • Join Filter: (("OrderPropertyDO".key)::text = "AdditionalSearchableAttributesDO"."propertyKey")
6. 104.613 104.613 ↓ 0.0 0 34,871

Index Scan using "index_OrderPropertyDO_orderRef" on "OrderPropertyDO" (cost=0.29..8.37 rows=1 width=19) (actual time=0.003..0.003 rows=0 loops=34,871)

  • Index Cond: ("orderRef" = o.id)
  • Filter: (((key)::text = ANY ('{customOrderNo,myOrderNo,SAPOrder}'::text[])) AND (lower((value)::text) ~~ '%123jkgl%'::text))
  • Rows Removed by Filter: 3
7. 0.000 0.000 ↓ 0.0 0

Materialize (cost=0.00..19.45 rows=1 width=32) (never executed)

8. 0.000 0.000 ↓ 0.0 0

Seq Scan on "AdditionalSearchableAttributesDO" (cost=0.00..19.45 rows=1 width=32) (never executed)

  • Filter: (("searchType" = 'orderNumberSearch'::text) AND ("referenceName" = 'formFieldType.allKinds'::text))
9. 0.000 0.000 ↓ 0.0 0

Index Scan Backward using "index_OrderPosDO_orderRef" on "OrderPosDO" op (cost=0.42..8,377.71 rows=52,317 width=8) (never executed)

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