explain.depesz.com

PostgreSQL's explain analyze made readable

Result: RQshm

Settings
# exclusive inclusive rows x rows loops node
1. 13.883 4,637.041 ↑ 1.0 1 1

Aggregate (cost=194,301.91..194,301.92 rows=1 width=48) (actual time=4,637.035..4,637.041 rows=1 loops=1)

  • Buffers: shared hit=87,984 dirtied=308, temp read=21,636 written=21,662
  • Functions: 47
  • Options: Inlining false, Optimization false, Expressions true, Deforming true
  • Timing: Generation 13.868 ms, Inlining 0.000 ms, Optimization 3.016 ms, Emission 76.222 ms, Total 93.105 ms
2. 14.982 4,623.158 ↓ 199.9 14,194 1

Subquery Scan on length_of_session (cost=159,505.44..194,300.84 rows=71 width=33) (actual time=2,440.149..4,623.158 rows=14,194 loops=1)

  • Filter: (length_of_session.is_end_of_session AND (length_of_session.length_of_session >= '00:00:10'::interval) AND (length_of_session.length_of_session <= '01:00:00'::interval))
  • Rows Removed by Filter: 86,028
  • Buffers: shared hit=87,984 dirtied=308, temp read=21,636 written=21,662
3. 84.409 4,608.176 ↓ 3.5 100,222 1

WindowAgg (cost=159,505.44..193,872.41 rows=28,562 width=34) (actual time=2,439.717..4,608.176 rows=100,222 loops=1)

  • Buffers: shared hit=87,984 dirtied=308, temp read=21,636 written=21,662
4. 155.312 4,523.767 ↓ 3.5 100,222 1

Subquery Scan on time_lag (cost=159,505.44..193,301.17 rows=28,562 width=40) (actual time=2,439.706..4,523.767 rows=100,222 loops=1)

  • Filter: ((((time_lag.time_diff_lag IS NULL) OR (time_lag.time_diff_lag > '00:30:00'::interval)) AND (time_lag.time_diff_lead IS NOT NULL) AND (time_lag.time_diff_lead >= '-00:30:00'::interval)) OR ((time_lag.time_diff_lag IS NOT NULL) AND (time_lag.time_diff_lag <= '00:30:00'::interval) AND ((time_lag.time_diff_lead IS NULL) OR (time_lag.time_diff_lead < '-00:30:00'::interval))))
  • Rows Removed by Filter: 608,849
  • Buffers: shared hit=87,984 dirtied=308, temp read=21,636 written=21,662
5. 686.003 4,368.455 ↓ 5.2 709,071 1

WindowAgg (cost=159,505.44..190,592.03 rows=135,457 width=56) (actual time=2,439.693..4,368.455 rows=709,071 loops=1)

  • Buffers: shared hit=87,984 dirtied=308, temp read=21,636 written=21,662
6. 114.727 3,682.452 ↓ 5.2 709,071 1

Merge Append (cost=159,505.44..187,544.25 rows=135,457 width=24) (actual time=2,439.667..3,682.452 rows=709,071 loops=1)

  • Sort Key: ""*SELECT* 1"".remote_address_hash
  • Buffers: shared hit=87,984 dirtied=308, temp read=21,636 written=21,662
7. 106.085 2,467.145 ↓ 10.0 627,551 1

Subquery Scan on "*SELECT* 1" (cost=140,354.02..163,573.40 rows=62,755 width=24) (actual time=1,455.568..2,467.145 rows=627,551 loops=1)

  • Buffers: shared hit=69,825, temp read=18,832 written=18,855
8. 656.539 2,361.060 ↓ 10.0 627,551 1

GroupAggregate (cost=140,354.02..162,945.85 rows=62,755 width=261) (actual time=1,455.566..2,361.060 rows=627,551 loops=1)

  • Group Key: _materialized_hypertable_11.remote_address_hash, _materialized_hypertable_11.""timestamp"", _materialized_hypertable_11.request_url, _materialized_hypertable_11.city_name, _materialized_hypertable_11.subdivision_1_name, _materialized_hypertable_11.subdivision_2_name, _materialized_hypertable_11.user_agent, _materialized_hypertable_11.content_type, _materialized_hypertable_11.country_name, _materialized_hypertable_11.accuracy_radius, _materialized_hypertable_11.latitude, _materialized_hypertable_11.longitude, _materialized_hypertable_11.bucket
  • Buffers: shared hit=69,825, temp read=18,832 written=18,855
9. 1,106.680 1,704.521 ↑ 1.0 627,551 1

Sort (cost=140,354.02..141,922.90 rows=627,551 width=261) (actual time=1,455.520..1,704.521 rows=627,551 loops=1)

  • Sort Key: _materialized_hypertable_11.remote_address_hash, _materialized_hypertable_11.""timestamp"", _materialized_hypertable_11.request_url, _materialized_hypertable_11.city_name, _materialized_hypertable_11.subdivision_1_name, _materialized_hypertable_11.subdivision_2_name, _materialized_hypertable_11.user_agent, _materialized_hypertable_11.content_type, _materialized_hypertable_11.country_name, _materialized_hypertable_11.accuracy_radius, _materialized_hypertable_11.latitude, _materialized_hypertable_11.longitude, _materialized_hypertable_11.bucket
  • Sort Method: external merge Disk: 150,656kB
  • Buffers: shared hit=69,825, temp read=18,832 written=18,855
10. 94.016 597.841 ↑ 1.0 627,551 1

Custom Scan (ChunkAppend) on _materialized_hypertable_11 (cost=0.68..34,693.67 rows=627,551 width=261) (actual time=80.365..597.841 rows=627,551 loops=1)

  • Chunks excluded during startup: 0
  • Buffers: shared hit=69,816
11. 503.825 503.825 ↑ 1.0 627,551 1

Index Scan using _hyper_11_15_chunk__materialized_hypertable_11_bucket_idx on _hyper_11_15_chunk (cost=0.68..34,693.67 rows=627,551 width=261) (actual time=80.363..503.825 rows=627,551 loops=1)

  • Index Cond: (bucket < COALESCE(_timescaledb_internal.to_timestamp_without_timezone(_timescaledb_internal.cagg_watermark('11'::oid)), '-infinity'::timestamp without time zone))
  • Buffers: shared hit=69,816
12. 14.509 1,100.580 ↓ 1.1 81,520 1

Subquery Scan on "*SELECT* 2" (cost=19,151.41..22,616.27 rows=72,702 width=24) (actual time=984.096..1,100.580 rows=81,520 loops=1)

  • Buffers: shared hit=18,159 dirtied=308, temp read=2,804 written=2,807
13. 73.893 1,086.071 ↓ 1.1 81,520 1

Group (cost=19,151.41..21,889.25 rows=72,702 width=248) (actual time=984.087..1,086.071 rows=81,520 loops=1)

  • Group Key: access_log.remote_address_hash, access_log.""timestamp"", access_log.request_url, access_log.city_name, access_log.subdivision_1_name, access_log.subdivision_2_name, access_log.user_agent, access_log.content_type, access_log.country_name, access_log.accuracy_radius, access_log.latitude, access_log.longitude, (time_bucket('1 day'::interval, access_log.""timestamp""))
  • Buffers: shared hit=18,159 dirtied=308, temp read=2,804 written=2,807
14. 151.312 1,012.178 ↓ 1.1 81,522 1

Sort (cost=19,151.41..19,333.98 rows=73,031 width=248) (actual time=984.048..1,012.178 rows=81,522 loops=1)

  • Sort Key: access_log.remote_address_hash, access_log.""timestamp"", access_log.request_url, access_log.city_name, access_log.subdivision_1_name, access_log.subdivision_2_name, access_log.user_agent, access_log.content_type, access_log.country_name, access_log.accuracy_radius, access_log.latitude, access_log.longitude, (time_bucket('1 day'::interval, access_log.""timestamp""))
  • Sort Method: external merge Disk: 22,432kB
  • Buffers: shared hit=18,159 dirtied=308, temp read=2,804 written=2,807
15. 25.409 860.866 ↓ 1.1 81,522 1

Custom Scan (ChunkAppend) on access_log (cost=0.53..8,280.63 rows=73,031 width=248) (actual time=0.503..860.866 rows=81,522 loops=1)

  • Chunks excluded during startup: 6
  • Buffers: shared hit=18,159 dirtied=308
16. 769.506 769.506 ↓ 1.1 74,846 1

Index Scan using _hyper_4_11_chunk_timestamp_index on _hyper_4_11_chunk (cost=0.67..7,385.02 rows=66,323 width=240) (actual time=0.499..769.506 rows=74,846 loops=1)

  • Index Cond: (""timestamp"" >= COALESCE(_timescaledb_internal.to_timestamp_without_timezone(_timescaledb_internal.cagg_watermark('11'::oid)), '-infinity'::timestamp without time zone))
  • Filter: (((user_agent)::text !~~* '%bot%'::text) AND ((user_agent)::text !~~* '%crawler%'::text) AND ((user_agent)::text !~~* '%spider%'::text))
  • Rows Removed by Filter: 47,815
  • Buffers: shared hit=16,828 dirtied=295
17. 65.951 65.951 ↑ 1.0 6,676 1

Index Scan using _hyper_4_16_chunk_timestamp_index on _hyper_4_16_chunk (cost=0.54..702.32 rows=6,702 width=242) (actual time=0.396..65.951 rows=6,676 loops=1)

  • Index Cond: (""timestamp"" >= COALESCE(_timescaledb_internal.to_timestamp_without_timezone(_timescaledb_internal.cagg_watermark('11'::oid)), '-infinity'::timestamp without time zone))
  • Filter: (((user_agent)::text !~~* '%bot%'::text) AND ((user_agent)::text !~~* '%crawler%'::text) AND ((user_agent)::text !~~* '%spider%'::text))
  • Rows Removed by Filter: 3,757
  • Buffers: shared hit=1,331 dirtied=13
Planning time : 68.155 ms
Execution time : 4,717.647 ms