explain.depesz.com

PostgreSQL's explain analyze made readable

Result: tjkI5

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.347 307.343 ↓ 11.9 298 1

Sort (cost=968.95..969.01 rows=25 width=179) (actual time=307.336..307.343 rows=298 loops=1)

  • Sort Key: sub.id1, sub.id2, sub.id3, sub.id4
  • Sort Method: quicksort Memory: 192kB
2. 0.079 306.996 ↓ 11.9 298 1

Subquery Scan on sub (cost=966.24..968.37 rows=25 width=179) (actual time=306.329..306.996 rows=298 loops=1)

3. 0.220 306.917 ↓ 11.9 298 1

WindowAgg (cost=966.24..967.87 rows=25 width=147) (actual time=306.325..306.917 rows=298 loops=1)

4. 0.226 306.697 ↓ 11.9 298 1

WindowAgg (cost=966.24..967.31 rows=25 width=147) (actual time=306.321..306.697 rows=298 loops=1)

5. 0.142 306.471 ↓ 11.9 298 1

WindowAgg (cost=966.24..966.81 rows=25 width=147) (actual time=306.316..306.471 rows=298 loops=1)

6. 1.105 306.329 ↓ 11.9 298 1

Sort (cost=966.24..966.31 rows=25 width=147) (actual time=306.311..306.329 rows=298 loops=1)

  • Sort Key: b.pk_szid, c.pk_szid, d.pk_szdeviceid
  • Sort Method: quicksort Memory: 526kB
7. 7.276 305.224 ↓ 11.9 298 1

Nested Loop Left Join (cost=791.02..965.66 rows=25 width=147) (actual time=227.181..305.224 rows=298 loops=1)

  • Join Filter: (((xpath('/ApplicationProfile/ApplicationComponentIDs/ApplicationComponentID[@Family="Preset"]/text()'::text, b.xmldata, '{}'::text[]))[1])::text = tblapplicationcomponent.pk_szid)
8. 0.019 0.019 ↑ 1.0 1 1

Index Scan using tblapplicationcomponent_pkey on tblapplicationcomponent (cost=0.14..8.16 rows=1 width=37) (actual time=0.017..0.019 rows=1 loops=1)

  • Index Cond: (pk_szid = '100'::text)
9. 70.177 297.929 ↓ 11.9 298 1

Hash Right Join (cost=790.88..957.13 rows=25 width=110) (actual time=227.128..297.929 rows=298 loops=1)

  • Hash Cond: (((xpath('/DeviceProfileAssignment/ProfileID/text()'::text, d.xmldata, '{}'::text[]))[1])::text = c.pk_szid)
10. 0.666 0.666 ↑ 1.0 5,000 1

Seq Scan on tbldeviceprofileassignment d (cost=0.00..141.00 rows=5,000 width=38) (actual time=0.002..0.666 rows=5,000 loops=1)

11. 0.125 227.086 ↓ 8.0 199 1

Hash (cost=790.56..790.56 rows=25 width=72) (actual time=227.086..227.086 rows=199 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 142kB
12. 104.288 226.961 ↓ 8.0 199 1

Hash Right Join (cost=405.31..790.56 rows=25 width=72) (actual time=121.730..226.961 rows=199 loops=1)

  • Hash Cond: (((xpath('/DeviceProfile/ApplicationProfileIDs/ApplicationProfileID/text()'::text, c.xmldata, '{}'::text[]))[1])::text = b.pk_szid)
13. 0.996 0.996 ↑ 1.0 5,000 1

Seq Scan on tbldeviceprofile c (cost=0.00..360.00 rows=5,000 width=36) (actual time=0.002..0.996 rows=5,000 loops=1)

14. 0.037 121.677 ↓ 4.0 100 1

Hash (cost=405.00..405.00 rows=25 width=36) (actual time=121.677..121.677 rows=100 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 50kB
15. 121.640 121.640 ↓ 4.0 100 1

Seq Scan on tblapplicationprofile b (cost=0.00..405.00 rows=25 width=36) (actual time=0.099..121.640 rows=100 loops=1)

  • Filter: (((xpath('/ApplicationProfile/ApplicationComponentIDs/ApplicationComponentID[@Family="Preset"]/text()'::text, xmldata, '{}'::text[]))[1])::text = '100'::text)
  • Rows Removed by Filter: 4900