explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xKAp : Optimization for: plan #5sqg7

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.011 0.356 ↓ 5.0 5 1

Sort (cost=73.82..73.82 rows=1 width=43) (actual time=0.355..0.356 rows=5 loops=1)

  • Sort Key: fbf.id
  • Sort Method: quicksort Memory: 25kB
2.          

CTE wt

3. 0.003 0.069 ↑ 1.0 1 1

Result (cost=20.95..20.96 rows=1 width=4) (actual time=0.069..0.069 rows=1 loops=1)

4.          

Initplan (forResult)

5. 0.027 0.066 ↑ 1.0 1 1

Seq Scan on routerule_tbl routerule_tbl_2 (cost=13.41..20.95 rows=1 width=4) (actual time=0.044..0.066 rows=1 loops=1)

  • Filter: ((origincityid = COALESCE($0, '-1'::integer)) AND (destinationcityid = COALESCE($1, '-1'::integer)) AND (origingroupid = $2) AND (destinationgroupid = $3))
  • Rows Removed by Filter: 226
6.          

Initplan (forSeq Scan)

7. 0.001 0.004 ↓ 0.0 0 1

Unique (cost=0.14..4.16 rows=1 width=4) (actual time=0.004..0.004 rows=0 loops=1)

8. 0.003 0.003 ↓ 0.0 0 1

Index Only Scan using location_idx on routerule_tbl (cost=0.14..4.16 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=1)

  • Index Cond: (origincityid = 4000017)
  • Heap Fetches: 0
9. 0.003 0.023 ↑ 5.0 1 1

Unique (cost=0.00..5.84 rows=5 width=4) (actual time=0.004..0.023 rows=1 loops=1)

10. 0.020 0.020 ↑ 1.2 5 1

Seq Scan on routerule_tbl routerule_tbl_1 (cost=0.00..5.84 rows=6 width=4) (actual time=0.003..0.020 rows=5 loops=1)

  • Filter: (destinationcityid = 4000020)
  • Rows Removed by Filter: 222
11. 0.006 0.006 ↑ 1.0 1 1

Seq Scan on routegrouplocation_tbl (cost=0.00..1.71 rows=1 width=4) (actual time=0.003..0.006 rows=1 loops=1)

  • Filter: ((countryid = 640) AND (clientid = 10020))
  • Rows Removed by Filter: 46
12. 0.006 0.006 ↑ 1.0 1 1

Seq Scan on routegrouplocation_tbl routegrouplocation_tbl_1 (cost=0.00..1.71 rows=1 width=4) (actual time=0.003..0.006 rows=1 loops=1)

  • Filter: ((countryid = 640) AND (clientid = 10020))
  • Rows Removed by Filter: 46
13. 0.008 0.345 ↓ 5.0 5 1

Nested Loop (cost=0.87..52.85 rows=1 width=43) (actual time=0.118..0.345 rows=5 loops=1)

14. 0.009 0.317 ↓ 10.0 10 1

Nested Loop (cost=0.74..52.63 rows=1 width=47) (actual time=0.109..0.317 rows=10 loops=1)

15. 0.003 0.298 ↓ 5.0 5 1

Nested Loop (cost=0.59..52.43 rows=1 width=8) (actual time=0.107..0.298 rows=5 loops=1)

16. 0.004 0.204 ↑ 1.0 1 1

Nested Loop (cost=0.31..6.83 rows=1 width=4) (actual time=0.099..0.204 rows=1 loops=1)

17. 0.063 0.189 ↑ 1.0 1 1

Hash Join (cost=0.03..6.16 rows=1 width=9) (actual time=0.086..0.189 rows=1 loops=1)

  • Hash Cond: (rr.id = wt.q)
18. 0.051 0.051 ↑ 1.0 227 1

Seq Scan on routerule_tbl rr (cost=0.00..5.27 rows=227 width=5) (actual time=0.004..0.051 rows=227 loops=1)

19. 0.003 0.075 ↑ 1.0 1 1

Hash (cost=0.02..0.02 rows=1 width=4) (actual time=0.075..0.075 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
20. 0.072 0.072 ↑ 1.0 1 1

CTE Scan on wt (cost=0.00..0.02 rows=1 width=4) (actual time=0.071..0.072 rows=1 loops=1)

21. 0.011 0.011 ↑ 1.0 1 1

Index Scan using fare_brand_route_idx on farebrandroute_tbl fbr (cost=0.28..0.65 rows=1 width=11) (actual time=0.009..0.011 rows=1 loops=1)

  • Index Cond: ((clientid = 10020) AND (routeruleid = rr.id))
  • Filter: (enabled AND (CASE WHEN rr.isfarebrand THEN 'BC'::text ELSE '##'::text END = (farebrand)::text))
  • Rows Removed by Filter: 8
22. 0.091 0.091 ↑ 1.2 5 1

Index Scan using fare_brand_features_idx on farebrandfeature_tbl fbf (cost=0.28..45.54 rows=6 width=12) (actual time=0.007..0.091 rows=5 loops=1)

  • Index Cond: (farebrandrouteid = fbr.id)
23. 0.010 0.010 ↑ 1.0 2 5

Index Scan using featurelanguage_uk on feature_tbl f (cost=0.14..0.18 rows=2 width=47) (actual time=0.001..0.002 rows=2 loops=5)

  • Index Cond: (featureid = fbf.featureid)
24. 0.020 0.020 ↓ 0.0 0 10

Index Scan using language_pk on language_tbl l (cost=0.13..0.21 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=10)

  • Index Cond: (id = f.languageid)
  • Filter: ((code)::text = 'gb'::text)
  • Rows Removed by Filter: 0