explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7OjM

Settings
# exclusive inclusive rows x rows loops node
1. 0.010 479.434 ↑ 8.3 3 1

Limit (cost=84,498.41..84,532.54 rows=25 width=1,030) (actual time=479.404..479.434 rows=3 loops=1)

2. 0.095 479.424 ↑ 723.3 3 1

Result (cost=84,498.41..87,460.46 rows=2,170 width=1,030) (actual time=479.395..479.424 rows=3 loops=1)

3. 0.046 479.329 ↑ 723.3 3 1

Sort (cost=57,417.47..57,422.89 rows=2,170 width=984) (actual time=479.329..479.329 rows=3 loops=1)

  • Sort Key: (CASE WHEN ("mdLoca"."nLocaID" = ANY ('{16287,913}'::integer[])) THEN true ELSE false END) DESC, (CASE WHEN (hashed SubPlan 1) THEN true ELSE false END) DESC, (getcodename(("mdLoca"."sLocaCode")::text, (CASE WHEN ("mdLoca"."sLocaCode" IS NOT NULL) THEN "mdLoca"."sLocaName" ELSE COALESCE("mdLoca"."sLocaName", "mdLoca"."sAddr", "mdLoca"."sCity") END)::text))
  • Sort Method: quicksort Memory: 26kB
4. 0.194 479.283 ↑ 723.3 3 1

Nested Loop Left Join (cost=27,096.57..57,356.23 rows=2,170 width=984) (actual time=194.361..479.283 rows=3 loops=1)

  • Join Filter: ((mdsettlocahours."nDivID" = "mdLoca"."nDivID") AND (mdsettlocahours."nLocaID" = "mdLoca"."nLocaID"))
  • Rows Removed by Join Filter: 2
5. 0.004 285.876 ↑ 723.3 3 1

Nested Loop Left Join (cost=15.62..29,658.41 rows=2,170 width=795) (actual time=0.986..285.876 rows=3 loops=1)

  • Join Filter: (mdsettdivhours."nDivID" = "mdLoca"."nDivID")
6. 0.043 285.788 ↑ 723.3 3 1

Hash Left Join (cost=15.62..29,622.00 rows=2,170 width=763) (actual time=0.900..285.788 rows=3 loops=1)

  • Hash Cond: (("mdLoca"."sCtry")::bpchar = mdctry."sCtryCode")
7. 285.421 285.421 ↑ 723.3 3 1

Seq Scan on md_locas "mdLoca" (cost=0.00..29,576.54 rows=2,170 width=605) (actual time=0.538..285.421 rows=3 loops=1)

  • Filter: ((NOT "bDeleted") AND ("sLocaCode" IS NOT NULL) AND ("sTimezone" IS NOT NULL) AND ("fLat" IS NOT NULL) AND ("fLong" IS NOT NULL) AND ("nDivID" = 10) AND (("nLocaID" = ANY ('{16287,913}'::integer[])) OR ('{15582}'::integer[] <@ "aLinkedCusts")))
  • Rows Removed by Filter: 500199
8. 0.193 0.324 ↓ 1.1 275 1

Hash (cost=12.50..12.50 rows=250 width=158) (actual time=0.324..0.324 rows=275 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 21kB
9. 0.131 0.131 ↓ 1.1 275 1

Seq Scan on md_ctrys mdctry (cost=0.00..12.50 rows=250 width=158) (actual time=0.014..0.131 rows=275 loops=1)

10. 0.006 0.084 ↓ 0.0 0 3

Materialize (cost=0.00..3.86 rows=1 width=36) (actual time=0.028..0.028 rows=0 loops=3)

11. 0.078 0.078 ↓ 0.0 0 1

Seq Scan on md_settings mdsettdivhours (cost=0.00..3.85 rows=1 width=36) (actual time=0.078..0.078 rows=0 loops=1)

  • Filter: ((NOT "bDeleted") AND ("nCustID" IS NULL) AND ("nLocaID" IS NULL) AND ("sSettType" = 'LocaHours'::"mdSettingType") AND ("nDivID" = 10))
  • Rows Removed by Filter: 59
12. 0.014 0.081 ↑ 1.0 1 3

Materialize (cost=0.00..3.86 rows=1 width=40) (actual time=0.026..0.027 rows=1 loops=3)

13. 0.067 0.067 ↑ 1.0 1 1

Seq Scan on md_settings mdsettlocahours (cost=0.00..3.85 rows=1 width=40) (actual time=0.066..0.067 rows=1 loops=1)

  • Filter: ((NOT "bDeleted") AND ("nCustID" IS NULL) AND ("sSettType" = 'LocaHours'::"mdSettingType") AND ("nDivID" = 10))
  • Rows Removed by Filter: 58
14.          

SubPlan (for Nested Loop Left Join)

15. 193.132 193.132 ↑ 2,168.0 1 1

Seq Scan on md_locas mdloca (cost=0.00..27,075.53 rows=2,168 width=4) (actual time=0.190..193.132 rows=1 loops=1)

  • Filter: ('{15582}'::integer[] <@ "aLinkedCusts")
  • Rows Removed by Filter: 500201
Planning time : 5.528 ms
Execution time : 480.001 ms