explain.depesz.com

PostgreSQL's explain analyze made readable

Result: lMD

Settings
# exclusive inclusive rows x rows loops node
1. 0.113 7,850.281 ↓ 36.0 36 1

Nested Loop (cost=17,948.26..35,231.94 rows=1 width=121) (actual time=2,791.787..7,850.281 rows=36 loops=1)

2.          

CTE rc

3. 85.032 85.032 ↓ 1.5 21,228 1

Index Scan using collections_2_tsrange_idx on collections (cost=0.55..14,808.65 rows=14,406 width=106) (actual time=0.202..85.032 rows=21,228 loops=1)

  • Index Cond: (tsrange(data_start, data_end, '[]'::text) && '["2019-09-30 21:17:18","2019-09-30 22:17:48"]'::tsrange)
4. 0.160 7,850.132 ↓ 36.0 36 1

Hash Join (cost=3,139.60..20,422.20 rows=1 width=268) (actual time=2,791.761..7,850.132 rows=36 loops=1)

  • Hash Cond: ((files_delete_road_20190930.collection_id = rc.id) AND (files_delete_road_20190930.collection_time = rc.collection_time))
5. 6.528 7,739.823 ↓ 18.3 55 1

Hash Join (cost=2,635.39..19,907.15 rows=3 width=236) (actual time=2,368.431..7,739.823 rows=55 loops=1)

  • Hash Cond: (files_delete_road_20190930.partition_id = partitions.id)
6. 4.393 7,685.419 ↓ 3.5 11,304 1

Nested Loop (cost=0.85..17,264.11 rows=3,238 width=95) (actual time=8.026..7,685.419 rows=11,304 loops=1)

7. 0.062 0.062 ↓ 4.0 4 1

Index Scan using datasets_name_idx on datasets (cost=0.28..2.30 rows=1 width=76) (actual time=0.038..0.062 rows=4 loops=1)

  • Index Cond: (name = ANY ('{/system_state}'::text[]))
8. 2.484 7,680.964 ↑ 5.9 2,826 4

Append (cost=0.56..17,095.77 rows=16,604 width=27) (actual time=7.457..1,920.241 rows=2,826 loops=4)

9. 4,535.980 4,535.980 ↑ 5.7 2,105 4

Index Scan using files_delete_road_20190930_dataset_id_collection_time_idx on files_delete_road_20190930 (cost=0.56..12,280.80 rows=11,988 width=27) (actual time=5.236..1,133.995 rows=2,105 loops=4)

  • Index Cond: ((dataset_id = datasets.id) AND (collection_time >= '2019-09-30 18:55:54'::timestamp without time zone) AND (collection_time <= '2019-10-01 01:45:45'::timestamp without time zone))
  • Filter: (catalog_id = 1)
10. 3,142.500 3,142.500 ↑ 6.4 721 4

Index Scan using files_delete_road_20191001_dataset_id_collection_time_idx1 on files_delete_road_20191001 (cost=0.56..4,731.95 rows=4,616 width=27) (actual time=3.511..785.625 rows=721 loops=4)

  • Index Cond: ((dataset_id = datasets.id) AND (collection_time >= '2019-09-30 18:55:54'::timestamp without time zone) AND (collection_time <= '2019-10-01 01:45:45'::timestamp without time zone))
  • Filter: (catalog_id = 1)
11. 0.049 47.876 ↑ 16.2 129 1

Hash (cost=2,608.38..2,608.38 rows=2,093 width=149) (actual time=47.876..47.876 rows=129 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 58kB
12. 36.225 47.827 ↑ 16.2 129 1

Bitmap Heap Scan on partitions (cost=527.22..2,608.38 rows=2,093 width=149) (actual time=44.211..47.827 rows=129 loops=1)

  • Recheck Cond: (metadata @> '{"vin": "5G21A6P05L4100051"}'::jsonb)
  • Heap Blocks: exact=364
13. 11.602 11.602 ↑ 5.4 388 1

Bitmap Index Scan on partitions_metadata_idx (cost=0.00..526.70 rows=2,093 width=0) (actual time=11.602..11.602 rows=388 loops=1)

  • Index Cond: (metadata @> '{"vin": "5G21A6P05L4100051"}'::jsonb)
14. 10.862 110.149 ↓ 1.5 21,228 1

Hash (cost=288.12..288.12 rows=14,406 width=64) (actual time=110.149..110.149 rows=21,228 loops=1)

  • Buckets: 32768 (originally 16384) Batches: 1 (originally 1) Memory Usage: 2910kB
15. 99.287 99.287 ↓ 1.5 21,228 1

CTE Scan on rc (cost=0.00..288.12 rows=14,406 width=64) (actual time=0.204..99.287 rows=21,228 loops=1)

16. 0.036 0.036 ↑ 1.0 1 36

Seq Scan on catalogs (cost=0.00..1.02 rows=1 width=2) (actual time=0.001..0.001 rows=1 loops=36)

  • Filter: (id = 1)
  • Rows Removed by Filter: 1
Planning time : 73.911 ms
Execution time : 7,851.176 ms