explain.depesz.com

PostgreSQL's explain analyze made readable

Result: SQxL

Settings
# exclusive inclusive rows x rows loops node
1. 1.001 629.107 ↑ 11.8 846 1

Limit (cost=7.21..268,369.80 rows=10,000 width=8) (actual time=7.785..629.107 rows=846 loops=1)

2. 50.577 628.106 ↑ 22.2 846 1

Merge Semi Join (cost=7.21..503,616.44 rows=18,766 width=8) (actual time=7.780..628.106 rows=846 loops=1)

  • Merge Cond: (o.id = op."orderRef")
3. 143.042 449.267 ↑ 22.2 846 1

Index Scan Backward using "OrderDO_pkey" on "OrderDO" o (cost=0.29..494,778.70 rows=18,810 width=8) (actual time=5.436..449.267 rows=846 loops=1)

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

SubPlan (for Index Scan Backward)

5. 68.050 306.225 ↓ 0.0 0 34,025

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

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

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

  • Index Cond: ("orderRef" = o.id)
  • Filter: (((key)::text = ANY ('{customOrderNo,myOrderNo,SAPOrder}'::text[])) AND (lower((value)::text) ~~ '%12345%'::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. 128.262 128.262 ↓ 1.3 69,507 1

Index Scan Backward using "index_OrderPosDO_orderRef" on "OrderPosDO" op (cost=0.42..8,377.71 rows=52,317 width=8) (actual time=0.010..128.262 rows=69,507 loops=1)

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