explain.depesz.com

PostgreSQL's explain analyze made readable

Result: VlxK

Settings
# exclusive inclusive rows x rows loops node
1. 0.016 11,910.735 ↓ 4.5 18 1

Sort (cost=1,901,922.21..1,901,922.22 rows=4 width=116) (actual time=11,910.734..11,910.735 rows=18 loops=1)

  • Sort Key: zz.z___pivot_row_rank
  • Sort Method: quicksort Memory: 27kB
2. 0.004 11,910.719 ↓ 4.5 18 1

Subquery Scan on zz (cost=1,901,921.82..1,901,922.17 rows=4 width=116) (actual time=11,910.704..11,910.719 rows=18 loops=1)

  • Filter: ((zz.z___pivot_row_rank <= 500) OR (zz.z__pivot_col_ordering = 1))
3. 0.018 11,910.715 ↓ 1.8 18 1

WindowAgg (cost=1,901,921.82..1,901,922.02 rows=10 width=100) (actual time=11,910.700..11,910.715 rows=18 loops=1)

4. 0.012 11,910.697 ↓ 1.8 18 1

Sort (cost=1,901,921.82..1,901,921.84 rows=10 width=100) (actual time=11,910.696..11,910.697 rows=18 loops=1)

  • Sort Key: xx.z__pivot_col_rank, xx.z___min_rank
  • Sort Method: quicksort Memory: 27kB
5. 0.010 11,910.685 ↓ 1.8 18 1

WindowAgg (cost=1,901,921.48..1,901,921.65 rows=10 width=100) (actual time=11,910.678..11,910.685 rows=18 loops=1)

6. 0.009 11,910.675 ↓ 1.8 18 1

Sort (cost=1,901,921.48..1,901,921.50 rows=10 width=100) (actual time=11,910.675..11,910.675 rows=18 loops=1)

  • Sort Key: xx.z___min_rank
  • Sort Method: quicksort Memory: 27kB
7. 0.000 11,910.666 ↓ 1.8 18 1

Subquery Scan on xx (cost=1,901,921.04..1,901,921.31 rows=10 width=100) (actual time=11,910.656..11,910.666 rows=18 loops=1)

8. 0.012 11,910.666 ↓ 1.8 18 1

WindowAgg (cost=1,901,921.04..1,901,921.21 rows=10 width=92) (actual time=11,910.656..11,910.666 rows=18 loops=1)

9. 0.021 11,910.654 ↓ 1.8 18 1

Sort (cost=1,901,921.04..1,901,921.06 rows=10 width=92) (actual time=11,910.652..11,910.654 rows=18 loops=1)

  • Sort Key: aa."acquirer_response.date_month"
  • Sort Method: quicksort Memory: 26kB
10. 0.003 11,910.633 ↓ 1.8 18 1

Subquery Scan on aa (cost=1,901,920.44..1,901,920.87 rows=10 width=92) (actual time=11,910.623..11,910.633 rows=18 loops=1)

11. 0.011 11,910.630 ↓ 1.8 18 1

WindowAgg (cost=1,901,920.44..1,901,920.77 rows=10 width=84) (actual time=11,910.622..11,910.630 rows=18 loops=1)

12. 0.017 11,910.619 ↓ 1.8 18 1

Sort (cost=1,901,920.44..1,901,920.47 rows=10 width=84) (actual time=11,910.618..11,910.619 rows=18 loops=1)

  • Sort Key: (CASE WHEN (bb.z__pivot_col_rank = 1) THEN CASE WHEN (bb."acquirer_response.count" IS NOT NULL) THEN 0 ELSE 1 END ELSE 2 END), (CASE WHEN (bb.z__pivot_col_rank = 1) THEN bb."acquirer_response.count" ELSE NULL::bigint END), bb."acquirer_response.count", bb.z__pivot_col_rank, bb."acquirer_response.date_month"
  • Sort Method: quicksort Memory: 26kB
13. 0.013 11,910.602 ↓ 1.8 18 1

Subquery Scan on bb (cost=1,901,919.19..1,901,920.28 rows=10 width=84) (actual time=11,910.586..11,910.602 rows=18 loops=1)

  • Filter: (bb.z__pivot_col_rank <= 16384)
14. 0.019 11,910.589 ↑ 1.6 18 1

WindowAgg (cost=1,901,919.19..1,901,919.92 rows=29 width=76) (actual time=11,910.580..11,910.589 rows=18 loops=1)

15. 0.015 11,910.570 ↑ 1.6 18 1

Sort (cost=1,901,919.19..1,901,919.26 rows=29 width=76) (actual time=11,910.569..11,910.570 rows=18 loops=1)

  • Sort Key: (CASE WHEN (ww."acquirer_response.result" IS NULL) THEN 1 ELSE 0 END), ww."acquirer_response.result", (CASE WHEN (ww."acquirer_response.resultCode" IS NULL) THEN 1 ELSE 0 END), ww."acquirer_response.resultCode"
  • Sort Method: quicksort Memory: 26kB
16. 0.006 11,910.555 ↑ 1.6 18 1

Subquery Scan on ww (cost=1,901,917.25..1,901,918.49 rows=29 width=76) (actual time=11,910.540..11,910.555 rows=18 loops=1)

17. 2,944.422 11,910.549 ↑ 1.6 18 1

HashAggregate (cost=1,901,917.25..1,901,918.20 rows=29 width=12) (actual time=11,910.538..11,910.549 rows=18 loops=1)

  • Group Key: acquirer_response.result, CASE WHEN (acquirer_response.result = 0) THEN 'Unknown'::text WHEN (acquirer_response.result = 1) THEN 'Approved'::text WHEN (acquirer_response.result = 2) THEN 'Refused'::text WHEN (acquirer_response.result = 4) THEN 'Error'::text ELSE NULL::text END, date_trunc('month'::text, (acquirer_response.date)::timestamp with time zone)
18.          

Initplan (forHashAggregate)

19. 0.022 0.022 ↑ 1.0 1 1

Result (cost=0.00..0.03 rows=1 width=0) (actual time=0.022..0.022 rows=1 loops=1)

20. 0.006 0.006 ↑ 1.0 1 1

Result (cost=0.00..0.03 rows=1 width=0) (actual time=0.006..0.006 rows=1 loops=1)

21. 7,358.325 8,966.099 ↓ 43.1 12,239,763 1

Bitmap Heap Scan on paymentaggregationdaily acquirer_response (cost=1,348,439.20..1,899,078.23 rows=283,896 width=12) (actual time=1,609.688..8,966.099 rows=12,239,763 loops=1)

  • Recheck Cond: ((date >= $0) AND (date < $1))
  • Rows Removed by Index Recheck: 95
  • Filter: ((CASE WHEN (result = 0) THEN 'Unknown'::text WHEN (result = 1) THEN 'Approved'::text WHEN (result = 2) THEN 'Refused'::text WHEN (result = 4) THEN 'Error'::text ELSE NULL::text END <> 'Unknown'::text) OR (CASE WHEN (result = 0) THEN 'Unknown'::text WHEN (result = 1) THEN 'Approved'::text WHEN (result = 2) THEN 'Refused'::text WHEN (result = 4) THEN 'Error'::text ELSE NULL::text END IS NULL))
  • Rows Removed by Filter: 141411
  • Heap Blocks: exact=13446 lossy=145162
22. 1,607.774 1,607.774 ↓ 43.4 12,381,174 1

Bitmap Index Scan on pad_txvariantid_date_idx (cost=0.00..1,348,368.23 rows=285,316 width=0) (actual time=1,607.774..1,607.774 rows=12,381,174 loops=1)

  • Index Cond: ((date >= $0) AND (date < $1))
Planning time : 0.858 ms
Execution time : 11,911.102 ms