explain.depesz.com

PostgreSQL's explain analyze made readable

Result: SOtU : Optimization for: plan #TX9m

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.284 2,215.658 ↓ 18.6 279 1

Sort (cost=1,081,043.21..1,081,043.25 rows=15 width=578) (actual time=2,215.599..2,215.658 rows=279 loops=1)

  • Sort Key: ((comparisons.time_stamp)::date), (sum(comparisons.comparisons)) DESC
  • Sort Method: quicksort Memory: 71kB
2.          

CTE comparisons_last_7d

3. 1.628 1,465.977 ↑ 62,102.7 10 1

Sort (cost=1,005,012.90..1,006,565.47 rows=621,027 width=87) (actual time=1,465.966..1,465.977 rows=10 loops=1)

  • Sort Key: (sum(comparisons_1.comparisons)) DESC
  • Sort Method: quicksort Memory: 579kB
4. 20.890 1,464.349 ↑ 206.7 3,005 1

GroupAggregate (cost=870,294.09..885,819.76 rows=621,027 width=87) (actual time=1,420.262..1,464.349 rows=3,005 loops=1)

  • Group Key: comparisons_1.brand, comparisons_1.plan_name, comparisons_1.plan_name_key, comparisons_1.is_variable
5. 293.561 1,443.459 ↑ 12.6 49,136 1

Sort (cost=870,294.09..871,846.65 rows=621,027 width=83) (actual time=1,420.233..1,443.459 rows=49,136 loops=1)

  • Sort Key: comparisons_1.brand, comparisons_1.plan_name, comparisons_1.plan_name_key, comparisons_1.is_variable
  • Sort Method: external merge Disk: 4,496kB
6. 522.122 1,149.898 ↑ 12.6 49,136 1

Bitmap Heap Scan on comparisons comparisons_1 (cost=86,548.92..751,100.95 rows=621,027 width=83) (actual time=806.725..1,149.898 rows=49,136 loops=1)

  • Recheck Cond: ((period)::text = 'day'::text)
  • Rows Removed by Index Recheck: 480
  • Filter: ((time_stamp)::date >= (('now'::cstring)::date - 7))
  • Rows Removed by Filter: 589,761
  • Heap Blocks: exact=50,915 lossy=79,469
7. 627.776 627.776 ↓ 3.3 6,063,571 1

Bitmap Index Scan on comparisons_period_time_stamp_index (cost=0.00..86,393.66 rows=1,863,081 width=0) (actual time=627.776..627.776 rows=6,063,571 loops=1)

  • Index Cond: ((period)::text = 'day'::text)
8. 5.100 2,215.374 ↓ 18.6 279 1

GroupAggregate (cost=74,477.00..74,477.45 rows=15 width=578) (actual time=2,207.522..2,215.374 rows=279 loops=1)

  • Group Key: "*SELECT* 1".tariff_type_category, "*SELECT* 1".brand, "*SELECT* 1".plan_name, "*SELECT* 1".plan_name_key, ((comparisons.time_stamp)::date)
9. 22.651 2,210.274 ↓ 766.5 11,498 1

Sort (cost=74,477.00..74,477.04 rows=15 width=574) (actual time=2,207.511..2,210.274 rows=11,498 loops=1)

  • Sort Key: "*SELECT* 1".tariff_type_category, "*SELECT* 1".brand, "*SELECT* 1".plan_name, "*SELECT* 1".plan_name_key, ((comparisons.time_stamp)::date)
  • Sort Method: quicksort Memory: 1,819kB
10. 7.761 2,187.623 ↓ 766.5 11,498 1

Nested Loop (cost=0.68..74,476.71 rows=15 width=574) (actual time=1,466.271..2,187.623 rows=11,498 loops=1)

11. 0.008 1,466.045 ↑ 1.0 9 1

Append (cost=0.00..0.39 rows=9 width=566) (actual time=1,465.972..1,466.045 rows=9 loops=1)

12. 0.005 1,465.988 ↑ 1.0 3 1

Subquery Scan on *SELECT* 1 (cost=0.00..0.09 rows=3 width=566) (actual time=1,465.971..1,465.988 rows=3 loops=1)

13. 0.007 1,465.983 ↑ 1.0 3 1

Limit (cost=0.00..0.06 rows=3 width=566) (actual time=1,465.970..1,465.983 rows=3 loops=1)

14. 1,465.976 1,465.976 ↑ 207,009.0 3 1

CTE Scan on comparisons_last_7d (cost=0.00..12,420.54 rows=621,027 width=566) (actual time=1,465.968..1,465.976 rows=3 loops=1)

15. 0.006 0.021 ↑ 1.0 3 1

Subquery Scan on *SELECT* 2 (cost=0.00..0.15 rows=3 width=566) (actual time=0.005..0.021 rows=3 loops=1)

16. 0.006 0.015 ↑ 1.0 3 1

Limit (cost=0.00..0.12 rows=3 width=566) (actual time=0.005..0.015 rows=3 loops=1)

17. 0.009 0.009 ↑ 103,504.7 3 1

CTE Scan on comparisons_last_7d comparisons_last_7d_1 (cost=0.00..12,420.54 rows=310,514 width=566) (actual time=0.003..0.009 rows=3 loops=1)

  • Filter: is_variable
18. 0.005 0.028 ↑ 1.0 3 1

Subquery Scan on *SELECT* 3 (cost=0.00..0.15 rows=3 width=566) (actual time=0.012..0.028 rows=3 loops=1)

19. 0.004 0.023 ↑ 1.0 3 1

Limit (cost=0.00..0.12 rows=3 width=566) (actual time=0.011..0.023 rows=3 loops=1)

20. 0.019 0.019 ↑ 103,504.7 3 1

CTE Scan on comparisons_last_7d comparisons_last_7d_2 (cost=0.00..12,420.54 rows=310,514 width=566) (actual time=0.010..0.019 rows=3 loops=1)

  • Filter: (NOT is_variable)
  • Rows Removed by Filter: 7
21. 713.817 713.817 ↓ 639.0 1,278 9

Index Scan using comparisons_uniq on comparisons (cost=0.68..8,275.12 rows=2 width=64) (actual time=0.608..79.313 rows=1,278 loops=9)

  • Index Cond: (((brand)::text = ("*SELECT* 1".brand)::text) AND ((period)::text = 'day'::text) AND ((plan_name_key)::text = ("*SELECT* 1".plan_name_key)::text))
  • Filter: ((time_stamp)::date >= (('now'::cstring)::date - 30))
  • Rows Removed by Filter: 2,648
Planning time : 1.406 ms
Execution time : 2,361.607 ms