explain.depesz.com

PostgreSQL's explain analyze made readable

Result: iFCt

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 619.311 ↑ 30.8 23 1

Subquery Scan on tab1 (cost=89,579.26..89,588.11 rows=708 width=22) (actual time=619.306..619.311 rows=23 loops=1)

2. 0.023 619.307 ↑ 30.8 23 1

Sort (cost=89,579.26..89,581.03 rows=708 width=43) (actual time=619.305..619.307 rows=23 loops=1)

  • Sort Key: c.bq03grpid
  • Sort Method: quicksort Memory: 26kB
3. 20.461 619.284 ↑ 30.8 23 1

HashAggregate (cost=89,531.58..89,545.74 rows=708 width=43) (actual time=619.270..619.284 rows=23 loops=1)

  • Group Key: c.bq03grpid, pdest.bq19isautorelease
  • Filter: ((count(CASE WHEN ((((bq05_vehdefect.bq05etat)::text ~~ 'CO_ '::text) AND (NOT (SubPlan 3))) OR ((bq05_vehdefect.bq05etat)::text ~~ 'IO_ '::text) OR ((bq05_vehdefect.bq05etat)::text ~~ 'AO_ '::text)) THEN 1 ELSE NULL::integer END) > 0) OR (count(CASE WHEN (((((bq05_vehdefect.bq05etat)::text ~~ 'CO_O___'::text) OR ((bq05_vehdefect.bq05etat)::text ~~ 'CO___O_'::text)) AND (NOT (SubPlan 4))) OR ((bq05_vehdefect.bq05etat)::text ~~ 'IO_O___'::text) OR ((bq05_vehdefect.bq05etat)::text ~~ 'IO___O_'::text) OR ((bq05_vehdefect.bq05etat)::text ~~ 'AO_O___'::text) OR ((bq05_vehdefect.bq05etat)::text ~~ 'AO___O_'::text)) THEN 1 ELSE NULL::integer END) > 0))
  • Rows Removed by Filter: 12
4. 2.776 585.728 ↓ 1.6 7,075 1

Hash Join (cost=38,563.59..61,503.26 rows=4,539 width=43) (actual time=278.942..585.728 rows=7,075 loops=1)

  • Hash Cond: (ca.id = cc.id)
5. 51.843 582.302 ↑ 13.4 7,226 1

Hash Join (cost=38,471.98..61,003.67 rows=96,689 width=51) (actual time=278.274..582.302 rows=7,226 loops=1)

  • Hash Cond: ((bq05_vehdefect.bq05vin)::text = (bq04_vehicle.bq04vin)::text)
6. 42.710 258.300 ↓ 1.8 112,972 1

Hash Join (cost=702.68..20,824.63 rows=62,286 width=67) (actual time=5.599..258.300 rows=112,972 loops=1)

  • Hash Cond: (bq05_vehdefect.bq05defectid = a.bq01defectid)
7. 210.037 210.037 ↑ 2.9 158,331 1

Seq Scan on bq05_vehdefect (cost=0.00..17,767.27 rows=461,818 width=41) (actual time=0.018..210.037 rows=158,331 loops=1)

  • Filter: (((bq05eligib)::text = 'O'::text) AND ("left"((bq05etat)::text, 1) <> 'R'::text))
  • Rows Removed by Filter: 379976
8. 0.208 5.553 ↑ 1.1 425 1

Hash (cost=696.62..696.62 rows=485 width=30) (actual time=5.553..5.553 rows=425 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 35kB
9. 1.570 5.345 ↑ 1.1 425 1

Hash Join (cost=257.27..696.62 rows=485 width=30) (actual time=2.632..5.345 rows=425 loops=1)

  • Hash Cond: (ca.bqv_id = c.bq03grpid)
10. 1.173 1.173 ↑ 1.0 3,089 1

Seq Scan on "case" ca (cost=0.00..395.89 rows=3,089 width=16) (actual time=0.004..1.173 rows=3,089 loops=1)

11. 0.200 2.602 ↓ 1.0 491 1

Hash (cost=251.21..251.21 rows=485 width=22) (actual time=2.602..2.602 rows=491 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 35kB
12. 1.284 2.402 ↓ 1.0 491 1

Hash Join (cost=13.91..251.21 rows=485 width=22) (actual time=0.305..2.402 rows=491 loops=1)

  • Hash Cond: (a.bq01defectid = c.bq03defectid)
13. 0.838 0.838 ↓ 1.0 3,600 1

Seq Scan on bq01_defect a (cost=0.00..218.96 rows=3,596 width=14) (actual time=0.006..0.838 rows=3,600 loops=1)

14. 0.143 0.280 ↓ 1.0 491 1

Hash (cost=7.85..7.85 rows=485 width=8) (actual time=0.280..0.280 rows=491 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 28kB
15. 0.137 0.137 ↓ 1.0 491 1

Seq Scan on bq03_defectgrouping c (cost=0.00..7.85 rows=485 width=8) (actual time=0.011..0.137 rows=491 loops=1)

16. 12.160 272.159 ↑ 3.3 28,135 1

Hash (cost=36,054.18..36,054.18 rows=93,369 width=20) (actual time=272.159..272.159 rows=28,135 loops=1)

  • Buckets: 65536 Batches: 2 Memory Usage: 1227kB
17. 169.282 259.999 ↑ 3.3 28,135 1

Hash Join (cost=22.87..36,054.18 rows=93,369 width=20) (actual time=3.903..259.999 rows=28,135 loops=1)

  • Hash Cond: ("left"((bq04_vehicle.bq04dest)::text, 9) = ((db.bq43dcz)::text || (db.bq43argument)::text))
18. 86.855 86.855 ↓ 1.0 345,958 1

Seq Scan on bq04_vehicle (cost=0.00..27,749.12 rows=345,812 width=33) (actual time=0.007..86.855 rows=345,958 loops=1)

19. 1.435 3.862 ↓ 48.0 2,591 1

Hash (cost=22.19..22.19 rows=54 width=12) (actual time=3.862..3.862 rows=2,591 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 146kB
20. 0.602 2.427 ↓ 48.0 2,591 1

Nested Loop (cost=0.56..22.19 rows=54 width=12) (actual time=0.101..2.427 rows=2,591 loops=1)

21. 0.011 0.105 ↓ 2.0 2 1

Nested Loop (cost=0.27..14.14 rows=1 width=8) (actual time=0.048..0.105 rows=2 loops=1)

22. 0.060 0.060 ↓ 2.0 2 1

Seq Scan on user_countries uc (cost=0.00..5.84 rows=1 width=3) (actual time=0.021..0.060 rows=2 loops=1)

  • Filter: (user_id = 187)
  • Rows Removed by Filter: 225
23. 0.034 0.034 ↑ 1.0 1 2

Index Scan using bq19_pays_pkey on bq19_pays pdest (cost=0.27..8.29 rows=1 width=5) (actual time=0.016..0.017 rows=1 loops=2)

  • Index Cond: ((bq19codepays)::text = (uc.countries_code)::text)
24. 1.720 1.720 ↓ 8.9 1,296 2

Index Scan using idx_bq43codepays on bq43_dcz db (cost=0.29..6.60 rows=145 width=13) (actual time=0.033..0.860 rows=1,296 loops=2)

  • Index Cond: ((bq43codepays)::text = (pdest.bq19codepays)::text)
  • Filter: ((bq43argument)::text <> ''::text)
  • Rows Removed by Filter: 1
25. 0.051 0.650 ↓ 1.1 165 1

Hash (cost=89.80..89.80 rows=145 width=8) (actual time=0.650..0.650 rows=165 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
26. 0.599 0.599 ↓ 1.1 165 1

Seq Scan on corporate_case cc (cost=0.00..89.80 rows=145 width=8) (actual time=0.320..0.599 rows=165 loops=1)

  • Filter: ((block_order_date IS NOT NULL) AND ((classification_type)::text = 'BLQ'::text))
  • Rows Removed by Filter: 1200
27.          

SubPlan (for HashAggregate)

28. 0.008 0.032 ↑ 1.0 1 4

Result (cost=0.00..1.53 rows=1 width=0) (actual time=0.008..0.008 rows=1 loops=4)

  • One-Time Filter: (a.bq01defectid = bq05_vehdefect.bq05defectid)
29. 0.024 0.024 ↑ 1.0 1 4

Seq Scan on bq10_plantcle (cost=0.00..1.53 rows=1 width=0) (actual time=0.006..0.006 rows=1 loops=4)

  • Filter: (((bq10clecode)::text = (bq05_vehdefect.bq05sitb)::text) AND ((bq10plantcode)::text = (a.bq01plantcode)::text))
  • Rows Removed by Filter: 17
30. 0.000 5.788 ↑ 1.0 1 1,447

Result (cost=0.00..1.53 rows=1 width=0) (actual time=0.004..0.004 rows=1 loops=1,447)

  • One-Time Filter: (a.bq01defectid = bq05_vehdefect.bq05defectid)
31. 5.788 5.788 ↑ 1.0 1 1,447

Seq Scan on bq10_plantcle bq10_plantcle_1 (cost=0.00..1.53 rows=1 width=0) (actual time=0.004..0.004 rows=1 loops=1,447)

  • Filter: (((bq10clecode)::text = (bq05_vehdefect.bq05sitb)::text) AND ((bq10plantcode)::text = (a.bq01plantcode)::text))
  • Rows Removed by Filter: 18
32. 0.016 0.040 ↑ 1.0 1 4

Result (cost=0.00..1.53 rows=1 width=0) (actual time=0.010..0.010 rows=1 loops=4)

  • One-Time Filter: (a.bq01defectid = bq05_vehdefect.bq05defectid)
33. 0.024 0.024 ↑ 1.0 1 4

Seq Scan on bq10_plantcle bq10_plantcle_2 (cost=0.00..1.53 rows=1 width=0) (actual time=0.006..0.006 rows=1 loops=4)

  • Filter: (((bq10clecode)::text = (bq05_vehdefect.bq05sitb)::text) AND ((bq10plantcode)::text = (a.bq01plantcode)::text))
  • Rows Removed by Filter: 17
34. 1.447 7.235 ↑ 1.0 1 1,447

Result (cost=0.00..1.53 rows=1 width=0) (actual time=0.005..0.005 rows=1 loops=1,447)

  • One-Time Filter: (a.bq01defectid = bq05_vehdefect.bq05defectid)
35. 5.788 5.788 ↑ 1.0 1 1,447

Seq Scan on bq10_plantcle bq10_plantcle_3 (cost=0.00..1.53 rows=1 width=0) (actual time=0.004..0.004 rows=1 loops=1,447)

  • Filter: (((bq10clecode)::text = (bq05_vehdefect.bq05sitb)::text) AND ((bq10plantcode)::text = (a.bq01plantcode)::text))
  • Rows Removed by Filter: 18
Planning time : 7.888 ms
Execution time : 619.843 ms