explain.depesz.com

PostgreSQL's explain analyze made readable

Result: AJTP

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 38.071 ↓ 5.8 23 1

Limit (cost=52.68..52.87 rows=4 width=100) (actual time=37.995..38.071 rows=23 loops=1)

2. 0.002 38.067 ↓ 5.8 23 1

Append (cost=52.68..52.87 rows=4 width=100) (actual time=37.993..38.067 rows=23 loops=1)

3. 0.273 38.058 ↓ 7.7 23 1

GroupAggregate (cost=52.68..52.80 rows=3 width=134) (actual time=37.993..38.058 rows=23 loops=1)

  • Group Key: sub_query2.correct_interval_time, sub_query2.stock_id, sub_query2.data_type_id, sub_query2.stock_trade_data_series_id
4. 0.118 37.785 ↓ 126.0 378 1

Sort (cost=52.68..52.68 rows=3 width=134) (actual time=37.761..37.785 rows=378 loops=1)

  • Sort Key: sub_query2.correct_interval_time, sub_query2.stock_id, sub_query2.data_type_id, sub_query2.stock_trade_data_series_id
  • Sort Method: quicksort Memory: 78kB
5. 0.044 37.667 ↓ 126.0 378 1

Subquery Scan on sub_query2 (cost=52.54..52.65 rows=3 width=134) (actual time=37.211..37.667 rows=378 loops=1)

6. 0.385 37.623 ↓ 126.0 378 1

WindowAgg (cost=52.54..52.62 rows=3 width=64) (actual time=37.211..37.623 rows=378 loops=1)

7. 0.312 37.238 ↓ 126.0 378 1

Sort (cost=52.54..52.55 rows=3 width=64) (actual time=37.200..37.238 rows=378 loops=1)

  • Sort Key: sub_query1.data_type_id, sub_query1.correct_interval_time, sub_query1.trade_time
  • Sort Method: quicksort Memory: 78kB
8. 0.075 36.926 ↓ 126.0 378 1

Subquery Scan on sub_query1 (cost=0.55..52.52 rows=3 width=64) (actual time=32.622..36.926 rows=378 loops=1)

9. 1.313 36.851 ↓ 126.0 378 1

Nested Loop (cost=0.55..52.49 rows=3 width=60) (actual time=32.622..36.851 rows=378 loops=1)

  • Join Filter: ((stock_trade_data_types.id = 2) OR (stock_trade_data.is_visible AND (date_trunc('minute'::text, stock_trade_data.trade_time) > '2019-11-08 14:17:00'::timestamp without time zone)))
  • Rows Removed by Join Filter: 3600
10. 34.476 34.476 ↓ 102.0 306 1

Index Scan using idx_std_is_temporary_stdtype_id_datetrunc on stock_trade_data (cost=0.55..49.05 rows=3 width=53) (actual time=32.542..34.476 rows=306 loops=1)

  • Index Cond: ((stock_id = 2601) AND (stock_trade_data_type_id = 1) AND (is_live = true))
  • Filter: (is_live AND (stock_trade_data_series_id = ANY ('{1,2,3}'::integer[])) AND (trade_time <= (clock_timestamp() - '00:05:00'::interval)))
  • Rows Removed by Filter: 4
11. 0.248 0.306 ↑ 1.1 13 306

Materialize (cost=0.00..1.81 rows=14 width=8) (actual time=0.000..0.001 rows=13 loops=306)

12. 0.002 0.058 ↑ 1.1 13 1

Append (cost=0.00..1.74 rows=14 width=8) (actual time=0.027..0.058 rows=13 loops=1)

13. 0.054 0.054 ↑ 1.1 12 1

Seq Scan on stock_trade_data_types (cost=0.00..1.57 rows=13 width=13) (actual time=0.026..0.054 rows=12 loops=1)

  • Filter: (id = ANY ('{1,2,3,4,5,6,7,8,9,10,16,17,18}'::integer[]))
  • Rows Removed by Filter: 5
14. 0.002 0.002 ↑ 1.0 1 1

Result (cost=0.02..0.03 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=1)

  • One-Time Filter: (2 = ANY ('{1,2,3,4,5,6,7,8,9,10,16,17,18}'::integer[]))
15.          

SubPlan (for Nested Loop)

16. 0.756 0.756 ↑ 1.0 1 378

Result (cost=0.00..0.27 rows=1 width=0) (actual time=0.001..0.002 rows=1 loops=378)

17. 0.000 0.007 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=0.02..0.04 rows=1 width=0) (actual time=0.007..0.007 rows=0 loops=1)

18. 0.001 0.007 ↓ 0.0 0 1

Limit (cost=0.02..0.03 rows=1 width=0) (actual time=0.007..0.007 rows=0 loops=1)

19. 0.005 0.006 ↓ 0.0 0 1

Sort (cost=0.02..0.03 rows=1 width=0) (actual time=0.006..0.006 rows=0 loops=1)

  • Sort Key: (date_trunc('minute'::text, trade_time))
  • Sort Method: quicksort Memory: 25kB
20. 0.001 0.001 ↓ 0.0 0 1

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=1)

  • One-Time Filter: false
Planning time : 0.638 ms
Execution time : 38.183 ms