explain.depesz.com

PostgreSQL's explain analyze made readable

Result: OPGD

Settings
# exclusive inclusive rows x rows loops node
1. 0.034 2.752 ↓ 8.0 8 1

Hash Join (cost=90.42..94.49 rows=1 width=396) (actual time=2.736..2.752 rows=8 loops=1)

  • Hash Cond: ((((COALESCE(rides_aggr_info.user_group, 'all'::character varying)))::text = (cg.user_group)::text) AND ((rides_aggr_info.service_type)::text = (cg.service_type)::text))
  • Join Filter: (((cg.hex_id = rides_aggr_info.hex_id) OR ((cg.hex_id IS NULL) AND (rides_aggr_info.hex_id IS NULL))) AND (((cg.borough_name)::text = (rides_aggr_info.borough_name)::text) OR ((cg.borough_name IS NULL) AND (rides_aggr_info.borough_name IS NULL))))
  • Rows Removed by Join Filter: 8
2.          

CTE rides_aggr_info

3. 0.338 0.706 ↑ 6.0 5 1

Hash Join (cost=36.90..76.23 rows=30 width=510) (actual time=0.091..0.706 rows=5 loops=1)

  • Hash Cond: (b.hex_id = e.hex_id)
4. 0.305 0.305 ↑ 1.0 1,966 1

Seq Scan on borough b (cost=0.00..31.66 rows=1,966 width=17) (actual time=0.016..0.305 rows=1,966 loops=1)

5. 0.004 0.063 ↑ 6.0 5 1

Hash (cost=36.52..36.52 rows=30 width=501) (actual time=0.063..0.063 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
6. 0.013 0.059 ↑ 6.0 5 1

Hash Join (cost=23.75..36.52 rows=30 width=501) (actual time=0.054..0.059 rows=5 loops=1)

  • Hash Cond: (e.estimation_id = rr.estimation_id)
7. 0.009 0.009 ↑ 36.0 5 1

Seq Scan on estimations e (cost=0.00..11.80 rows=180 width=156) (actual time=0.008..0.009 rows=5 loops=1)

8. 0.003 0.037 ↑ 6.0 5 1

Hash (cost=23.38..23.38 rows=30 width=641) (actual time=0.037..0.037 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
9. 0.011 0.034 ↑ 6.0 5 1

Hash Join (cost=11.84..23.38 rows=30 width=641) (actual time=0.029..0.034 rows=5 loops=1)

  • Hash Cond: (ro.ride_id = rr.ride_id)
10. 0.007 0.007 ↑ 15.0 6 1

Seq Scan on ride_offers ro (cost=0.00..10.90 rows=90 width=247) (actual time=0.005..0.007 rows=6 loops=1)

11. 0.006 0.016 ↑ 7.4 5 1

Hash (cost=11.38..11.38 rows=37 width=542) (actual time=0.015..0.016 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
12. 0.010 0.010 ↑ 7.4 5 1

Seq Scan on ride_requests rr (cost=0.00..11.38 rows=37 width=542) (actual time=0.007..0.010 rows=5 loops=1)

  • Filter: (created_at <= '2018-11-29 00:00:00'::timestamp without time zone)
13. 0.030 2.646 ↑ 15.0 8 1

HashAggregate (cost=0.90..2.85 rows=120 width=318) (actual time=2.639..2.646 rows=8 loops=1)

  • Hash Key: rides_aggr_info.hex_id, rides_aggr_info.service_type, rides_aggr_info.user_group
  • Hash Key: rides_aggr_info.hex_id, rides_aggr_info.service_type
  • Hash Key: rides_aggr_info.borough_name, rides_aggr_info.service_type, rides_aggr_info.user_group
  • Hash Key: rides_aggr_info.borough_name, rides_aggr_info.service_type
14. 2.616 2.616 ↑ 6.0 5 1

CTE Scan on rides_aggr_info (cost=0.00..0.60 rows=30 width=278) (actual time=1.996..2.616 rows=5 loops=1)

15. 0.006 0.072 ↓ 2.0 8 1

Hash (cost=13.23..13.23 rows=4 width=380) (actual time=0.072..0.072 rows=8 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
16. 0.004 0.066 ↓ 2.0 8 1

Subquery Scan on cg (cost=13.11..13.23 rows=4 width=380) (actual time=0.060..0.066 rows=8 loops=1)

17. 0.020 0.062 ↓ 2.0 8 1

HashAggregate (cost=13.11..13.19 rows=4 width=470) (actual time=0.058..0.062 rows=8 loops=1)

  • Hash Key: rai.hex_id, rc.cancel_reason, (CASE rai.matched WHEN CASE_TEST_EXPR THEN 'after_matching'::cancellation_relation_to_match ELSE 'before_matching'::cancellation_relation_to_match END), rai.service_type, rai.user_group
  • Hash Key: rai.hex_id, rc.cancel_reason, (CASE rai.matched WHEN CASE_TEST_EXPR THEN 'after_matching'::cancellation_relation_to_match ELSE 'before_matching'::cancellation_relation_to_match END), rai.service_type
  • Hash Key: rai.borough_name, rc.cancel_reason, (CASE rai.matched WHEN CASE_TEST_EXPR THEN 'after_matching'::cancellation_relation_to_match ELSE 'before_matching'::cancellation_relation_to_match END), rai.service_type, rai.user_group
  • Hash Key: rai.borough_name, rc.cancel_reason, (CASE rai.matched WHEN CASE_TEST_EXPR THEN 'after_matching'::cancellation_relation_to_match ELSE 'before_matching'::cancellation_relation_to_match END), rai.service_type
18. 0.015 0.042 ↓ 3.0 3 1

Hash Join (cost=12.41..13.09 rows=1 width=430) (actual time=0.038..0.042 rows=3 loops=1)

  • Hash Cond: (rai.ride_id = rc.ride_id)
19. 0.005 0.005 ↑ 6.0 5 1

CTE Scan on rides_aggr_info rai (cost=0.00..0.60 rows=30 width=430) (actual time=0.002..0.005 rows=5 loops=1)

20. 0.004 0.022 ↓ 3.0 3 1

Hash (cost=12.40..12.40 rows=1 width=296) (actual time=0.022..0.022 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
21. 0.018 0.018 ↓ 3.0 3 1

Seq Scan on ride_cancellations rc (cost=0.00..12.40 rows=1 width=296) (actual time=0.017..0.018 rows=3 loops=1)

  • Filter: ((created_at <= '2018-11-29 00:00:00'::timestamp without time zone) AND (cancelled_by = 'customer'::ride_cancelled_by))