explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qrUl

Settings
# exclusive inclusive rows x rows loops node
1. 0.868 123.194 ↓ 5.6 3,908 1

Sort (cost=14,701.34..14,703.10 rows=703 width=112) (actual time=123.061..123.194 rows=3,908 loops=1)

  • Sort Key: cacategory0_.cac_code
  • Sort Method: quicksort Memory: 647kB
2.          

CTE cacategorytreecte

3. 0.166 6.884 ↑ 77.0 607 1

Recursive Union (cost=284.29..8,648.16 rows=46,720 width=11) (actual time=2.536..6.884 rows=607 loops=1)

4. 0.204 2.966 ↓ 1.0 607 1

Hash Join (cost=284.29..363.68 rows=600 width=11) (actual time=2.535..2.966 rows=607 loops=1)

  • Hash Cond: (cacategory0__1.cac_id = suppliers1__1.casc_cac_id)
5. 0.235 0.235 ↑ 1.0 2,750 1

Seq Scan on ca_category cacategory0__1 (cost=0.00..65.50 rows=2,750 width=11) (actual time=0.001..0.235 rows=2,750 loops=1)

  • Filter: (cac_id IS NOT NULL)
6. 0.044 2.527 ↓ 1.0 607 1

Hash (cost=276.79..276.79 rows=600 width=4) (actual time=2.527..2.527 rows=607 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 30kB
7. 1.407 2.483 ↓ 1.0 607 1

HashAggregate (cost=270.79..276.79 rows=600 width=4) (actual time=2.438..2.483 rows=607 loops=1)

  • Group Key: suppliers1__1.casc_cac_id
8. 1.076 1.076 ↓ 1.0 15,047 1

Seq Scan on ca_supplier_category suppliers1__1 (cost=0.00..234.83 rows=14,383 width=4) (actual time=0.002..1.076 rows=15,047 loops=1)

9. 0.077 3.752 ↑ 10.2 451 1

Hash Join (cost=478.93..735.01 rows=4,612 width=11) (actual time=3.655..3.752 rows=451 loops=1)

  • Hash Cond: (cacategory1_.id = cacategory0__2.cac_parent_id)
10. 0.023 0.023 ↑ 9.9 607 1

WorkTable Scan on cacategorytreecte cacategory1_ (cost=0.00..120.00 rows=6,000 width=4) (actual time=0.001..0.023 rows=607 loops=1)

11. 0.048 3.652 ↑ 1.0 456 1

Hash (cost=472.99..472.99 rows=475 width=15) (actual time=3.652..3.652 rows=456 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 30kB
12. 0.067 3.604 ↑ 1.0 462 1

Hash Anti Join (cost=384.16..472.99 rows=475 width=15) (actual time=3.161..3.604 rows=462 loops=1)

  • Hash Cond: (cacategory0__2.cac_id = children3_.cac_parent_id)
13. 0.254 3.074 ↓ 1.0 607 1

Hash Join (cost=284.29..363.68 rows=600 width=15) (actual time=2.695..3.074 rows=607 loops=1)

  • Hash Cond: (cacategory0__2.cac_id = suppliers2_.casc_cac_id)
14. 0.134 0.134 ↑ 1.0 2,750 1

Seq Scan on ca_category cacategory0__2 (cost=0.00..65.50 rows=2,750 width=15) (actual time=0.002..0.134 rows=2,750 loops=1)

15. 0.046 2.686 ↓ 1.0 607 1

Hash (cost=276.79..276.79 rows=600 width=4) (actual time=2.686..2.686 rows=607 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 30kB
16. 1.550 2.640 ↓ 1.0 607 1

HashAggregate (cost=270.79..276.79 rows=600 width=4) (actual time=2.591..2.640 rows=607 loops=1)

  • Group Key: suppliers2_.casc_cac_id
17. 1.090 1.090 ↓ 1.0 15,047 1

Seq Scan on ca_supplier_category suppliers2_ (cost=0.00..234.83 rows=14,383 width=4) (actual time=0.002..1.090 rows=15,047 loops=1)

18. 0.204 0.463 ↑ 1.1 2,539 1

Hash (cost=65.50..65.50 rows=2,750 width=4) (actual time=0.463..0.463 rows=2,539 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 122kB
19. 0.259 0.259 ↑ 1.0 2,750 1

Seq Scan on ca_category children3_ (cost=0.00..65.50 rows=2,750 width=4) (actual time=0.002..0.259 rows=2,750 loops=1)

20. 9.002 122.326 ↓ 5.6 3,908 1

GroupAggregate (cost=5,993.57..6,019.94 rows=703 width=112) (actual time=111.863..122.326 rows=3,908 loops=1)

  • Group Key: cacategory0_.cac_id, suppliers1_.casc_cac_id, suppliers1_.casc_cas_id, party3_.p_name, casupplier2_.cas_erp_code
21. 16.641 113.324 ↓ 45.8 32,200 1

Sort (cost=5,993.57..5,995.33 rows=703 width=55) (actual time=111.837..113.324 rows=32,200 loops=1)

  • Sort Key: cacategory0_.cac_id, suppliers1_.casc_cac_id, suppliers1_.casc_cas_id, party3_.p_name, casupplier2_.cas_erp_code
  • Sort Method: quicksort Memory: 4159kB
22. 4.730 96.683 ↓ 45.8 32,200 1

Nested Loop (cost=1,953.14..5,960.33 rows=703 width=55) (actual time=53.847..96.683 rows=32,200 loops=1)

23. 4.219 59.753 ↓ 45.2 32,200 1

Hash Join (cost=1,952.72..4,789.86 rows=712 width=52) (actual time=53.808..59.753 rows=32,200 loops=1)

  • Hash Cond: (caaggregat4_.caas_cac_id = cacategory0_.cac_id)
24. 1.779 2.119 ↓ 1.0 8,967 1

Bitmap Heap Scan on ca_aggregated_spend caaggregat4_ (cost=225.52..3,022.92 rows=8,693 width=8) (actual time=0.389..2.119 rows=8,967 loops=1)

  • Recheck Cond: ((caas_period >= '2018-11-29 00:00:00'::timestamp without time zone) AND (caas_period <= '2019-11-29 00:00:00'::timestamp without time zone))
  • Heap Blocks: exact=529
25. 0.340 0.340 ↓ 1.0 8,967 1

Bitmap Index Scan on idx_caas_period_filter (cost=0.00..223.35 rows=8,693 width=0) (actual time=0.340..0.340 rows=8,967 loops=1)

  • Index Cond: ((caas_period >= '2018-11-29 00:00:00'::timestamp without time zone) AND (caas_period <= '2019-11-29 00:00:00'::timestamp without time zone))
26. 2.785 53.415 ↓ 65.2 14,743 1

Hash (cost=1,724.37..1,724.37 rows=226 width=56) (actual time=53.415..53.415 rows=14,743 loops=1)

  • Buckets: 16384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1379kB
27. 5.362 50.630 ↓ 65.2 14,743 1

Nested Loop Left Join (cost=1,056.98..1,724.37 rows=226 width=56) (actual time=7.196..50.630 rows=14,743 loops=1)

28. 1.585 30.525 ↓ 65.2 14,743 1

Nested Loop Left Join (cost=1,056.56..1,387.87 rows=226 width=39) (actual time=7.188..30.525 rows=14,743 loops=1)

29. 1.542 14.197 ↓ 65.2 14,743 1

Nested Loop Left Join (cost=1,056.27..1,296.56 rows=226 width=28) (actual time=7.183..14.197 rows=14,743 loops=1)

30. 0.219 8.095 ↓ 8.8 380 1

Nested Loop Semi Join (cost=1,055.99..1,197.14 rows=43 width=19) (actual time=7.180..8.095 rows=380 loops=1)

31. 0.109 7.496 ↓ 1.9 380 1

Hash Join (cost=1,055.70..1,131.31 rows=198 width=15) (actual time=7.173..7.496 rows=380 loops=1)

  • Hash Cond: (cacategory0_.cac_id = cacategory7_.id)
32. 0.222 0.222 ↑ 1.0 395 1

Seq Scan on ca_category cacategory0_ (cost=0.00..72.38 rows=395 width=11) (actual time=0.005..0.222 rows=395 loops=1)

  • Filter: (cac_cocam_id = 5363)
  • Rows Removed by Filter: 2355
33. 0.052 7.165 ↓ 3.0 607 1

Hash (cost=1,053.20..1,053.20 rows=200 width=4) (actual time=7.165..7.165 rows=607 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 30kB
34. 0.136 7.113 ↓ 3.0 607 1

HashAggregate (cost=1,051.20..1,053.20 rows=200 width=4) (actual time=7.054..7.113 rows=607 loops=1)

  • Group Key: cacategory7_.id
35. 6.977 6.977 ↑ 77.0 607 1

CTE Scan on cacategorytreecte cacategory7_ (cost=0.00..934.40 rows=46,720 width=4) (actual time=2.536..6.977 rows=607 loops=1)

36. 0.380 0.380 ↑ 24.0 1 380

Index Only Scan using pk_ca_supplier_category on ca_supplier_category suppliers6_ (cost=0.29..1.15 rows=24 width=4) (actual time=0.001..0.001 rows=1 loops=380)

  • Index Cond: (casc_cac_id = cacategory0_.cac_id)
  • Heap Fetches: 3
37. 4.560 4.560 ↓ 1.6 39 380

Index Scan using pk_ca_supplier_category on ca_supplier_category suppliers1_ (cost=0.29..2.07 rows=24 width=9) (actual time=0.001..0.012 rows=39 loops=380)

  • Index Cond: (cacategory0_.cac_id = casc_cac_id)
38. 14.743 14.743 ↑ 1.0 1 14,743

Index Scan using pk_ca_supplier on ca_supplier casupplier2_ (cost=0.29..0.40 rows=1 width=15) (actual time=0.001..0.001 rows=1 loops=14,743)

  • Index Cond: (suppliers1_.casc_cas_id = cas_id)
39. 14.743 14.743 ↑ 1.0 1 14,743

Index Scan using pk_party on party party3_ (cost=0.42..1.49 rows=1 width=25) (actual time=0.001..0.001 rows=1 loops=14,743)

  • Index Cond: (casupplier2_.cas_p_id = p_id)
40. 32.200 32.200 ↑ 1.0 1 32,200

Index Only Scan using idx_caasv_currency_filter_actual_spend on ca_aggregated_spend_value spendvalue5_ (cost=0.42..1.64 rows=1 width=11) (actual time=0.001..0.001 rows=1 loops=32,200)

  • Index Cond: ((caasv_caas_id = caaggregat4_.caas_id) AND (caasv_cur = 'EUR'::text))
  • Heap Fetches: 21354
Planning time : 1.693 ms
Execution time : 123.614 ms