explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8TSO : Optimization for: Optimization for: plan #itSXI; plan #ZZOu

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.012 1,272.766 ↓ 1.5 6 1
→

Nested Loop (cost=155,379.53..155,434.72 rows=4 width=126) (actual time=1,272.713..1,272.766 rows=6 loops=1)

2.          

CTE data
3. 0.002 1,272.695 ↑ 1.0 6 1
→

Limit (cost=155,378.92..155,379.10 rows=6 width=22) (actual time=1,272.686..1,272.695 rows=6 loops=1)

4. 0.015 1,272.693 ↑ 1.5 6 1
→

Finalize GroupAggregate (cost=155,378.92..155,379.19 rows=9 width=22) (actual time=1,272.685..1,272.693 rows=6 loops=1)

  • Group Key: cte_1.card_network, cte_1.type
5. 0.046 1,272.678 ↑ 1.0 18 1
→

Sort (cost=155,378.92..155,378.96 rows=18 width=22) (actual time=1,272.676..1,272.678 rows=18 loops=1)

  • Sort Key: cte_1.card_network, cte_1.type
  • Sort Method: quicksort Memory: 26kB
6. 8.193 1,272.632 ↑ 1.0 18 1
→

Gather (cost=155,376.65..155,378.54 rows=18 width=22) (actual time=1,270.794..1,272.632 rows=18 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
7. 475.874 1,264.439 ↑ 1.5 6 3
→

Partial HashAggregate (cost=154,376.65..154,376.74 rows=9 width=22) (actual time=1,264.437..1,264.439 rows=6 loops=3)

  • Group Key: cte_1.card_network, cte_1.type
8. 681.724 788.565 ↓ 28.2 637,529 3
→

Parallel Bitmap Heap Scan on card_transaction_events cte_1 (cost=1,155.95..154,207.29 rows=22,582 width=22) (actual time=112.034..788.565 rows=637,529 loops=3)

  • Recheck Cond: (((date_trunc('day'::text, transaction_timestamp))::date >= '2019-01-01'::date) AND ((date_trunc('day'::text, transaction_timestamp))::date <= '2019-01-15'::date))
  • Rows Removed by Index Recheck: 15166
  • Heap Blocks: exact=20016 lossy=13396
9. 106.841 106.841 ↓ 35.3 1,912,586 1
→

Bitmap Index Scan on card_transaction_events_ix6 (cost=0.00..1,142.40 rows=54,197 width=0) (actual time=106.841..106.841 rows=1,912,586 loops=1)

  • Index Cond: (((date_trunc('day'::text, transaction_timestamp))::date >= '2019-01-01'::date) AND ((date_trunc('day'::text, transaction_timestamp))::date <= '2019-01-15'::date))
10. 1,272.700 1,272.700 ↑ 1.0 6 1
→

CTE Scan on data d (cost=0.00..0.12 rows=6 width=108) (actual time=1,272.688..1,272.700 rows=6 loops=1)

11. 0.054 0.054 ↑ 3.0 1 6
→

Index Scan using card_transaction_events_ix5 on card_transaction_events cte (cost=0.43..9.22 rows=3 width=40) (actual time=0.008..0.009 rows=1 loops=6)

  • Index Cond: (transaction_timestamp = d.transaction_timestamp)
  • Filter: (((d.card_network)::text = (card_network)::text) AND ((d.type)::text = (type)::text))
  • Rows Removed by Filter: 3