explain.depesz.com

PostgreSQL's explain analyze made readable

Result: M8tq

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 7.553 ↑ 2.2 13 1

Limit (cost=74.27..75.72 rows=28 width=129) (actual time=7.533..7.553 rows=13 loops=1)

2. 0.000 7.551 ↑ 2.2 13 1

Append (cost=74.27..75.72 rows=28 width=129) (actual time=7.531..7.551 rows=13 loops=1)

3. 0.478 7.544 ↑ 2.1 13 1

GroupAggregate (cost=74.27..75.42 rows=27 width=134) (actual time=7.531..7.544 rows=13 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.199 7.066 ↓ 31.3 844 1

Sort (cost=74.27..74.34 rows=27 width=134) (actual time=7.021..7.066 rows=844 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: 143kB
5. 0.074 6.867 ↓ 31.3 844 1

Subquery Scan on sub_query2 (cost=72.61..73.63 rows=27 width=134) (actual time=6.106..6.867 rows=844 loops=1)

6. 0.651 6.793 ↓ 31.3 844 1

WindowAgg (cost=72.61..73.36 rows=27 width=64) (actual time=6.104..6.793 rows=844 loops=1)

7. 0.530 6.142 ↓ 31.3 844 1

Sort (cost=72.61..72.68 rows=27 width=64) (actual time=6.095..6.142 rows=844 loops=1)

  • Sort Key: sub_query1.data_type_id, sub_query1.correct_interval_time, sub_query1.trade_time
  • Sort Method: quicksort Memory: 143kB
8. 0.084 5.612 ↓ 31.3 844 1

Subquery Scan on sub_query1 (cost=0.43..71.97 rows=27 width=64) (actual time=0.229..5.612 rows=844 loops=1)

9. 3.154 5.528 ↓ 31.3 844 1

Nested Loop (cost=0.43..71.70 rows=27 width=60) (actual time=0.228..5.528 rows=844 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-06 15:19:00'::timestamp without time zone)))
  • Rows Removed by Join Filter: 9972
10. 1.542 1.542 ↓ 166.4 832 1

Index Scan using idx_std_is_temporary_stdtype_id_datetrunc on stock_trade_data (cost=0.43..61.11 rows=5 width=53) (actual time=0.084..1.542 rows=832 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)))
11. 0.733 0.832 ↑ 1.1 13 832

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

12. 0.002 0.099 ↑ 1.1 13 1

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

13. 0.094 0.094 ↑ 1.1 12 1

Seq Scan on stock_trade_data_types (cost=0.00..1.57 rows=13 width=62) (actual time=0.049..0.094 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.003 0.003 ↑ 1.0 1 1

Result (cost=0.02..0.03 rows=1 width=0) (actual time=0.003..0.003 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.000 0.000 ↑ 1.0 1 844

Result (cost=0.00..0.27 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=844)

17. 0.001 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.000 0.006 ↓ 0.0 0 1

Limit (cost=0.02..0.03 rows=1 width=0) (actual time=0.006..0.006 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.479 ms