explain.depesz.com

PostgreSQL's explain analyze made readable

Result: FyTDH

Settings
# exclusive inclusive rows x rows loops node
1. 0.051 9,331.469 ↑ 1.3 3 1

Hash Full Join (cost=423,804.37..424,489.50 rows=4 width=880) (actual time=9,331.454..9,331.469 rows=3 loops=1)

  • Hash Cond: ((('DELTA/NONDELTA'::text) = (o.levels)::text) AND (((((dpc_details.delta_type)::text || ' | '::text) || (dpc_details.agency_name)::text)) = o.geography))
2.          

CTE procurement_qty

3. 88.590 6,505.994 ↑ 4.2 51,946 1

Unique (cost=305,143.14..309,462.56 rows=215,971 width=200) (actual time=6,369.702..6,505.994 rows=51,946 loops=1)

4. 143.710 6,417.404 ↑ 4.2 51,946 1

Sort (cost=305,143.14..305,683.07 rows=215,971 width=200) (actual time=6,369.698..6,417.404 rows=51,946 loops=1)

  • Sort Key: "*SELECT* 1_4".levels, "*SELECT* 1_4".geography, "*SELECT* 1_4".purchase_date, "*SELECT* 1_4".product_name, "*SELECT* 1_4".quantity, "*SELECT* 1_4".no_of_dpc, ('P'::text)
  • Sort Method: external merge Disk: 3640kB
5. 71.935 6,273.694 ↑ 4.2 51,946 1

Append (cost=222,858.33..265,336.60 rows=215,971 width=200) (actual time=4,694.689..6,273.694 rows=51,946 loops=1)

6. 79.563 6,178.504 ↑ 4.2 51,946 1

Subquery Scan on *SELECT* 1_4 (cost=222,858.33..261,994.98 rows=215,970 width=152) (actual time=4,694.687..6,178.504 rows=51,946 loops=1)

7. 218.465 6,098.941 ↑ 4.2 51,946 1

Finalize GroupAggregate (cost=222,858.33..259,835.28 rows=215,970 width=167) (actual time=4,694.684..6,098.941 rows=51,946 loops=1)

  • Group Key: agg_procurement_quantity.levels, agg_procurement_quantity.geography, agg_procurement_quantity.agency_name, agg_procurement_quantity.purchase_date, agg_procurement_quantity.product_name
8. 476.517 5,880.476 ↑ 2.3 92,782 1

Gather Merge (cost=222,858.33..250,788.22 rows=210,118 width=103) (actual time=4,694.661..5,880.476 rows=92,782 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
9. 372.664 5,403.959 ↑ 3.4 30,927 3 / 3

Partial GroupAggregate (cost=221,858.30..225,535.37 rows=105,059 width=103) (actual time=4,673.865..5,403.959 rows=30,927 loops=3)

  • Group Key: agg_procurement_quantity.levels, agg_procurement_quantity.geography, agg_procurement_quantity.agency_name, agg_procurement_quantity.purchase_date, agg_procurement_quantity.product_name
10. 1,047.882 5,031.295 ↓ 1.7 179,950 3 / 3

Sort (cost=221,858.30..222,120.95 rows=105,059 width=52) (actual time=4,673.836..5,031.295 rows=179,950 loops=3)

  • Sort Key: agg_procurement_quantity.levels, agg_procurement_quantity.geography, agg_procurement_quantity.agency_name, agg_procurement_quantity.purchase_date, agg_procurement_quantity.product_name
  • Sort Method: external merge Disk: 12432kB
  • Worker 0: Sort Method: external merge Disk: 12728kB
  • Worker 1: Sort Method: external merge Disk: 11464kB
11. 3,983.413 3,983.413 ↓ 1.7 179,950 3 / 3

Parallel Seq Scan on agg_procurement_quantity (cost=0.00..209,504.94 rows=105,059 width=52) (actual time=2,704.704..3,983.413 rows=179,950 loops=3)

  • Filter: (((flag)::text = 'S'::text) AND (season_master_id = '5'::bigint) AND ((state)::text = 'TAMIL NADU'::text))
  • Rows Removed by Filter: 2224690
12. 0.002 23.255 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2_4 (cost=2,261.72..2,261.77 rows=1 width=148) (actual time=23.254..23.255 rows=0 loops=1)

13. 0.004 23.253 ↓ 0.0 0 1

GroupAggregate (cost=2,261.72..2,261.75 rows=1 width=136) (actual time=23.252..23.253 rows=0 loops=1)

  • Group Key: agg_movement.levels, agg_movement.geography, agg_movement.agency_name, agg_movement.transport_date, agg_movement.product_name
14. 0.021 23.249 ↓ 0.0 0 1

Sort (cost=2,261.72..2,261.72 rows=1 width=42) (actual time=23.248..23.249 rows=0 loops=1)

  • Sort Key: agg_movement.levels, agg_movement.geography, agg_movement.agency_name, agg_movement.transport_date, agg_movement.product_name
  • Sort Method: quicksort Memory: 25kB
15. 23.228 23.228 ↓ 0.0 0 1

Seq Scan on agg_movement (cost=0.00..2,261.70 rows=1 width=42) (actual time=23.227..23.228 rows=0 loops=1)

  • Filter: (((flag)::text = 'S'::text) AND (season_master_id = '4'::bigint))
  • Rows Removed by Filter: 66678
16. 1.570 15.804 ↑ 1.3 3 1

HashAggregate (cost=386.45..386.51 rows=4 width=84) (actual time=15.801..15.804 rows=3 loops=1)

  • Group Key: dpc_details.agency_name, dpc_details.delta_type
17. 8.274 14.234 ↓ 1.4 1,639 1

Seq Scan on dpc_details (cost=206.19..377.42 rows=1,204 width=20) (actual time=9.419..14.234 rows=1,639 loops=1)

  • Filter: ((status = 1) AND ((hashed SubPlan 3) OR ((dpc_type)::text = 'Manual POS'::text)))
  • Rows Removed by Filter: 1008
18.          

SubPlan (for Seq Scan)

19. 5.960 5.960 ↓ 1.0 1,634 1

Seq Scan on device_dpc_mapping (cost=0.00..202.15 rows=1,615 width=8) (actual time=0.434..5.960 rows=1,634 loops=1)

  • Filter: (status = 1)
  • Rows Removed by Filter: 4001
20. 0.011 9,315.614 ↓ 3.0 3 1

Hash (cost=113,955.34..113,955.34 rows=1 width=840) (actual time=9,315.613..9,315.614 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
21. 0.019 9,315.603 ↓ 3.0 3 1

Merge Join (cost=113,950.86..113,955.34 rows=1 width=840) (actual time=9,315.538..9,315.603 rows=3 loops=1)

  • Merge Cond: (((o.levels)::text = m.mc_levels) AND (o.geography = m.mc_geography))
22. 0.082 7,172.037 ↑ 5.0 4 1

GroupAggregate (cost=29,160.65..29,164.40 rows=20 width=744) (actual time=7,171.991..7,172.037 rows=4 loops=1)

  • Group Key: o.levels, o.geography
23. 9.882 7,171.955 ↑ 4.0 5 1

Sort (cost=29,160.65..29,160.70 rows=20 width=580) (actual time=7,171.950..7,171.955 rows=5 loops=1)

  • Sort Key: o.levels, o.geography
  • Sort Method: quicksort Memory: 557kB
24. 4.300 7,162.073 ↓ 160.6 3,212 1

Subquery Scan on o (cost=29,159.82..29,160.22 rows=20 width=580) (actual time=7,154.356..7,162.073 rows=3,212 loops=1)

25. 8.823 7,157.773 ↓ 160.6 3,212 1

HashAggregate (cost=29,159.82..29,160.02 rows=20 width=580) (actual time=7,154.352..7,157.773 rows=3,212 loops=1)

  • Group Key: "*SELECT* 1".levels, "*SELECT* 1".geography, (0), "*SELECT* 1".current_paddy_a, "*SELECT* 1".current_paddy_c, "*SELECT* 1".paddy_current_total, "*SELECT* 1".monthpaddya, "*SELECT* 1".monthpaddyc, "*SELECT* 1".paddy_month_total, ('0'::numeric), ('0'::numeric), ((0)::numeric), ((0)::numeric), ((0)::numeric), ((0)::numeric), ((0)::numeric), ((0)::numeric), ((0)::numeric), ((0)::numeric)
26. 4.235 7,148.950 ↓ 160.6 3,212 1

Append (cost=14,578.56..29,158.87 rows=20 width=580) (actual time=6,998.792..7,148.950 rows=3,212 loops=1)

27. 8.194 7,127.789 ↓ 321.2 3,212 1

Subquery Scan on *SELECT* 1 (cost=14,578.56..14,579.38 rows=10 width=580) (actual time=6,998.790..7,127.789 rows=3,212 loops=1)

28. 75.299 7,119.595 ↓ 321.2 3,212 1

GroupAggregate (cost=14,578.56..14,579.08 rows=10 width=356) (actual time=6,998.785..7,119.595 rows=3,212 loops=1)

  • Group Key: p.levels, p.geography
29. 140.757 7,044.296 ↓ 5,262.6 52,626 1

Sort (cost=14,578.56..14,578.58 rows=10 width=192) (actual time=6,998.299..7,044.296 rows=52,626 loops=1)

  • Sort Key: p.levels, p.geography
  • Sort Method: external merge Disk: 2800kB
30. 72.260 6,903.539 ↓ 5,262.6 52,626 1

Subquery Scan on p (cost=0.00..14,578.39 rows=10 width=192) (actual time=6,370.849..6,903.539 rows=52,626 loops=1)

31. 68.003 6,831.279 ↓ 5,262.6 52,626 1

Append (cost=0.00..14,578.29 rows=10 width=224) (actual time=6,370.846..6,831.279 rows=52,626 loops=1)

32. 1.385 6,598.517 ↓ 136.0 680 1

Subquery Scan on *SELECT* 1_1 (cost=0.00..6,479.23 rows=5 width=224) (actual time=6,370.844..6,598.517 rows=680 loops=1)

33. 6,597.132 6,597.132 ↓ 136.0 680 1

CTE Scan on procurement_qty (cost=0.00..6,479.15 rows=5 width=168) (actual time=6,370.840..6,597.132 rows=680 loops=1)

  • Filter: ((pmflag = 'P'::text) AND (date_trunc('day'::text, purchase_date) = to_date('2020-02-04'::text, 'YYYY-MM-DD'::text)))
  • Rows Removed by Filter: 51266
34. 75.435 164.759 ↓ 10,389.2 51,946 1

Subquery Scan on *SELECT* 2_1 (cost=0.00..8,099.01 rows=5 width=224) (actual time=0.024..164.759 rows=51,946 loops=1)

35. 89.324 89.324 ↓ 10,389.2 51,946 1

CTE Scan on procurement_qty procurement_qty_1 (cost=0.00..8,098.94 rows=5 width=168) (actual time=0.020..89.324 rows=51,946 loops=1)

  • Filter: ((pmflag = 'P'::text) AND ((purchase_date)::date >= to_date('2019-10-01'::text, 'YYYY-MM-DD'::text)) AND ((purchase_date)::date <= to_date('2020-02-04'::text, 'YYYY-MM-DD'::text)))
36. 0.003 16.926 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=14,578.56..14,579.38 rows=10 width=580) (actual time=16.925..16.926 rows=0 loops=1)

37. 0.003 16.923 ↓ 0.0 0 1

GroupAggregate (cost=14,578.56..14,579.08 rows=10 width=356) (actual time=16.923..16.923 rows=0 loops=1)

  • Group Key: "*SELECT* 1_2".levels, "*SELECT* 1_2".geography
38. 0.014 16.920 ↓ 0.0 0 1

Sort (cost=14,578.56..14,578.58 rows=10 width=192) (actual time=16.920..16.920 rows=0 loops=1)

  • Sort Key: "*SELECT* 1_2".levels, "*SELECT* 1_2".geography
  • Sort Method: quicksort Memory: 25kB
39. 0.003 16.906 ↓ 0.0 0 1

Append (cost=0.00..14,578.29 rows=10 width=192) (actual time=16.905..16.906 rows=0 loops=1)

40. 0.002 8.846 ↓ 0.0 0 1

Subquery Scan on *SELECT* 1_2 (cost=0.00..6,479.23 rows=5 width=192) (actual time=8.846..8.846 rows=0 loops=1)

41. 8.844 8.844 ↓ 0.0 0 1

CTE Scan on procurement_qty procurement_qty_2 (cost=0.00..6,479.15 rows=5 width=136) (actual time=8.843..8.844 rows=0 loops=1)

  • Filter: ((pmflag = 'M'::text) AND ((purchase_date)::date = to_date('2020-02-04'::text, 'YYYY-MM-DD'::text)))
  • Rows Removed by Filter: 51946
42. 0.003 8.057 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2_2 (cost=0.00..8,099.01 rows=5 width=192) (actual time=8.056..8.057 rows=0 loops=1)

43. 8.054 8.054 ↓ 0.0 0 1

CTE Scan on procurement_qty procurement_qty_3 (cost=0.00..8,098.94 rows=5 width=136) (actual time=8.054..8.054 rows=0 loops=1)

  • Filter: ((pmflag = 'M'::text) AND ((purchase_date)::date >= to_date('2019-10-01'::text, 'YYYY-MM-DD'::text)) AND ((purchase_date)::date <= to_date('2020-02-04'::text, 'YYYY-MM-DD'::text)))
  • Rows Removed by Filter: 51946
44. 0.019 2,143.547 ↑ 2.7 3 1

GroupAggregate (cost=84,790.21..84,790.51 rows=8 width=192) (actual time=2,143.538..2,143.547 rows=3 loops=1)

  • Group Key: m.mc_levels, m.mc_geography
45. 0.028 2,143.528 ↑ 1.6 5 1

Sort (cost=84,790.21..84,790.23 rows=8 width=96) (actual time=2,143.524..2,143.528 rows=5 loops=1)

  • Sort Key: m.mc_levels, m.mc_geography
  • Sort Method: quicksort Memory: 25kB
46. 0.010 2,143.500 ↑ 1.6 5 1

Subquery Scan on m (cost=84,789.93..84,790.09 rows=8 width=96) (actual time=2,143.488..2,143.500 rows=5 loops=1)

47. 0.031 2,143.490 ↑ 1.6 5 1

HashAggregate (cost=84,789.93..84,790.01 rows=8 width=96) (actual time=2,143.485..2,143.490 rows=5 loops=1)

  • Group Key: ('DELTA/NONDELTA'::text), "*SELECT* 1_3".mc_geography, "*SELECT* 1_3".farmer_count, "*SELECT* 1_3".dpc_count, ((0)::bigint), ((0)::bigint)
48. 0.015 2,143.459 ↑ 1.6 5 1

Append (cost=39,622.06..84,789.81 rows=8 width=96) (actual time=1,357.152..2,143.459 rows=5 loops=1)

49. 0.013 1,811.589 ↑ 1.3 3 1

Subquery Scan on *SELECT* 1_3 (cost=39,622.06..39,659.88 rows=4 width=96) (actual time=1,357.149..1,811.589 rows=3 loops=1)

50. 391.304 1,811.576 ↑ 1.3 3 1

GroupAggregate (cost=39,622.06..39,659.82 rows=4 width=99) (actual time=1,357.145..1,811.576 rows=3 loops=1)

  • Group Key: agg_procurement_details.agency_name, agg_procurement_details.delta_type
51. 219.489 1,420.272 ↓ 25.5 76,844 1

Sort (cost=39,622.06..39,629.60 rows=3,016 width=38) (actual time=1,346.542..1,420.272 rows=76,844 loops=1)

  • Sort Key: agg_procurement_details.agency_name, agg_procurement_details.delta_type
  • Sort Method: external merge Disk: 3752kB
52. 82.181 1,200.783 ↓ 25.5 76,844 1

Gather (cost=1,000.00..39,447.76 rows=3,016 width=38) (actual time=3.489..1,200.783 rows=76,844 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
53. 1,118.602 1,118.602 ↓ 20.4 25,615 3 / 3

Parallel Seq Scan on agg_procurement_details (cost=0.00..38,146.16 rows=1,257 width=38) (actual time=28.658..1,118.602 rows=25,615 loops=3)

  • Filter: ((order_type = 1) AND ((purchase_date)::date >= to_date('2019-10-01'::text, 'YYYY-MM-DD'::text)) AND ((purchase_date)::date <= to_date('2020-02-04'::text, 'YYYY-MM-DD'::text)))
  • Rows Removed by Filter: 178809
54. 0.006 331.855 ↑ 2.0 2 1

Subquery Scan on *SELECT* 2_3 (cost=45,092.08..45,129.90 rows=4 width=96) (actual time=331.763..331.855 rows=2 loops=1)

55. 1.277 331.849 ↑ 2.0 2 1

GroupAggregate (cost=45,092.08..45,129.84 rows=4 width=99) (actual time=331.760..331.849 rows=2 loops=1)

  • Group Key: agg_procurement_details_1.agency_name, agg_procurement_details_1.delta_type
56. 0.632 330.572 ↑ 7.4 410 1

Sort (cost=45,092.08..45,099.62 rows=3,016 width=38) (actual time=330.311..330.572 rows=410 loops=1)

  • Sort Key: agg_procurement_details_1.agency_name, agg_procurement_details_1.delta_type
  • Sort Method: quicksort Memory: 57kB
57. 329.940 329.940 ↑ 7.4 410 1

Seq Scan on agg_procurement_details agg_procurement_details_1 (cost=0.00..44,917.78 rows=3,016 width=38) (actual time=82.183..329.940 rows=410 loops=1)

  • Filter: ((order_type = 1) AND ((purchase_date)::date = (now())::date))
  • Rows Removed by Filter: 612861
58.          

SubPlan (for Hash Full Join)

59. 0.000 0.000 ↓ 0.0 0

Seq Scan on dpc_details dpc_details_1 (cost=0.00..171.24 rows=13 width=12) (never executed)

  • Filter: ((((dpc_code)::text || ' | '::text) || (agency_name)::text) = COALESCE(o.geography, ((((dpc_details.delta_type)::text || ' | '::text) || (dpc_details.agency_name)::text))))
Planning time : 20.234 ms
Execution time : 9,339.293 ms