explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mbxp

Settings
# exclusive inclusive rows x rows loops node
1. 501.604 8,487.660 ↑ 17.4 30 1

GroupAggregate (cost=928,259.99..928,276.92 rows=521 width=56) (actual time=7,521.395..8,487.660 rows=30 loops=1)

  • Group Key: (to_char(orders."timestamp", 'YYYY-MM-DD'::text))
2. 1,049.956 7,986.056 ↓ 210.9 109,887 1

Sort (cost=928,259.99..928,261.29 rows=521 width=36) (actual time=7,485.860..7,986.056 rows=109,887 loops=1)

  • Sort Key: (to_char(orders."timestamp", 'YYYY-MM-DD'::text))
  • Sort Method: quicksort Memory: 8223kB
3. 1,281.677 6,936.100 ↓ 210.9 109,887 1

Hash Left Join (cost=3,902.25..928,236.48 rows=521 width=36) (actual time=24.884..6,936.100 rows=109,887 loops=1)

  • Hash Cond: ((orders.order_currency)::text = (currencies.currencycode)::text)
4. 1,142.637 5,654.043 ↓ 210.9 109,887 1

Nested Loop (cost=3,895.49..928,221.25 rows=521 width=16) (actual time=24.418..5,654.043 rows=109,887 loops=1)

5. 0.022 0.050 ↑ 1.0 1 1

Nested Loop (cost=0.00..3.04 rows=1 width=4) (actual time=0.037..0.050 rows=1 loops=1)

  • Join Filter: (campaigns.advertiser_id = advertiser.id)
6. 0.019 0.019 ↑ 1.0 1 1

Seq Scan on campaigns (cost=0.00..2.01 rows=1 width=8) (actual time=0.014..0.019 rows=1 loops=1)

  • Filter: (id = 1)
7. 0.009 0.009 ↑ 1.0 1 1

Seq Scan on advertiser (cost=0.00..1.01 rows=1 width=4) (actual time=0.009..0.009 rows=1 loops=1)

  • Filter: (network_id = 1)
8. 2,052.406 4,511.356 ↓ 210.9 109,887 1

Bitmap Heap Scan on orders (cost=3,895.49..928,213.00 rows=521 width=24) (actual time=24.364..4,511.356 rows=109,887 loops=1)

  • Recheck Cond: (("timestamp" >= '2019-04-15 00:00:00+02'::timestamp with time zone) AND ("timestamp" <= '2019-05-14 23:59:59+02'::timestamp with time zone))
  • Filter: ((campaign_id = 1) AND (((SubPlan 1))::text = 'click'::text))
  • Rows Removed by Filter: 1257
  • Heap Blocks: exact=21983
9. 13.782 13.782 ↓ 1.2 126,153 1

Bitmap Index Scan on orders_timestamp_idx (cost=0.00..3,895.36 rows=104,293 width=0) (actual time=13.782..13.782 rows=126,153 loops=1)

  • Index Cond: (("timestamp" >= '2019-04-15 00:00:00+02'::timestamp with time zone) AND ("timestamp" <= '2019-05-14 23:59:59+02'::timestamp with time zone))
10.          

SubPlan (forBitmap Heap Scan)

11. 1,667.160 2,445.168 ↑ 1.0 1 111,144

Limit (cost=0.42..8.44 rows=1 width=6) (actual time=0.017..0.022 rows=1 loops=111,144)

12. 778.008 778.008 ↑ 1.0 1 111,144

Index Scan using order_actions_id_seq2 on order_actions (cost=0.42..8.44 rows=1 width=6) (actual time=0.007..0.007 rows=1 loops=111,144)

  • Index Cond: (id = orders.order_actions_id)
13. 0.168 0.380 ↑ 1.0 34 1

Hash (cost=6.34..6.34 rows=34 width=4) (actual time=0.380..0.380 rows=34 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
14. 0.212 0.212 ↑ 1.0 34 1

Seq Scan on currencies (cost=0.00..6.34 rows=34 width=4) (actual time=0.022..0.212 rows=34 loops=1)

Planning time : 2.161 ms
Execution time : 8,488.834 ms