explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jGpKI

Settings
# exclusive inclusive rows x rows loops node
1. 0.616 66,098.238 ↑ 1,483.0 131 1

Sort (cost=30,515,299.55..30,515,785.24 rows=194,274 width=3,504) (actual time=66,098.180..66,098.238 rows=131 loops=1)

  • Sort Key: ad.pdsectionid, ad."position", ad.position2
  • Sort Method: quicksort Memory: 52kB
2.          

Initplan (for Sort)

3. 0.001 0.033 ↑ 2,020.0 1 1

Nested Loop (cost=48.51..5,070.46 rows=2,020 width=20) (actual time=0.031..0.033 rows=1 loops=1)

4. 0.018 0.018 ↑ 1.0 1 1

Index Only Scan using ixpk_smstylesupplier01 on dbsmstylesupplier ss (cost=0.43..8.45 rows=1 width=20) (actual time=0.017..0.018 rows=1 loops=1)

  • Index Cond: (smstylesupplierid = '5509143677'::numeric)
  • Heap Fetches: 1
5. 0.004 0.014 ↑ 2,020.0 1 1

Bitmap Heap Scan on dbpdpage p (cost=48.08..5,041.81 rows=2,020 width=40) (actual time=0.012..0.014 rows=1 loops=1)

  • Recheck Cond: (smstylesupplierid = '5509143677'::numeric)
  • Heap Blocks: exact=1
6. 0.010 0.010 ↑ 2,020.0 1 1

Bitmap Index Scan on fk_pdpage_stls (cost=0.00..47.57 rows=2,020 width=0) (actual time=0.009..0.010 rows=1 loops=1)

  • Index Cond: (smstylesupplierid = '5509143677'::numeric)
7. 0.893 66,097.589 ↑ 1,483.0 131 1

Hash Left Join (cost=1,144,028.41..29,907,495.33 rows=194,274 width=3,504) (actual time=65,432.434..66,097.589 rows=131 loops=1)

  • Hash Cond: (adv.referencevalueid = rv.referencevalueid)
8. 0.458 65,984.836 ↑ 1,483.0 131 1

Hash Left Join (cost=1,143,560.88..1,526,753.35 rows=194,274 width=1,946) (actual time=65,403.074..65,984.836 rows=131 loops=1)

  • Hash Cond: (ad.pdattributeid = a.pdattributeid)
9. 5,298.795 65,976.456 ↑ 1,483.0 131 1

Hash Right Join (cost=1,143,420.00..1,523,941.20 rows=194,274 width=1,402) (actual time=65,395.101..65,976.456 rows=131 loops=1)

  • Hash Cond: (adv.pdattributedataid = ad.pdattributedataid)
10. 3,707.004 3,707.004 ↑ 1.0 6,848,928 1

Seq Scan on dbpdattributedatavalue adv (cost=0.00..121,778.28 rows=6,848,928 width=96) (actual time=0.046..3,707.004 rows=6,848,928 loops=1)

11. 1.324 56,970.657 ↑ 1,483.0 131 1

Hash (cost=1,108,928.57..1,108,928.57 rows=194,274 width=1,326) (actual time=56,970.657..56,970.657 rows=131 loops=1)

  • Buckets: 4096 Batches: 128 Memory Usage: 32kB
12. 19,467.765 56,969.333 ↑ 1,483.0 131 1

Hash Join (cost=135,559.34..1,108,928.57 rows=194,274 width=1,326) (actual time=19,931.695..56,969.333 rows=131 loops=1)

  • Hash Cond: (ad.pdsectionid = pds.pdsectionid)
13. 33,433.528 33,433.528 ↑ 1.0 38,855,236 1

Seq Scan on dbpdattributedata ad (cost=0.00..825,719.36 rows=38,855,236 width=1,326) (actual time=0.009..33,433.528 rows=38,855,236 loops=1)

14. 0.014 4,068.040 ↑ 2,044.2 11 1

Hash (cost=135,278.26..135,278.26 rows=22,486 width=40) (actual time=4,068.039..4,068.040 rows=11 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 257kB
15. 2,132.036 4,068.026 ↑ 2,044.2 11 1

Hash Join (cost=35,666.37..135,278.26 rows=22,486 width=40) (actual time=3,395.483..4,068.026 rows=11 loops=1)

  • Hash Cond: (pds.pdsectionid = dbpdsection.pdsectionid)
16. 1,935.414 1,935.414 ↑ 1.0 4,497,239 1

Seq Scan on dbpdsection pds (cost=0.00..82,522.39 rows=4,497,239 width=20) (actual time=0.007..1,935.414 rows=4,497,239 loops=1)

17. 0.009 0.576 ↑ 2,044.2 11 1

Hash (cost=35,385.29..35,385.29 rows=22,486 width=20) (actual time=0.576..0.576 rows=11 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 257kB
18. 0.036 0.567 ↑ 2,044.2 11 1

HashAggregate (cost=35,160.43..35,385.29 rows=22,486 width=20) (actual time=0.544..0.567 rows=11 loops=1)

  • Group Key: dbpdsection.pdsectionid
19. 0.023 0.531 ↑ 2,044.2 11 1

Hash Join (cost=537.03..35,104.22 rows=22,486 width=20) (actual time=0.513..0.531 rows=11 loops=1)

  • Hash Cond: (dbpdsection.sectionid = dbsection.sectionid)
20. 0.008 0.055 ↑ 2,044.2 11 1

Bitmap Heap Scan on dbpdsection (cost=522.70..34,780.71 rows=22,486 width=40) (actual time=0.050..0.055 rows=11 loops=1)

  • Recheck Cond: (pdpageid = $3)
  • Heap Blocks: exact=1
21. 0.047 0.047 ↑ 2,044.2 11 1

Bitmap Index Scan on ixpdsection01 (cost=0.00..517.08 rows=22,486 width=0) (actual time=0.046..0.047 rows=11 loops=1)

  • Index Cond: (pdpageid = $3)
22. 0.228 0.453 ↑ 1.0 459 1

Hash (cost=8.59..8.59 rows=459 width=20) (actual time=0.452..0.453 rows=459 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 27kB
23. 0.225 0.225 ↑ 1.0 459 1

Seq Scan on dbsection (cost=0.00..8.59 rows=459 width=20) (actual time=0.006..0.225 rows=459 loops=1)

24. 4.356 7.922 ↑ 1.0 4,128 1

Hash (cost=89.28..89.28 rows=4,128 width=544) (actual time=7.922..7.922 rows=4,128 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 372kB
25. 3.566 3.566 ↑ 1.0 4,128 1

Seq Scan on dbpdattribute a (cost=0.00..89.28 rows=4,128 width=544) (actual time=0.015..3.566 rows=4,128 loops=1)

26. 14.252 27.758 ↑ 1.0 16,157 1

Hash (cost=265.57..265.57 rows=16,157 width=50) (actual time=27.757..27.758 rows=16,157 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 783kB
27. 13.506 13.506 ↑ 1.0 16,157 1

Seq Scan on dbreferencevalue rv (cost=0.00..265.57 rows=16,157 width=50) (actual time=0.737..13.506 rows=16,157 loops=1)

28.          

SubPlan (for Hash Left Join)

29. 79.255 79.255 ↑ 11.0 1 131

Index Scan using pk_18ndscrptn on dbi18ndescription i (cost=0.57..48.69 rows=11 width=516) (actual time=0.598..0.605 rows=1 loops=131)

  • Index Cond: ((rid = a.pdattributeid) AND (languageid = '3064863'::numeric) AND ((descriptionkey)::text = 'Description'::text))
30. 1.572 1.572 ↓ 0.0 0 131

Index Scan using pk_18ndscrptn on dbi18ndescription i_1 (cost=0.57..48.69 rows=11 width=516) (actual time=0.012..0.012 rows=0 loops=131)

  • Index Cond: ((rid = a.pdattributeid) AND (languageid = '3064863'::numeric) AND ((descriptionkey)::text = 'LongDescription'::text))
31. 3.275 3.275 ↓ 0.0 0 131

Index Scan using pk_18ndscrptn on dbi18ndescription i_2 (cost=0.57..48.69 rows=11 width=516) (actual time=0.025..0.025 rows=0 loops=131)

  • Index Cond: ((rid = adv.referencevalueid) AND (languageid = '3064863'::numeric) AND ((descriptionkey)::text = 'Description'::text))
Planning time : 1.408 ms
Execution time : 66,098.597 ms