explain.depesz.com

PostgreSQL's explain analyze made readable

Result: aNL1

Settings
# exclusive inclusive rows x rows loops node
1. 12.159 4,260.620 ↑ 1.0 1 1

Aggregate (cost=190,975.74..190,975.75 rows=1 width=48) (actual time=4,260.615..4,260.620 rows=1 loops=1)

  • Functions: 40
  • Options: Inlining false, Optimization false, Expressions true, Deforming true
  • Timing: Generation 10.986 ms, Inlining 0.000 ms, Optimization 2.313 ms, Emission 63.718 ms, Total 77.017 ms
2. 12.354 4,248.461 ↓ 211.5 13,956 1

Subquery Scan on length_of_session (cost=157,213.62..190,974.75 rows=66 width=33) (actual time=2,167.511..4,248.461 rows=13,956 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: 84,564
3. 81.324 4,236.107 ↓ 3.7 98,520 1

WindowAgg (cost=157,213.62..190,576.74 rows=26,534 width=34) (actual time=2,167.142..4,236.107 rows=98,520 loops=1)

4. 139.604 4,154.783 ↓ 3.7 98,520 1

Subquery Scan on time_lag (cost=157,213.62..190,046.06 rows=26,534 width=40) (actual time=2,167.133..4,154.783 rows=98,520 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: 598,257
5. 666.018 4,015.179 ↓ 5.5 696,777 1

WindowAgg (cost=157,213.62..187,529.28 rows=125,839 width=56) (actual time=2,167.114..4,015.179 rows=696,777 loops=1)

6. 104.595 3,349.161 ↓ 5.5 696,777 1

Merge Append (cost=157,213.62..184,697.90 rows=125,839 width=24) (actual time=2,167.082..3,349.161 rows=696,777 loops=1)

  • Sort Key: ""*SELECT* 1"".remote_address_hash
7. 97.774 2,364.956 ↓ 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,377.980..2,364.956 rows=627,551 loops=1)

8. 646.716 2,267.182 ↓ 10.0 627,551 1

GroupAggregate (cost=140,354.02..162,945.85 rows=62,755 width=261) (actual time=1,377.978..2,267.182 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
9. 1,052.889 1,620.466 ↑ 1.0 627,551 1

Sort (cost=140,354.02..141,922.90 rows=627,551 width=261) (actual time=1,377.948..1,620.466 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
10. 86.495 567.577 ↑ 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=67.041..567.577 rows=627,551 loops=1)

  • Chunks excluded during startup: 0
11. 481.082 481.082 ↑ 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=67.040..481.082 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))
12. 10.917 879.610 ↓ 1.1 69,226 1

Subquery Scan on "*SELECT* 2" (cost=16,859.59..19,866.09 rows=63,084 width=24) (actual time=789.098..879.610 rows=69,226 loops=1)

13. 58.627 868.693 ↓ 1.1 69,226 1

Group (cost=16,859.59..19,235.25 rows=63,084 width=248) (actual time=789.096..868.693 rows=69,226 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""))
14. 121.887 810.066 ↓ 1.1 69,228 1

Sort (cost=16,859.59..17,018.02 rows=63,370 width=248) (actual time=789.073..810.066 rows=69,228 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: 19,056kB
15. 19.892 688.179 ↓ 1.1 69,228 1

Custom Scan (ChunkAppend) on access_log (cost=0.53..7,490.11 rows=63,370 width=248) (actual time=0.400..688.179 rows=69,228 loops=1)

  • Chunks excluded during startup: 6
16. 668.287 668.287 ↓ 1.1 69,228 1

Index Scan using _hyper_4_11_chunk_access_log_timestamp_idx on _hyper_4_11_chunk (cost=0.67..7,320.97 rows=63,364 width=240) (actual time=0.397..668.287 rows=69,228 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: 43,505
Planning time : 55.937 ms
Execution time : 4,302.299 ms