explain.depesz.com

PostgreSQL's explain analyze made readable

Result: kQQ0

Settings
# exclusive inclusive rows x rows loops node
1. 0.005 453.543 ↑ 246.0 2 1

Sort (cost=31,493.55..31,494.78 rows=492 width=981) (actual time=453.542..453.543 rows=2 loops=1)

  • Sort Key: m.""time"
  • Sort Method: quicksort Memory: 26kB
2. 0.001 453.538 ↑ 246.0 2 1

Append (cost=0.43..31,471.55 rows=492 width=981) (actual time=453.522..453.538 rows=2 loops=1)

3. 421.017 453.237 ↓ 0.0 0 1

Index Scan using message_partition_1970_01_device_idx on message_partition_1970_01 m (cost=0.43..29,007.92 rows=455 width=985) (actual time=453.237..453.237 rows=0 loops=1)

  • Index Cond: (device = 513)
  • Filter: ((""time"" < '2016-02-27 21:00:42.164+02'::timestamp with time zone) AND (""time"" >= (SubPlan 1)))
  • Rows Removed by Filter: 1,770
4.          

SubPlan (for Index Scan)

5. 0.000 32.220 ↑ 1.0 1 1,790

Limit (cost=1.12..18.50 rows=1 width=8) (actual time=0.018..0.018 rows=1 loops=1,790)

6. 1.790 32.220 ↑ 304.0 1 1,790

Append (cost=1.12..5,285.47 rows=304 width=8) (actual time=0.018..0.018 rows=1 loops=1,790)

7. 30.430 30.430 ↑ 34.0 1 1,790

Index Scan using message_partition_2016_01_device_time_idx on message_partition_2016_01 m2_1 (cost=0.56..457.69 rows=34 width=8) (actual time=0.017..0.017 rows=1 loops=1,790)

  • Index Cond: ((device = m.device) AND (""time"" < '2016-02-27 21:00:42.164+02'::timestamp with time zone))
  • Filter: ((message_type = 'STATUS'::text) OR (((json ->> 'direction'::text) = 'TO_SERVER'::text) AND ((json ->> 'media'::text) = 'SMS'::text)))
  • Rows Removed by Filter: 1
8. 0.000 0.000 ↓ 0.0 0

Index Scan using message_partition_1970_01_device_time_idx on message_partition_1970_01 m2 (cost=0.56..4,826.26 rows=270 width=8) (never executed)

  • Index Cond: ((device = m.device) AND (""time"" < '2016-02-27 21:00:42.164+02'::timestamp with time zone))
  • Filter: ((message_type = 'STATUS'::text) OR (((json ->> 'direction'::text) = 'TO_SERVER'::text) AND ((json ->> 'media'::text) = 'SMS'::text)))
9. 0.300 0.300 ↑ 18.5 2 1

Index Scan Backward using message_partition_2016_01_device_time_idx on message_partition_2016_01 m_1 (cost=0.56..2,461.17 rows=37 width=936) (actual time=0.285..0.300 rows=2 loops=1)

  • Index Cond: ((device = 513) AND (""time"" < '2016-02-27 21:00:42.164+02'::timestamp with time zone))
  • Filter: (""time"" >= (SubPlan 1))
  • Rows Removed by Filter: 18
Planning time : 0.403 ms
Execution time : 453.573 ms