explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Sv2

Settings
# exclusive inclusive rows x rows loops node
1. 1,255.892 13,025.626 ↓ 10.7 348,627 1

Sort (cost=744,167.59..744,248.90 rows=32,524 width=556) (actual time=12,619.341..13,025.626 rows=348,627 loops=1)

  • Sort Key: (((messages.metadata -> 'share_count'::text))::integer), messages.posted_at
  • Sort Method: quicksort Memory: 39525kB
2.          

CTE filtered_messages

3. 1,092.188 1,208.411 ↓ 1.6 524,544 1

Bitmap Heap Scan on messages messages_1 (cost=9,947.16..523,638.05 rows=325,233 width=2,043) (actual time=140.709..1,208.411 rows=524,544 loops=1)

  • Recheck Cond: ((feed_id = 7) AND (posted_at >= '2015-04-20 04:00:00'::timestamp without time zone) AND (posted_at < '2015-04-28 04:00:00'::timestamp without time zone))
  • Heap Blocks: exact=66126
4. 116.223 116.223 ↓ 1.6 525,606 1

Bitmap Index Scan on index_messages_on_feed_id_posted_at (cost=0.00..9,865.85 rows=325,233 width=0) (actual time=116.223..116.223 rows=525,606 loops=1)

  • Index Cond: ((feed_id = 7) AND (posted_at >= '2015-04-20 04:00:00'::timestamp without time zone) AND (posted_at < '2015-04-28 04:00:00'::timestamp without time zone))
5. 922.199 11,769.734 ↓ 10.7 348,627 1

Subquery Scan on messages (cost=214,595.69..218,091.99 rows=32,524 width=556) (actual time=9,103.608..11,769.734 rows=348,627 loops=1)

6. 1,111.061 10,847.535 ↓ 10.7 348,627 1

Unique (cost=214,595.69..216,221.86 rows=32,524 width=1,072) (actual time=9,103.584..10,847.535 rows=348,627 loops=1)

7. 6,096.008 9,736.474 ↓ 1.6 524,544 1

Sort (cost=214,595.69..215,408.78 rows=325,233 width=1,072) (actual time=9,103.579..9,736.474 rows=524,544 loops=1)

  • Sort Key: (COALESCE(filtered_messages.shared_parent_id, filtered_messages.id_str)), filtered_messages.posted_at
  • Sort Method: quicksort Memory: 164390kB
8. 3,640.466 3,640.466 ↓ 1.6 524,544 1

CTE Scan on filtered_messages (cost=0.00..32,523.30 rows=325,233 width=1,072) (actual time=140.724..3,640.466 rows=524,544 loops=1)

Planning time : 0.476 ms
Execution time : 13,585.216 ms