#	exclusive	inclusive	rows x	rows	loops	node
1.	96.622	3,213.053	↓ 2.0	2	1	→ Gather (cost=269,314.70..390,148.13 rows=1 width=16) (actual time=3,145.344..3,213.053 rows=2 loops=1) Workers Planned: 2 Workers Launched: 2 JIT: Functions: 96 Options: Inlining false, Optimization false, Expressions true, Deforming true Timing: Generation 10.304 ms, Inlining 0.000 ms, Optimization 2.689 ms, Emission 53.866 ms, Total 66.859 ms
2.	263.406	3,116.431	↑ 1.0	1	3 / 3	→ Parallel Hash Join (cost=268,314.70..389,148.03 rows=1 width=16) (actual time=3,056.417..3,116.431 rows=1 loops=3) Hash Cond: (((trip_fare_25.medallion)::text = (trip_data_25.medallion)::text) AND ((trip_fare_25.hack_license)::text = (trip_data_25.hack_license)::text) AND ((trip_fare_25.vendor_id)::text = (trip_data_25.vendor_id)::text) AND (trip_fare_25.pickup_datetime = trip_data_25.pickup_datetime))
3.	362.988	2,072.755	↑ 346,270.0	1	3 / 3	→ Hash Join (cost=130,050.77..217,409.26 rows=346,270 width=78) (actual time=1,961.336..2,072.755 rows=1 loops=3) Hash Cond: ((date_trunc('month'::text, trip_fare_25.pickup_datetime) = (date_trunc('month'::text, trip_fare_25_1.pickup_datetime))) AND (trip_fare_25.total_amount = (max(trip_fare_25_1.total_amount))))
4.	116.513	116.513	↑ 1.2	1,231,385	3 / 3	→ Parallel Seq Scan on trip_fare_25 (cost=0.00..79,085.12 rows=1,539,212 width=82) (actual time=0.061..116.513 rows=1,231,385 loops=3)
5.	0.005	1,593.254	↑ 45,623.0	1	3 / 3	→ Hash (cost=129,366.43..129,366.43 rows=45,623 width=12) (actual time=1,593.254..1,593.254 rows=1 loops=3) Buckets: 65,536 Batches: 1 Memory Usage: 513kB
6.	525.611	1,593.249	↑ 45,623.0	1	3 / 3	→ HashAggregate (cost=128,339.91..128,910.20 rows=45,623 width=12) (actual time=1,593.049..1,593.249 rows=1 loops=3) Group Key: date_trunc('month'::text, trip_fare_25_1.pickup_datetime)
7.	1,067.638	1,067.638	↓ 1.0	3,694,154	3 / 3	→ Seq Scan on trip_fare_25 trip_fare_25_1 (cost=0.00..109,869.36 rows=3,694,109 width=12) (actual time=0.045..1,067.638 rows=3,694,154 loops=3)
8.	492.213	780.270	↑ 1.2	1,231,385	3 / 3	→ Parallel Hash (cost=86,434.31..86,434.31 rows=1,539,231 width=86) (actual time=780.270..780.270 rows=1,231,385 loops=3) Buckets: 32,768 Batches: 128 Memory Usage: 3,744kB
9.	288.057	288.057	↑ 1.2	1,231,385	3 / 3	→ Parallel Seq Scan on trip_data_25 (cost=0.00..86,434.31 rows=1,539,231 width=86) (actual time=19.202..288.057 rows=1,231,385 loops=3)

Planning time	:	1.580 ms
Execution time	:	3,219.106 ms

                                                                                                                                                 QUERY PLAN                                              
                                                                                                   
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
 Gather  (cost=269314.70..390148.13 rows=1 width=16) (actual time=3145.344..3213.053 rows=2 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Hash Join  (cost=268314.70..389148.03 rows=1 width=16) (actual time=3056.417..3116.431 rows=1 loops=3)
         Hash Cond: (((trip_fare_25.medallion)::text = (trip_data_25.medallion)::text) AND ((trip_fare_25.hack_license)::text = (trip_data_25.hack_license)::text) AND ((trip_fare_25.vendor_id)::text = 
(trip_data_25.vendor_id)::text) AND (trip_fare_25.pickup_datetime = trip_data_25.pickup_datetime))
         ->  Hash Join  (cost=130050.77..217409.26 rows=346270 width=78) (actual time=1961.336..2072.755 rows=1 loops=3)
               Hash Cond: ((date_trunc('month'::text, trip_fare_25.pickup_datetime) = (date_trunc('month'::text, trip_fare_25_1.pickup_datetime))) AND (trip_fare_25.total_amount = (max(trip_fare_25_1.t
otal_amount))))
               ->  Parallel Seq Scan on trip_fare_25  (cost=0.00..79085.12 rows=1539212 width=82) (actual time=0.061..116.513 rows=1231385 loops=3)
               ->  Hash  (cost=129366.43..129366.43 rows=45623 width=12) (actual time=1593.254..1593.254 rows=1 loops=3)
                     Buckets: 65536  Batches: 1  Memory Usage: 513kB
                     ->  HashAggregate  (cost=128339.91..128910.20 rows=45623 width=12) (actual time=1593.049..1593.249 rows=1 loops=3)
                           Group Key: date_trunc('month'::text, trip_fare_25_1.pickup_datetime)
                           ->  Seq Scan on trip_fare_25 trip_fare_25_1  (cost=0.00..109869.36 rows=3694109 width=12) (actual time=0.045..1067.638 rows=3694154 loops=3)
         ->  Parallel Hash  (cost=86434.31..86434.31 rows=1539231 width=86) (actual time=780.270..780.270 rows=1231385 loops=3)
               Buckets: 32768  Batches: 128  Memory Usage: 3744kB
               ->  Parallel Seq Scan on trip_data_25  (cost=0.00..86434.31 rows=1539231 width=86) (actual time=19.202..288.057 rows=1231385 loops=3)
 Planning Time: 1.580 ms
 JIT:
   Functions: 96
   Options: Inlining false, Optimization false, Expressions true, Deforming true
   Timing: Generation 10.304 ms, Inlining 0.000 ms, Optimization 2.689 ms, Emission 53.866 ms, Total 66.859 ms
 Execution Time: 3219.106 ms
(22 rows)

Per node type stats

node type	count	sum of times	% of query
Gather	1	96.622 ms	3.0 %
Hash	1	0.005 ms	0.0 %
Hash Join	1	362.988 ms	11.3 %
HashAggregate	1	525.611 ms	16.4 %
Parallel Hash	1	492.213 ms	15.3 %
Parallel Hash Join	1	263.406 ms	8.2 %
Parallel Seq Scan	2	404.570 ms	12.6 %
Seq Scan	1	1,067.638 ms	33.2 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
trip_data_25	1	288.057 ms	9.0 %
Parallel Seq Scan	1	288.057 ms	100.0 %
trip_fare_25	2	1,184.151 ms	36.9 %
Parallel Seq Scan	1	116.513 ms	9.8 %
Seq Scan	1	1,067.638 ms	90.2 %

explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BhLe

Color mode:

Visible columns:

Per node type stats

Per table stats