#	exclusive	inclusive	rows x	rows	loops	node
1.	0.024	0.322	↓ 0.0	0	1	→ Sort (cost=16,703.43..16,703.43 rows=1 width=84) (actual time=0.322..0.322 rows=0 loops=1) Sort Key: cacategory0_.cac_code Sort Method: quicksort Memory: 25kB
2.						CTE cacategorytreecte
3.	0.000	0.000	↓ 0.0		0	→ Recursive Union (cost=328.24..7,354.16 rows=25,692 width=11) (never executed)
4.	0.000	0.000	↓ 0.0		0	→ Hash Join (cost=328.24..396.31 rows=392 width=11) (never executed) Hash Cond: (cacategory0__1.cac_id = suppliers1__1.casc_cac_id)
5.	0.000	0.000	↓ 0.0		0	→ Seq Scan on ca_category cacategory0__1 (cost=0.00..57.94 rows=2,194 width=11) (never executed) Filter: (cac_id IS NOT NULL)
6.	0.000	0.000	↓ 0.0		0	→ Hash (cost=323.34..323.34 rows=392 width=4) (never executed)
7.	0.000	0.000	↓ 0.0		0	→ HashAggregate (cost=319.42..323.34 rows=392 width=4) (never executed) Group Key: suppliers1__1.casc_cac_id
8.	0.000	0.000	↓ 0.0		0	→ Seq Scan on ca_supplier_category suppliers1__1 (cost=0.00..276.54 rows=17,154 width=4) (never executed)
9.	0.000	0.000	↓ 0.0		0	→ Hash Join (cost=491.71..644.40 rows=2,530 width=11) (never executed) Hash Cond: (cacategory1_.id = cacategory0__2.cac_parent_id)
10.	0.000	0.000	↓ 0.0		0	→ WorkTable Scan on cacategorytreecte cacategory1_ (cost=0.00..78.40 rows=3,920 width=4) (never executed)
11.	0.000	0.000	↓ 0.0		0	→ Hash (cost=487.82..487.82 rows=311 width=15) (never executed)
12.	0.000	0.000	↓ 0.0		0	→ Hash Anti Join (cost=413.61..487.82 rows=311 width=15) (never executed) Hash Cond: (cacategory0__2.cac_id = children3_.cac_parent_id)
13.	0.000	0.000	↓ 0.0		0	→ Hash Join (cost=328.24..396.31 rows=392 width=15) (never executed) Hash Cond: (cacategory0__2.cac_id = suppliers2_.casc_cac_id)
14.	0.000	0.000	↓ 0.0		0	→ Seq Scan on ca_category cacategory0__2 (cost=0.00..57.94 rows=2,194 width=15) (never executed)
15.	0.000	0.000	↓ 0.0		0	→ Hash (cost=323.34..323.34 rows=392 width=4) (never executed)
16.	0.000	0.000	↓ 0.0		0	→ HashAggregate (cost=319.42..323.34 rows=392 width=4) (never executed) Group Key: suppliers2_.casc_cac_id
17.	0.000	0.000	↓ 0.0		0	→ Seq Scan on ca_supplier_category suppliers2_ (cost=0.00..276.54 rows=17,154 width=4) (never executed)
18.	0.000	0.000	↓ 0.0		0	→ Hash (cost=57.94..57.94 rows=2,194 width=4) (never executed)
19.	0.000	0.000	↓ 0.0		0	→ Seq Scan on ca_category children3_ (cost=0.00..57.94 rows=2,194 width=4) (never executed)
20.	0.001	0.298	↓ 0.0	0	1	→ Nested Loop (cost=583.70..9,349.26 rows=1 width=84) (actual time=0.298..0.298 rows=0 loops=1) Join Filter: (cacategory0_.cac_id = cacategory7_.id)
21.	0.000	0.297	↓ 0.0	0	1	→ Nested Loop Left Join (cost=5.63..159.85 rows=1 width=56) (actual time=0.297..0.297 rows=0 loops=1)
22.	0.000	0.297	↓ 0.0	0	1	→ Nested Loop Left Join (cost=5.20..152.39 rows=1 width=35) (actual time=0.297..0.297 rows=0 loops=1)
23.	0.000	0.297	↓ 0.0	0	1	→ Nested Loop Left Join (cost=4.92..152.03 rows=1 width=24) (actual time=0.297..0.297 rows=0 loops=1)
24.	0.000	0.297	↓ 0.0	0	1	→ Nested Loop Semi Join (cost=0.29..64.30 rows=1 width=15) (actual time=0.297..0.297 rows=0 loops=1)
25.	0.297	0.297	↓ 0.0	0	1	→ Seq Scan on ca_category cacategory0_ (cost=0.00..63.42 rows=1 width=11) (actual time=0.297..0.297 rows=0 loops=1) Filter: (cac_cocam_id = 5,363) Rows Removed by Filter: 2,200
26.	0.000	0.000	↓ 0.0		0	→ Index Only Scan using pk_ca_supplier_category on ca_supplier_category suppliers6_ (cost=0.29..9.06 rows=44 width=4) (never executed) Index Cond: (casc_cac_id = cacategory0_.cac_id) Heap Fetches: 0
27.	0.000	0.000	↓ 0.0		0	→ Bitmap Heap Scan on ca_supplier_category suppliers1_ (cost=4.63..87.29 rows=44 width=9) (never executed) Recheck Cond: (cacategory0_.cac_id = casc_cac_id)
28.	0.000	0.000	↓ 0.0		0	→ Bitmap Index Scan on pk_ca_supplier_category (cost=0.00..4.62 rows=44 width=0) (never executed) Index Cond: (cacategory0_.cac_id = casc_cac_id)
29.	0.000	0.000	↓ 0.0		0	→ Index Scan using pk_ca_supplier on ca_supplier casupplier2_ (cost=0.29..0.36 rows=1 width=15) (never executed) Index Cond: (suppliers1_.casc_cas_id = cas_id)
30.	0.000	0.000	↓ 0.0		0	→ Index Scan using pk_party on party party3_ (cost=0.43..7.46 rows=1 width=29) (never executed) Index Cond: (casupplier2_.cas_p_id = p_id)
31.	0.000	0.000	↓ 0.0		0	→ HashAggregate (cost=578.07..580.07 rows=200 width=4) (never executed) Group Key: cacategory7_.id
32.	0.000	0.000	↓ 0.0		0	→ CTE Scan on cacategorytreecte cacategory7_ (cost=0.00..513.84 rows=25,692 width=4) (never executed)
33.						SubPlan (for Nested Loop)
34.	0.000	0.000	↓ 0.0		0	→ Aggregate (cost=8,606.82..8,606.83 rows=1 width=32) (never executed)
35.	0.000	0.000	↓ 0.0		0	→ Nested Loop (cost=0.42..8,606.44 rows=154 width=7) (never executed)
36.	0.000	0.000	↓ 0.0		0	→ Seq Scan on ca_aggregated_spend caaggregat4_ (cost=0.00..7,911.69 rows=150 width=4) (never executed) Filter: ((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) AND (caas_cac_id = cacategory0_.cac_id))
37.	0.000	0.000	↓ 0.0		0	→ Index Only Scan using idx_caasv_currency_filter_actual_spend on ca_aggregated_spend_value spendvalue5_ (cost=0.42..4.63 rows=1 width=11) (never executed) Index Cond: ((caasv_caas_id = caaggregat4_.caas_id) AND (caasv_cur = 'EUR'::text)) Heap Fetches: 0

Planning time	:	0.773 ms
Execution time	:	0.620 ms

Sort  (cost=16703.43..16703.43 rows=1 width=84) (actual time=0.322..0.322 rows=0 loops=1)
  Sort Key: cacategory0_.cac_code
  Sort Method: quicksort  Memory: 25kB
  CTE cacategorytreecte
    ->  Recursive Union  (cost=328.24..7354.16 rows=25692 width=11) (never executed)
          ->  Hash Join  (cost=328.24..396.31 rows=392 width=11) (never executed)
                Hash Cond: (cacategory0__1.cac_id = suppliers1__1.casc_cac_id)
                ->  Seq Scan on ca_category cacategory0__1  (cost=0.00..57.94 rows=2194 width=11) (never executed)
                      Filter: (cac_id IS NOT NULL)
                ->  Hash  (cost=323.34..323.34 rows=392 width=4) (never executed)
                      ->  HashAggregate  (cost=319.42..323.34 rows=392 width=4) (never executed)
                            Group Key: suppliers1__1.casc_cac_id
                            ->  Seq Scan on ca_supplier_category suppliers1__1  (cost=0.00..276.54 rows=17154 width=4) (never executed)
          ->  Hash Join  (cost=491.71..644.40 rows=2530 width=11) (never executed)
                Hash Cond: (cacategory1_.id = cacategory0__2.cac_parent_id)
                ->  WorkTable Scan on cacategorytreecte cacategory1_  (cost=0.00..78.40 rows=3920 width=4) (never executed)
                ->  Hash  (cost=487.82..487.82 rows=311 width=15) (never executed)
                      ->  Hash Anti Join  (cost=413.61..487.82 rows=311 width=15) (never executed)
                            Hash Cond: (cacategory0__2.cac_id = children3_.cac_parent_id)
                            ->  Hash Join  (cost=328.24..396.31 rows=392 width=15) (never executed)
                                  Hash Cond: (cacategory0__2.cac_id = suppliers2_.casc_cac_id)
                                  ->  Seq Scan on ca_category cacategory0__2  (cost=0.00..57.94 rows=2194 width=15) (never executed)
                                  ->  Hash  (cost=323.34..323.34 rows=392 width=4) (never executed)
                                        ->  HashAggregate  (cost=319.42..323.34 rows=392 width=4) (never executed)
                                              Group Key: suppliers2_.casc_cac_id
                                              ->  Seq Scan on ca_supplier_category suppliers2_  (cost=0.00..276.54 rows=17154 width=4) (never executed)
                            ->  Hash  (cost=57.94..57.94 rows=2194 width=4) (never executed)
                                  ->  Seq Scan on ca_category children3_  (cost=0.00..57.94 rows=2194 width=4) (never executed)
  ->  Nested Loop  (cost=583.70..9349.26 rows=1 width=84) (actual time=0.298..0.298 rows=0 loops=1)
        Join Filter: (cacategory0_.cac_id = cacategory7_.id)
        ->  Nested Loop Left Join  (cost=5.63..159.85 rows=1 width=56) (actual time=0.297..0.297 rows=0 loops=1)
              ->  Nested Loop Left Join  (cost=5.20..152.39 rows=1 width=35) (actual time=0.297..0.297 rows=0 loops=1)
                    ->  Nested Loop Left Join  (cost=4.92..152.03 rows=1 width=24) (actual time=0.297..0.297 rows=0 loops=1)
                          ->  Nested Loop Semi Join  (cost=0.29..64.30 rows=1 width=15) (actual time=0.297..0.297 rows=0 loops=1)
                                ->  Seq Scan on ca_category cacategory0_  (cost=0.00..63.42 rows=1 width=11) (actual time=0.297..0.297 rows=0 loops=1)
                                      Filter: (cac_cocam_id = 5363)
                                      Rows Removed by Filter: 2200
                                ->  Index Only Scan using pk_ca_supplier_category on ca_supplier_category suppliers6_  (cost=0.29..9.06 rows=44 width=4) (never executed)
                                      Index Cond: (casc_cac_id = cacategory0_.cac_id)
                                      Heap Fetches: 0
                          ->  Bitmap Heap Scan on ca_supplier_category suppliers1_  (cost=4.63..87.29 rows=44 width=9) (never executed)
                                Recheck Cond: (cacategory0_.cac_id = casc_cac_id)
                                ->  Bitmap Index Scan on pk_ca_supplier_category  (cost=0.00..4.62 rows=44 width=0) (never executed)
                                      Index Cond: (cacategory0_.cac_id = casc_cac_id)
                    ->  Index Scan using pk_ca_supplier on ca_supplier casupplier2_  (cost=0.29..0.36 rows=1 width=15) (never executed)
                          Index Cond: (suppliers1_.casc_cas_id = cas_id)
              ->  Index Scan using pk_party on party party3_  (cost=0.43..7.46 rows=1 width=29) (never executed)
                    Index Cond: (casupplier2_.cas_p_id = p_id)
        ->  HashAggregate  (cost=578.07..580.07 rows=200 width=4) (never executed)
              Group Key: cacategory7_.id
              ->  CTE Scan on cacategorytreecte cacategory7_  (cost=0.00..513.84 rows=25692 width=4) (never executed)
        SubPlan 2
          ->  Aggregate  (cost=8606.82..8606.83 rows=1 width=32) (never executed)
                ->  Nested Loop  (cost=0.42..8606.44 rows=154 width=7) (never executed)
                      ->  Seq Scan on ca_aggregated_spend caaggregat4_  (cost=0.00..7911.69 rows=150 width=4) (never executed)
                            Filter: ((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) AND (caas_cac_id = cacategory0_.cac_id))
                      ->  Index Only Scan using idx_caasv_currency_filter_actual_spend on ca_aggregated_spend_value spendvalue5_  (cost=0.42..4.63 rows=1 width=11) (never executed)
                            Index Cond: ((caasv_caas_id = caaggregat4_.caas_id) AND (caasv_cur = 'EUR'::text))
                            Heap Fetches: 0
Planning Time: 0.773 ms
Execution Time: 0.620 ms

Per node type stats

node type	count	sum of times	% of query
Aggregate	1	0.000 ms	0.0 %
Bitmap Heap Scan	1	0.000 ms	0.0 %
Bitmap Index Scan	1	0.000 ms	0.0 %
CTE Scan	1	0.000 ms	0.0 %
Hash	4	0.000 ms	0.0 %
Hash Anti Join	1	0.000 ms	0.0 %
Hash Join	3	0.000 ms	0.0 %
HashAggregate	3	0.000 ms	0.0 %
Index Only Scan	2	0.000 ms	0.0 %
Index Scan	2	0.000 ms	0.0 %
Nested Loop	2	0.001 ms	0.3 %
Nested Loop Left Join	3	0.000 ms	0.0 %
Nested Loop Semi Join	1	0.000 ms	0.0 %
Recursive Union	1	0.000 ms	0.0 %
Seq Scan	7	0.297 ms	92.2 %
Sort	1	0.024 ms	7.5 %
WorkTable Scan	1	0.000 ms	0.0 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
ca_aggregated_spend	1	0.000 ms	0.0 %
Seq Scan	1	0.000 ms	0.0 %
ca_aggregated_spend_value	1	0.000 ms	0.0 %
Index Only Scan	1	0.000 ms	0.0 %
ca_category	4	0.297 ms	92.2 %
Seq Scan	4	0.297 ms	100.0 %
ca_supplier	1	0.000 ms	0.0 %
Index Scan	1	0.000 ms	0.0 %
ca_supplier_category	4	0.000 ms	0.0 %
Bitmap Heap Scan	1	0.000 ms	0.0 %
Index Only Scan	1	0.000 ms	0.0 %
Seq Scan	2	0.000 ms	0.0 %
party	1	0.000 ms	0.0 %
Index Scan	1	0.000 ms	0.0 %

explain.depesz.com

PostgreSQL's explain analyze made readable

Result: SYKw

Color mode:

Visible columns:

Per node type stats

Per table stats