explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3X3dP

Settings
# exclusive inclusive rows x rows loops node
1. 0.014 301,205.823 ↑ 8.3 24 1

Sort (cost=264,443.23..264,443.33 rows=200 width=20) (actual time=301,205.822..301,205.823 rows=24 loops=1)

  • Sort Key: ((date_trunc('hour'::text, now()) + (((s.a)::text || ' hours'::text))::interval))
  • Sort Method: quicksort Memory: 26kB
2. 6.781 301,205.809 ↑ 8.3 24 1

HashAggregate (cost=264,440.30..264,441.70 rows=200 width=20) (actual time=301,205.804..301,205.809 rows=24 loops=1)

  • Group Key: (date_trunc('hour'::text, now()) + (((s.a)::text || ' hours'::text))::interval)
3. 120,422.616 301,199.028 ↑ 103.0 19,769 1

Nested Loop (cost=3,164.21..262,405.08 rows=2,035,222 width=20) (actual time=4,148.793..301,199.028 rows=19,769 loops=1)

  • Join Filter: ((ce.created_at <= (date_trunc('hour'::text, now()) + (((s.a)::text || ' hours'::text))::interval)) AND (ce.created_at >= ((date_trunc('hour'::text, now()) + (((s.a)::text || ' hours'::text))::interval) + '-01:00:00'::interval)))
  • Rows Removed by Join Filter: 91666039
4. 0.036 0.036 ↑ 41.7 24 1

Function Scan on generate_series s (cost=0.00..3.00 rows=1,000 width=4) (actual time=0.008..0.036 rows=24 loops=1)

5. 10,099.982 180,776.376 ↓ 208.6 3,820,242 24

Materialize (cost=3,164.21..16,149.35 rows=18,317 width=24) (actual time=0.121..7,532.349 rows=3,820,242 loops=24)

6. 724.986 170,676.394 ↓ 208.6 3,820,242 1

Nested Loop (cost=3,164.21..16,131.03 rows=18,317 width=24) (actual time=1.977..170,676.394 rows=3,820,242 loops=1)

7. 0.324 2.453 ↓ 253.0 253 1

Unique (cost=2,654.41..2,654.41 rows=1 width=16) (actual time=1.916..2.453 rows=253 loops=1)

8. 0.285 2.129 ↓ 253.0 253 1

Sort (cost=2,654.41..2,654.41 rows=1 width=16) (actual time=1.916..2.129 rows=253 loops=1)

  • Sort Key: b.id
  • Sort Method: quicksort Memory: 36kB
9. 0.272 1.844 ↓ 253.0 253 1

Nested Loop Left Join (cost=320.22..2,654.40 rows=1 width=16) (actual time=0.454..1.844 rows=253 loops=1)

  • Join Filter: (b.id = bs.business_id)
  • Rows Removed by Join Filter: 2349
  • Filter: ((bs.value IS NULL) OR ((bs.value)::text = 'false'::text))
  • Rows Removed by Filter: 9
10. 1.043 1.310 ↓ 1.0 262 1

Bitmap Heap Scan on businesses b (cost=320.17..2,595.22 rows=251 width=16) (actual time=0.324..1.310 rows=262 loops=1)

  • Recheck Cond: (deleted_at IS NULL)
  • Filter: (subscription_active OR (trial_end_date >= now()))
  • Rows Removed by Filter: 1137
  • Heap Blocks: exact=369
11. 0.267 0.267 ↓ 1.1 1,608 1

Bitmap Index Scan on index_businesses_on_deleted_at (cost=0.00..320.15 rows=1,399 width=0) (actual time=0.267..0.267 rows=1,608 loops=1)

  • Index Cond: (deleted_at IS NULL)
12. 0.143 0.262 ↓ 9.0 9 262

Materialize (cost=0.06..58.06 rows=1 width=22) (actual time=0.000..0.001 rows=9 loops=262)

13. 0.119 0.119 ↓ 9.0 9 1

Index Scan using index_business_settings_on_business_id_and_name on business_settings bs (cost=0.06..58.06 rows=1 width=22) (actual time=0.013..0.119 rows=9 loops=1)

  • Index Cond: ((name)::text = 'internalAccount'::text)
14. 133,319.109 169,948.955 ↑ 1.2 15,100 253

Bitmap Heap Scan on conversation_elements ce (cost=509.80..13,421.67 rows=18,317 width=40) (actual time=146.919..671.735 rows=15,100 loops=253)

  • Recheck Cond: (business_id = b.id)
  • Filter: ((type)::text = 'ConversationElements::Message'::text)
  • Rows Removed by Filter: 1493
  • Heap Blocks: exact=2095506
15. 36,629.846 36,629.846 ↑ 1.1 18,652 253

Bitmap Index Scan on index_conversation_elements_business_id_created_at (cost=0.00..508.89 rows=20,359 width=0) (actual time=144.782..144.782 rows=18,652 loops=253)

  • Index Cond: (business_id = b.id)
Planning time : 0.872 ms