explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MfIhh

Settings
# exclusive inclusive rows x rows loops node
1. 0.067 2.296 ↑ 21.3 96 1

Sort (cost=649.75..650.78 rows=2,045 width=1,676) (actual time=2.291..2.296 rows=96 loops=1)

  • Sort Key: ids.sort_order__c
  • Sort Method: quicksort Memory: 50kB
2.          

CTE ids

3. 0.140 2.114 ↑ 21.4 96 1

Recursive Union (cost=1.75..613.91 rows=2,055 width=225) (actual time=0.088..2.114 rows=96 loops=1)

4. 0.015 0.296 ↓ 2.8 42 1

Nested Loop (cost=1.75..23.55 rows=15 width=225) (actual time=0.085..0.296 rows=42 loops=1)

5. 0.020 0.209 ↓ 1.8 18 1

Nested Loop (cost=1.72..21.67 rows=10 width=74) (actual time=0.074..0.209 rows=18 loops=1)

6. 0.056 0.099 ↓ 1.8 18 1

Hash Join (cost=1.66..3.96 rows=10 width=61) (actual time=0.053..0.099 rows=18 loops=1)

  • Hash Cond: ((ppc.cpq_product_code__c)::text = (pc.sfid)::text)
  • Join Filter: ((((pc.name)::text = 'IP-VPN'::text) AND ((ppc.version__c)::text = '2.0.0'::text)) OR (((pc.name)::text = 'IP-DIA-AGG'::text) AND ((ppc.version__c)::text = '2.0.0'::text)) OR (((pc.name)::text = 'IP-DIA'::text) AND ((ppc.version__c)::text = '2.0.0'::text)) OR (((pc.name)::text = 'IP-DIA-AGG'::text) AND ((ppc.version__c)::text = '1.0.0'::text)) OR (((pc.name)::text = 'IP-DIA'::text) AND ((ppc.version__c)::text = '1.0.0'::text)) OR (((pc.name)::text = 'IP-VPN'::text) AND ((ppc.version__c)::text = '1.0.0'::text)) OR (((pc.name)::text = 'WAVES-STD-P2P'::text) AND ((ppc.version__c)::text = '2.0.0'::text)) OR (((pc.name)::text = 'WAVES-STD-P2P'::text) AND ((ppc.version__c)::text = '1.0.0'::text)) OR (((pc.name)::text = 'ETH-PDN-P2P'::text) AND ((ppc.version__c)::text = '2.0.0'::text)) OR (((pc.name)::text = 'ETH-ELINE-UNI'::text) AND ((ppc.version__c)::text = '2.0.0'::text)) OR (((pc.name)::text = 'ETH-ELINE-P2P'::text) AND ((ppc.version__c)::text = '2.0.0'::text)) OR (((pc.name)::text = 'ETH-ELINE-NNI'::text) AND ((ppc.version__c)::text = '2.0.0'::text)) OR (((pc.name)::text = 'ETH-ELAN-M2M'::text) AND ((ppc.version__c)::text = '2.0.0'::text)) OR (((pc.name)::text = 'ETH-ELINE-UNI'::text) AND ((ppc.version__c)::text = '1.0.0'::text)) OR (((pc.name)::text = 'ETH-ELINE-P2P'::text) AND ((ppc.version__c)::text = '1.0.0'::text)) OR (((pc.name)::text = 'ETH-ELINE-NNI'::text) AND ((ppc.version__c)::text = '1.0.0'::text)) OR (((pc.name)::text = 'ETH-ELAN-M2M'::text) AND ((ppc.version__c)::text = '1.0.0'::text)) OR (((pc.name)::text = 'ETH-PDN-P2P'::text) AND ((ppc.version__c)::text = '1.0.0'::text)))
  • Rows Removed by Join Filter: 12
7. 0.012 0.012 ↓ 1.0 84 1

Seq Scan on cpq_plan_product_code__c ppc (cost=0.00..2.25 rows=83 width=63) (actual time=0.005..0.012 rows=84 loops=1)

8. 0.007 0.031 ↑ 1.7 9 1

Hash (cost=1.61..1.61 rows=15 width=36) (actual time=0.031..0.031 rows=9 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
9. 0.024 0.024 ↑ 1.7 9 1

Seq Scan on cpq_product_code__c pc (cost=0.00..1.61 rows=15 width=36) (actual time=0.005..0.024 rows=9 loops=1)

  • Filter: (((name)::text = 'IP-VPN'::text) OR ((name)::text = 'IP-DIA-AGG'::text) OR ((name)::text = 'IP-DIA'::text) OR ((name)::text = 'IP-DIA-AGG'::text) OR ((name)::text = 'IP-DIA'::text) OR ((name)::text = 'IP-VPN'::text) OR ((name)::text = 'WAVES-STD-P2P'::text) OR ((name)::text = 'WAVES-STD-P2P'::text) OR ((name)::text = 'ETH-PDN-P2P'::text) OR ((name)::text = 'ETH-ELINE-UNI'::text) OR ((name)::text = 'ETH-ELINE-P2P'::text) OR ((name)::text = 'ETH-ELINE-NNI'::text) OR ((name)::text = 'ETH-ELAN-M2M'::text) OR ((name)::text = 'ETH-ELINE-UNI'::text) OR ((name)::text = 'ETH-ELINE-P2P'::text) OR ((name)::text = 'ETH-ELINE-NNI'::text) OR ((name)::text = 'ETH-ELAN-M2M'::text) OR ((name)::text = 'ETH-PDN-P2P'::text))
  • Rows Removed by Filter: 42
10. 0.090 0.090 ↑ 1.0 1 18

Index Scan using hcu_idx_cpq_plan__c_sfid on cpq_plan__c plan (cost=0.06..1.77 rows=1 width=32) (actual time=0.005..0.005 rows=1 loops=18)

  • Index Cond: ((sfid)::text = (ppc.cpq_plan__c)::text)
11. 0.072 0.072 ↑ 1.0 2 18

Index Scan using hc_idx_cpq_plan_component__c_cpq_plan_product_code__c on cpq_plan_component__c pcomp (cost=0.03..0.18 rows=2 width=151) (actual time=0.003..0.004 rows=2 loops=18)

  • Index Cond: ((cpq_plan_product_code__c)::text = (ppc.sfid)::text)
12. 0.056 1.678 ↑ 7.6 27 2

Hash Join (cost=22.97..57.80 rows=204 width=225) (actual time=0.559..0.839 rows=27 loops=2)

  • Hash Cond: ((compr.parent_component_relationship__c)::text = (ids_1.sfid)::text)
13. 0.106 1.588 ↑ 1.2 166 2

Hash Join (cost=21.55..55.61 rows=204 width=225) (actual time=0.288..0.794 rows=166 loops=2)

  • Hash Cond: ((ppc_1.sfid)::text = (pcomp_1.cpq_plan_product_code__c)::text)
14. 0.055 1.230 ↓ 1.0 84 2

Hash Join (cost=11.29..44.60 rows=83 width=74) (actual time=0.157..0.615 rows=84 loops=2)

  • Hash Cond: ((ppc_1.cpq_product_code__c)::text = (pc_1.sfid)::text)
15. 0.668 1.152 ↓ 1.0 84 2

Merge Join (cost=9.96..43.22 rows=83 width=76) (actual time=0.140..0.576 rows=84 loops=2)

  • Merge Cond: ((plan_1.sfid)::text = (ppc_1.cpq_plan__c)::text)
16. 0.416 0.416 ↑ 4.4 577 2

Index Scan using hcu_idx_cpq_plan__c_sfid on cpq_plan__c plan_1 (cost=0.06..172.98 rows=2,566 width=32) (actual time=0.007..0.208 rows=577 loops=2)

17. 0.051 0.068 ↓ 1.0 84 2

Sort (cost=2.78..2.82 rows=83 width=63) (actual time=0.029..0.034 rows=84 loops=2)

  • Sort Key: ppc_1.cpq_plan__c
  • Sort Method: quicksort Memory: 36kB
18. 0.017 0.017 ↓ 1.0 84 1

Seq Scan on cpq_plan_product_code__c ppc_1 (cost=0.00..2.25 rows=83 width=63) (actual time=0.002..0.017 rows=84 loops=1)

19. 0.014 0.023 ↑ 1.0 51 1

Hash (cost=1.15..1.15 rows=51 width=36) (actual time=0.023..0.023 rows=51 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
20. 0.009 0.009 ↑ 1.0 51 1

Seq Scan on cpq_product_code__c pc_1 (cost=0.00..1.15 rows=51 width=36) (actual time=0.002..0.009 rows=51 loops=1)

21. 0.073 0.252 ↑ 1.2 166 1

Hash (cost=9.54..9.54 rows=204 width=170) (actual time=0.252..0.252 rows=166 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 40kB
22. 0.076 0.179 ↑ 1.2 166 1

Hash Join (cost=4.81..9.54 rows=204 width=170) (actual time=0.101..0.179 rows=166 loops=1)

  • Hash Cond: ((compr.child_component_relationship__c)::text = (pcomp_1.sfid)::text)
23. 0.017 0.017 ↑ 1.2 166 1

Seq Scan on cpq_plan_component_relationship__c compr (cost=0.00..4.61 rows=204 width=38) (actual time=0.003..0.017 rows=166 loops=1)

24. 0.053 0.086 ↓ 1.0 127 1

Hash (cost=4.38..4.38 rows=125 width=151) (actual time=0.086..0.086 rows=127 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 31kB
25. 0.033 0.033 ↓ 1.0 127 1

Seq Scan on cpq_plan_component__c pcomp_1 (cost=0.00..4.38 rows=125 width=151) (actual time=0.002..0.033 rows=127 loops=1)

26. 0.020 0.034 ↑ 3.1 48 2

Hash (cost=0.90..0.90 rows=150 width=54) (actual time=0.017..0.017 rows=48 loops=2)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
27. 0.014 0.014 ↑ 3.1 48 2

WorkTable Scan on ids ids_1 (cost=0.00..0.90 rows=150 width=54) (actual time=0.001..0.007 rows=48 loops=2)

28. 2.229 2.229 ↑ 21.3 96 1

CTE Scan on ids (cost=0.00..13.35 rows=2,045 width=1,676) (actual time=0.092..2.229 rows=96 loops=1)

  • Filter: (cpq_plan_product_code__c IS NOT NULL)
Planning time : 2.810 ms