explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bL5Mr

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 101.091 ↓ 111.0 2,109 1

Nested Loop (cost=618.38..3,726.75 rows=19 width=121) (actual time=13.547..101.091 rows=2,109 loops=1)

2.          

CTE rc

3. 6.614 6.614 ↓ 2.4 1,096 1

Index Scan using collections_2_tsrange_idx on collections (cost=0.42..471.44 rows=458 width=53) (actual time=0.173..6.614 rows=1,096 loops=1)

  • Index Cond: (tsrange(data_start, data_end, '[]'::text) && '["2019-07-24 17:09:25","2019-07-24 17:09:45"]'::tsrange)
4.          

Initplan (forNested Loop)

5. 0.127 7.025 ↑ 1.0 1 1

Aggregate (cost=10.30..10.31 rows=1 width=8) (actual time=7.024..7.025 rows=1 loops=1)

6. 6.898 6.898 ↓ 2.4 1,096 1

CTE Scan on rc rc_1 (cost=0.00..9.16 rows=458 width=8) (actual time=0.000..6.898 rows=1,096 loops=1)

7. 0.096 0.192 ↑ 1.0 1 1

Aggregate (cost=10.30..10.31 rows=1 width=8) (actual time=0.192..0.192 rows=1 loops=1)

8. 0.096 0.096 ↓ 2.4 1,096 1

CTE Scan on rc rc_2 (cost=0.00..9.16 rows=458 width=8) (actual time=0.001..0.096 rows=1,096 loops=1)

9. 0.018 0.018 ↑ 1.0 1 1

Seq Scan on catalogs (cost=0.00..1.06 rows=1 width=34) (actual time=0.013..0.018 rows=1 loops=1)

  • Filter: (id = 1)
  • Rows Removed by Filter: 4
10. 1.051 96.255 ↓ 111.0 2,109 1

Nested Loop (cost=126.32..3,232.29 rows=19 width=171) (actual time=13.503..96.255 rows=2,109 loops=1)

11. 4.240 90.986 ↓ 111.0 2,109 1

Hash Join (cost=126.04..3,226.59 rows=19 width=103) (actual time=13.482..90.986 rows=2,109 loops=1)

  • Hash Cond: (files.partition_id = partitions.id)
12. 14.603 86.330 ↓ 20.7 49,816 1

Nested Loop (cost=0.56..3,094.80 rows=2,406 width=59) (actual time=7.495..86.330 rows=49,816 loops=1)

13. 0.487 0.487 ↓ 2.4 1,096 1

CTE Scan on rc (cost=0.00..9.16 rows=458 width=56) (actual time=0.175..0.487 rows=1,096 loops=1)

14. 71.240 71.240 ↓ 9.0 45 1,096

Index Scan using files_test_collection_id_collection_time_idx on files_test files (cost=0.56..6.69 rows=5 width=19) (actual time=0.048..0.065 rows=45 loops=1,096)

  • Index Cond: ((collection_id = rc.id) AND (collection_time >= $1) AND (collection_time <= $2) AND (collection_time > '2019-07-24 00:00:00'::timestamp without time zone))
  • Filter: (catalog_id = 1)
15. 0.037 0.416 ↑ 1.0 171 1

Hash (cost=123.33..123.33 rows=171 width=52) (actual time=0.416..0.416 rows=171 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 23kB
16. 0.379 0.379 ↑ 1.0 171 1

Index Scan using partitions_vin_id_idx on partitions (cost=0.29..123.33 rows=171 width=52) (actual time=0.031..0.379 rows=171 loops=1)

  • Index Cond: (vin = '5G21A6P01L4100015'::text)
17. 4.218 4.218 ↑ 1.0 1 2,109

Index Scan using datasets_pkey on datasets (cost=0.28..0.30 rows=1 width=76) (actual time=0.002..0.002 rows=1 loops=2,109)

  • Index Cond: (id = files.dataset_id)
Planning time : 1.227 ms
Execution time : 101.399 ms