explain.depesz.com

PostgreSQL's explain analyze made readable

Result: CyFu

Settings
# exclusive inclusive rows x rows loops node
1. 4.232 52,446.513 ↑ 1.0 50,000 1

Limit (cost=696,175.24..696,300.24 rows=50,000 width=1,226) (actual time=52,386.496..52,446.513 rows=50,000 loops=1)

2. 709.818 52,442.281 ↑ 1.5 50,000 1

Sort (cost=696,175.24..696,366.00 rows=76,302 width=1,226) (actual time=52,386.494..52,442.281 rows=50,000 loops=1)

  • Sort Key: d.partition_id
  • Sort Method: external merge Disk: 132496kB
3. 129.138 51,732.463 ↓ 1.4 106,952 1

Hash Join (cost=1,425.51..649,040.87 rows=76,302 width=1,226) (actual time=574.671..51,732.463 rows=106,952 loops=1)

  • Hash Cond: (d.topic_id = t.id)
4. 35.140 51,594.303 ↓ 1.4 106,952 1

Hash Join (cost=690.66..647,256.86 rows=76,302 width=134) (actual time=565.625..51,594.303 rows=106,952 loops=1)

  • Hash Cond: (d.partition_id = p.id)
5. 9.751 51,558.476 ↑ 37.3 106,952 1

Append (cost=0.00..630,849.85 rows=3,987,554 width=81) (actual time=564.892..51,558.476 rows=106,952 loops=1)

6. 0.007 0.007 ↓ 0.0 0 1

Seq Scan on data d (cost=0.00..0.00 rows=1 width=81) (actual time=0.007..0.007 rows=0 loops=1)

  • Filter: ((start_time <= '2019-07-19 07:06:24'::timestamp without time zone) AND (end_time >= '2019-07-19 07:01:24'::timestamp without time zone) AND (start_time >= '2019-07-19 02:01:24'::timestamp without time zone))
7. 51,548.718 51,548.718 ↑ 37.3 106,952 1

Index Scan using start_time_index_2019_07 on data_2019_07 d_1 (cost=0.58..630,849.85 rows=3,987,553 width=81) (actual time=564.882..51,548.718 rows=106,952 loops=1)

  • Index Cond: ((start_time <= '2019-07-19 07:06:24'::timestamp without time zone) AND (start_time >= '2019-07-19 02:01:24'::timestamp without time zone))
  • Filter: (end_time >= '2019-07-19 07:01:24'::timestamp without time zone)
  • Rows Removed by Filter: 11429093
8. 0.116 0.687 ↑ 1.1 436 1

Hash (cost=684.94..684.94 rows=458 width=61) (actual time=0.687..0.687 rows=436 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 50kB
9. 0.304 0.571 ↑ 1.1 436 1

Bitmap Heap Scan on partition p (cost=12.99..684.94 rows=458 width=61) (actual time=0.285..0.571 rows=436 loops=1)

  • Recheck Cond: (((bag_date)::text >= to_char('2019-07-18 07:01:24'::timestamp without time zone, 'YYYYMMDD'::text)) AND ((bag_date)::text <= to_char('2019-07-19 07:06:24'::timestamp without time zone, 'YYYYMMDD'::text)))
  • Heap Blocks: exact=91
10. 0.267 0.267 ↑ 1.1 436 1

Bitmap Index Scan on partition_bag_date_idx (cost=0.00..12.87 rows=458 width=0) (actual time=0.267..0.267 rows=436 loops=1)

  • Index Cond: (((bag_date)::text >= to_char('2019-07-18 07:01:24'::timestamp without time zone, 'YYYYMMDD'::text)) AND ((bag_date)::text <= to_char('2019-07-19 07:06:24'::timestamp without time zone, 'YYYYMMDD'::text)))
11. 5.507 9.022 ↓ 1.0 4,007 1

Hash (cost=686.60..686.60 rows=3,860 width=1,108) (actual time=9.022..9.022 rows=4,007 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 4516kB
12. 3.515 3.515 ↓ 1.0 4,007 1

Seq Scan on topic t (cost=0.00..686.60 rows=3,860 width=1,108) (actual time=0.006..3.515 rows=4,007 loops=1)

Planning time : 1.816 ms
Execution time : 52,489.608 ms