#	exclusive	inclusive	rows x	rows	loops	node
1.	84.713	7,170.498	↓ 6.8	1,353	1	→ GroupAggregate (cost=378,770.36..404,079.89 rows=200 width=44) (actual time=6,556.081..7,170.498 rows=1,353 loops=1) Group Key: (date_trunc('HOUR'::text, o.creation_date)) Buffers: shared hit=1472809 read=114569 dirtied=22728, temp read=1006 written=1006
2.	490.298	7,085.785	↓ 1.1	159,630	1	→ GroupAggregate (cost=378,770.36..399,610.62 rows=148,859 width=36) (actual time=6,555.938..7,085.785 rows=159,630 loops=1) Group Key: (date_trunc('HOUR'::text, o.creation_date)), o.number, evtpyxis.order_id Buffers: shared hit=1472809 read=114569 dirtied=22728, temp read=1006 written=1006
3.	410.003	6,595.487	↓ 1.1	164,832	1	→ Sort (cost=378,770.36..379,142.51 rows=148,859 width=36) (actual time=6,555.857..6,595.487 rows=164,832 loops=1) Sort Key: (date_trunc('HOUR'::text, o.creation_date)), o.number, evtpyxis.order_id Sort Method: external merge Disk: 8040kB Buffers: shared hit=1472809 read=114569 dirtied=22728, temp read=1006 written=1006
4.	61.143	6,185.484	↓ 1.1	164,832	1	→ Nested Loop (cost=33,997.39..365,980.70 rows=148,859 width=36) (actual time=2,108.997..6,185.484 rows=164,832 loops=1) Buffers: shared hit=1472798 read=114569 dirtied=22728
5.	72.686	5,645.313	↓ 1.1	159,676	1	→ Nested Loop (cost=33,996.96..295,313.10 rows=145,929 width=36) (actual time=2,108.394..5,645.313 rows=159,676 loops=1) Buffers: shared hit=831923 read=113748 dirtied=22722
6.	708.545	4,774.247	↓ 1.1	159,676	1	→ Hash Right Join (cost=33,996.53..178,813.76 rows=145,929 width=32) (actual time=2,107.169..4,774.247 rows=159,676 loops=1) Hash Cond: (evtpyxis.order_id = o.id) Buffers: shared hit=247791 read=95705 dirtied=21839
7.	1,958.649	1,958.649	↓ 1.0	523,009	1	→ Seq Scan on order_status_event evtpyxis (cost=0.00..137,514.59 rows=518,566 width=12) (actual time=0.020..1,958.649 rows=523,009 loops=1) Filter: ((final_status)::text = 'RELEASED'::text) Rows Removed by Filter: 4881867 Buffers: shared hit=10554 read=58488 dirtied=20088
8.	2,107.053	2,107.053	↓ 1.1	159,676	1	→ Hash (cost=32,172.41..32,172.41 rows=145,929 width=24) (actual time=2,107.053..2,107.053 rows=159,676 loops=1) Buckets: 16384 Batches: 1 Memory Usage: 8733kB Buffers: shared hit=237237 read=37217 dirtied=1751 -> Index Scan using order_creation_date_index on order_ o (cost=0.43..32172.41 rows=145929 width=24) (actual time=0.709..2048.868 rows= Index Cond: ((creation_date >= '2018-09-29 00:00:00'::timestamp without time zone) AND (creation_date <= '2018-11-30 00:00:00'::tim Filter: (((type)::text = 'RE'::text) AND (date_part('hour'::text, creation_date) <> ALL ('{22,23}'::double precision[]))) Rows Removed by Filter: 138000 Buffers: shared hit=237237 read=37217 dirtied=1751
9.	798.380	798.380	↑ 1.0	1	159,676	→ Index Only Scan using project_pkey on project p (cost=0.43..0.79 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=159,676) Index Cond: (id = o.project_id) Heap Fetches: 121669 Buffers: shared hit=584132 read=18043 dirtied=883
10.	479.028	479.028	↑ 1.0	1	159,676	→ Index Scan using index_payment_project_id on payment pay (cost=0.43..0.47 rows=1 width=12) (actual time=0.002..0.003 rows=1 loops=159,676) Index Cond: (project_id = p.id) Filter: initial Buffers: shared hit=640875 read=821 dirtied=6

                          QUERY PLAN

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------
 GroupAggregate  (cost=378770.36..404079.89 rows=200 width=44) (actual time=6556.081..7170.498 rows=1353 loops=1)
   Group Key: (date_trunc('HOUR'::text, o.creation_date))
   Buffers: shared hit=1472809 read=114569 dirtied=22728, temp read=1006 written=1006
   ->  GroupAggregate  (cost=378770.36..399610.62 rows=148859 width=36) (actual time=6555.938..7085.785 rows=159630 loops=1)
         Group Key: (date_trunc('HOUR'::text, o.creation_date)), o.number, evtpyxis.order_id
         Buffers: shared hit=1472809 read=114569 dirtied=22728, temp read=1006 written=1006
         ->  Sort  (cost=378770.36..379142.51 rows=148859 width=36) (actual time=6555.857..6595.487 rows=164832 loops=1)
               Sort Key: (date_trunc('HOUR'::text, o.creation_date)), o.number, evtpyxis.order_id
               Sort Method: external merge  Disk: 8040kB
               Buffers: shared hit=1472809 read=114569 dirtied=22728, temp read=1006 written=1006
               ->  Nested Loop  (cost=33997.39..365980.70 rows=148859 width=36) (actual time=2108.997..6185.484 rows=164832 loops=1)
                     Buffers: shared hit=1472798 read=114569 dirtied=22728
                     ->  Nested Loop  (cost=33996.96..295313.10 rows=145929 width=36) (actual time=2108.394..5645.313 rows=159676 loops=1)
                           Buffers: shared hit=831923 read=113748 dirtied=22722
                           ->  Hash Right Join  (cost=33996.53..178813.76 rows=145929 width=32) (actual time=2107.169..4774.247 rows=159676 loops=1)
                                 Hash Cond: (evtpyxis.order_id = o.id)
                                 Buffers: shared hit=247791 read=95705 dirtied=21839
                                 ->  Seq Scan on order_status_event evtpyxis  (cost=0.00..137514.59 rows=518566 width=12) (actual time=0.020..1958.649 rows=523009 loops=1)
                                       Filter: ((final_status)::text = 'RELEASED'::text)
                                       Rows Removed by Filter: 4881867
                                       Buffers: shared hit=10554 read=58488 dirtied=20088
                                 ->  Hash  (cost=32172.41..32172.41 rows=145929 width=24) (actual time=2107.053..2107.053 rows=159676 loops=1)
                                       Buckets: 16384  Batches: 1  Memory Usage: 8733kB
                                       Buffers: shared hit=237237 read=37217 dirtied=1751
                                       ->  Index Scan using order_creation_date_index on order_ o  (cost=0.43..32172.41 rows=145929 width=24) (actual time=0.709..2048.868 rows=
159676 loops=1)
                                             Index Cond: ((creation_date >= '2018-09-29 00:00:00'::timestamp without time zone) AND (creation_date <= '2018-11-30 00:00:00'::tim
estamp without time zone))
                                             Filter: (((type)::text = 'RE'::text) AND (date_part('hour'::text, creation_date) <> ALL ('{22,23}'::double precision[])))
                                             Rows Removed by Filter: 138000
                                             Buffers: shared hit=237237 read=37217 dirtied=1751
                           ->  Index Only Scan using project_pkey on project p  (cost=0.43..0.79 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=159676)
                                 Index Cond: (id = o.project_id)
                                 Heap Fetches: 121669
                                 Buffers: shared hit=584132 read=18043 dirtied=883
                     ->  Index Scan using index_payment_project_id on payment pay  (cost=0.43..0.47 rows=1 width=12) (actual time=0.002..0.003 rows=1 loops=159676)
                           Index Cond: (project_id = p.id)
                           Filter: initial
                           Buffers: shared hit=640875 read=821 dirtied=6
 Planning time: 3.761 ms
 Execution time: 7174.846 ms
(39 rows)

Per node type stats

node type	count	sum of times	% of query
GroupAggregate	2	575.011 ms	8.0 %
Hash	1	2,107.053 ms	29.4 %
Hash Right Join	1	708.545 ms	9.9 %
Index Only Scan	1	798.380 ms	11.1 %
Index Scan	1	479.028 ms	6.7 %
Nested Loop	2	133.829 ms	1.9 %
Seq Scan	1	1,958.649 ms	27.3 %
Sort	1	410.003 ms	5.7 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
order_status_event	1	1,958.649 ms	27.3 %
Seq Scan	1	1,958.649 ms	100.0 %
payment	1	479.028 ms	6.7 %
Index Scan	1	479.028 ms	100.0 %
project	1	798.380 ms	11.1 %
Index Only Scan	1	798.380 ms	100.0 %

explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8kGZ

Color mode:

Visible columns:

Optimization(s) for this plan:

Per node type stats

Per table stats