explain.depesz.com

PostgreSQL's explain analyze made readable

Result: QP8X

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=10,972,862.55..62,144,404.50 rows=786,647 width=37) (actual rows= loops=)

  • Group Key: calculation.guid
  • Filter: (max((count(DISTINCT category.category2_guid))) <> count(DISTINCT service.category_guid))
2. 0.000 0.000 ↓ 0.0

Gather Merge (cost=10,972,862.55..61,548,508.32 rows=78,135,157 width=82) (actual rows= loops=)

  • Workers Planned: 2
3. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=10,971,862.52..52,528,772.45 rows=32,556,315 width=82) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

Merge Join (cost=10,971,861.96..11,414,563.10 rows=1,990,927 width=86) (actual rows= loops=)

  • Merge Cond: ((calculation.guid)::text = (line2.calculation_guid)::text)
5. 0.000 0.000 ↓ 0.0

Sort (cost=1,109,969.75..1,111,457.79 rows=595,217 width=77) (actual rows= loops=)

  • Sort Key: calculation.guid
6. 0.000 0.000 ↓ 0.0

Merge Join (cost=942,506.86..1,037,765.34 rows=595,217 width=77) (actual rows= loops=)

  • Merge Cond: ((calculation.compensation_guid)::text = (category.compensation_guid)::text)
7. 0.000 0.000 ↓ 0.0

Sort (cost=501,307.12..502,130.66 rows=329,417 width=74) (actual rows= loops=)

  • Sort Key: calculation.compensation_guid
8. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on msp_compensation_calculation calculation (cost=0.00..462,750.78 rows=329,417 width=74) (actual rows= loops=)

  • Filter: ((period_from <= '2018-12-31'::date) AND (period_to >= '2018-01-01'::date) AND ((status)::text = 'PUBLISHED'::text))
9. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=441,199.75..498,073.31 rows=2,462,852 width=77) (actual rows= loops=)

  • Group Key: category.compensation_guid
10. 0.000 0.000 ↓ 0.0

Sort (cost=441,199.75..447,721.73 rows=2,608,791 width=74) (actual rows= loops=)

  • Sort Key: category.compensation_guid
11. 0.000 0.000 ↓ 0.0

Seq Scan on tmp_category_comp_2018 category (cost=0.00..96,928.50 rows=2,608,791 width=74) (actual rows= loops=)

  • Filter: ((category_compensation_type)::text = 'ACTUAL'::text)
12. 0.000 0.000 ↓ 0.0

Materialize (cost=9,861,892.16..10,141,769.43 rows=55,975,454 width=46) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

Sort (cost=9,861,892.16..10,001,830.79 rows=55,975,454 width=46) (actual rows= loops=)

  • Sort Key: line2.calculation_guid
14. 0.000 0.000 ↓ 0.0

Seq Scan on msp_compensation_calculation_line2 line2 (cost=0.00..1,674,385.54 rows=55,975,454 width=46) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

Index Only Scan using pk_msp_compensation_category_services2 on msp_compensation_category_services2 service (cost=0.56..20.55 rows=10 width=46) (actual rows= loops=)

  • Index Cond: ((category_guid = ANY (((array_agg(DISTINCT category.category2_guid)))::text[])) AND (service_type = (line2.service_type)::text))