#	exclusive	inclusive	rows x	rows	loops	node
1.	0.014	323694.308	↑ 1.0	10	1	→ Hash Left Join (cost=12463520.78..12463521.03 rows=10 width=49) (actual time=323694.301..323694.308 rows=10 loops=1) Hash Cond: (sample.invno = rejections_0_14_days.invno)
2.						CTE sample
3.	0.006	0.038	↑ 1.0	10	1	→ Limit (cost=0.00..0.45 rows=10 width=8) (actual time=0.012..0.038 rows=10 loops=1)
4.	0.032	0.032	↑ 3520414.1	10	1	→ Seq Scan on invoice (cost=0.00..1594783.41 rows=35204141 width=8) (actual time=0.010..0.032 rows=10 loops=1)
5.						CTE sample_cids
6.	0.031	80.708	↑ 1.0	10	1	→ HashAggregate (cost=206.93..207.03 rows=10 width=8) (actual time=80.700..80.708 rows=10 loops=1)
7.	0.038	80.677	↑ 1.0	10	1	→ Nested Loop (cost=0.00..206.90 rows=10 width=8) (actual time=0.046..80.677 rows=10 loops=1)
8.	0.032	31.689	↑ 1.0	10	1	→ Nested Loop (cost=0.00..171.89 rows=10 width=14) (actual time=0.019..31.689 rows=10 loops=1)
9.	0.037	0.037	↑ 1.0	10	1	→ CTE Scan on sample (cost=0.00..0.20 rows=10 width=8) (actual time=0.001..0.037 rows=10 loops=1)
10.	31.620	31.620	↑ 1.0	1	10	→ Index Scan using invoice_invno_index on invoice (cost=0.00..17.16 rows=1 width=22) (actual time=3.162..3.162 rows=1 loops=10) Index Cond: (invno = sample.invno)
11.	48.950	48.950	↑ 1.0	1	10	→ Index Scan using customer_pno_index on customer (cost=0.00..3.49 rows=1 width=24) (actual time=4.894..4.895 rows=1 loops=10) Index Cond: (pno = public.invoice.pno)
12.						CTE old_rejections
13.	690.781	7096.124	↑ 2.3	61	1	→ Hash Join (cost=1007.03..369486.94 rows=142 width=51) (actual time=381.620..7096.124 rows=61 loops=1) Hash Cond: (rejection.pno = public.customer.pno)
14.	6261.890	6261.890	↓ 1.0	7577476	1	→ Seq Scan on rejection (cost=0.00..340821.19 rows=7372677 width=58) (actual time=18.155..6261.890 rows=7577476 loops=1) Filter: (reason IS NOT NULL)
15.	0.030	143.453	↑ 21.5	11	1	→ Hash (cost=1004.08..1004.08 rows=236 width=24) (actual time=143.453..143.453 rows=11 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 1kB
16.	0.036	143.423	↑ 21.5	11	1	→ Nested Loop (cost=0.00..1004.08 rows=236 width=24) (actual time=85.957..143.423 rows=11 loops=1)
17.	80.727	80.727	↑ 1.0	10	1	→ CTE Scan on sample_cids (cost=0.00..0.20 rows=10 width=8) (actual time=80.702..80.727 rows=10 loops=1)
18.	62.660	62.660	↑ 24.0	1	10	→ Index Scan using customer_cid_idx on customer (cost=0.00..100.09 rows=24 width=24) (actual time=6.265..6.266 rows=1 loops=10) Index Cond: (cid = sample_cids.cid)
19.						CTE payments
20.	5336.726	306680.168	↑ 1.1	27670712	1	→ Group (cost=11148902.33..11375432.30 rows=30203996 width=16) (actual time=267071.189..306680.168 rows=27670712 loops=1)
21.	88724.779	301343.442	↑ 1.0	30179254	1	→ Sort (cost=11148902.33..11224412.32 rows=30203996 width=16) (actual time=267071.185..301343.442 rows=30179254 loops=1) Sort Key: public.customer.cid, public.inv_paid_hist.date Sort Method: external merge Disk: 760456kB
22.	73886.514	212618.663	↑ 1.0	30179254	1	→ Hash Join (cost=2742721.08..6880138.71 rows=30203996 width=16) (actual time=46615.192..212618.663 rows=30179254 loops=1) Hash Cond: (public.invoice.pno = public.customer.pno)
23.	66576.975	126619.982	↑ 1.0	30179122	1	→ Hash Join (cost=2241110.17..5122141.91 rows=30203996 width=22) (actual time=34498.868..126619.982 rows=30179122 loops=1) Hash Cond: (public.inv_paid_hist.invno = public.invoice.invno)
24.	25552.430	25552.430	↑ 1.0	30179122	1	→ Seq Scan on inv_paid_hist (cost=0.00..680819.96 rows=30203996 width=16) (actual time=4.545..25552.430 rows=30179122 loops=1)
25.	8183.324	34490.577	↑ 1.0	34848073	1	→ Hash (cost=1594783.41..1594783.41 rows=35204141 width=22) (actual time=34490.577..34490.577 rows=34848073 loops=1) Buckets: 1048576 Batches: 4 Memory Usage: 463014kB
26.	26307.253	26307.253	↑ 1.0	34848073	1	→ Seq Scan on invoice (cost=0.00..1594783.41 rows=35204141 width=22) (actual time=0.010..26307.253 rows=34848073 loops=1)
27.	3379.187	12112.167	↓ 1.0	12284213	1	→ Hash (cost=276568.96..276568.96 rows=12257596 width=24) (actual time=12112.167..12112.167 rows=12284213 loops=1) Buckets: 1048576 Batches: 2 Memory Usage: 339167kB
28.	8732.980	8732.980	↓ 1.0	12284213	1	→ Seq Scan on customer (cost=0.00..276568.96 rows=12257596 width=24) (actual time=0.004..8732.980 rows=12284213 loops=1)
29.						CTE paid_since_rejected
30.	0.003	316507.619	↓ 0.0	0	1	→ HashAggregate (cost=718182.35..718182.36 rows=1 width=8) (actual time=316507.619..316507.619 rows=0 loops=1)
31.	0.082	316507.616	↓ 0.0	0	1	→ Nested Loop (cost=207.03..718182.35 rows=1 width=8) (actual time=316507.616..316507.616 rows=0 loops=1) Join Filter: ((payments.pay_date > o.rej_date) AND (public.customer.cid = o.cid) AND ((public.invoice.create_date - o.rej_date) < '14 days'::interval))
32.	0.006	0.006	↑ 2.3	61	1	→ CTE Scan on old_rejections o (cost=0.00..2.84 rows=142 width=16) (actual time=0.001..0.006 rows=61 loops=1)
33.	0.051	316507.528	↑ 17.6	11	61	→ Materialize (cost=207.03..717560.16 rows=194 width=40) (actual time=4381.125..5188.648 rows=11 loops=61)
34.	2239.674	316507.477	↑ 17.6	11	1	→ Hash Join (cost=207.03..717559.19 rows=194 width=40) (actual time=267248.614..316507.477 rows=11 loops=1) Hash Cond: (payments.cid = public.customer.cid) Join Filter: (payments.pay_date < public.invoice.create_date)
35.	314267.563	314267.563	↑ 1.1	27670712	1	→ CTE Scan on payments (cost=0.00..604079.92 rows=30203996 width=16) (actual time=267071.192..314267.563 rows=27670712 loops=1)
36.	0.003	0.240	↑ 1.0	10	1	→ Hash (cost=206.90..206.90 rows=10 width=24) (actual time=0.240..0.240 rows=10 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 1kB
37.	0.006	0.237	↑ 1.0	10	1	→ Nested Loop (cost=0.00..206.90 rows=10 width=24) (actual time=0.061..0.237 rows=10 loops=1)
38.	0.008	0.091	↑ 1.0	10	1	→ Nested Loop (cost=0.00..171.89 rows=10 width=30) (actual time=0.035..0.091 rows=10 loops=1)
39.	0.003	0.003	↑ 1.0	10	1	→ CTE Scan on sample (cost=0.00..0.20 rows=10 width=8) (actual time=0.002..0.003 rows=10 loops=1)
40.	0.080	0.080	↑ 1.0	1	10	→ Index Scan using invoice_invno_index on invoice (cost=0.00..17.16 rows=1 width=30) (actual time=0.008..0.008 rows=1 loops=10) Index Cond: (invno = sample.invno)
41.	0.140	0.140	↑ 1.0	1	10	→ Index Scan using customer_pno_index on customer (cost=0.00..3.49 rows=1 width=24) (actual time=0.013..0.014 rows=1 loops=10) Index Cond: (pno = public.invoice.pno)
42.						CTE rejections_0_14_days
43.	0.011	323694.274	↓ 2.0	2	1	→ HashAggregate (cost=211.66..211.67 rows=1 width=64) (actual time=323694.274..323694.274 rows=2 loops=1)
44.	0.030	323694.263	↓ 6.0	6	1	→ WindowAgg (cost=211.61..211.65 rows=1 width=64) (actual time=323694.256..323694.263 rows=6 loops=1)
45.	0.011	323694.233	↓ 6.0	6	1	→ Sort (cost=211.61..211.62 rows=1 width=64) (actual time=323694.232..323694.233 rows=6 loops=1) Sort Key: public.invoice.invno, o.rej_date Sort Method: quicksort Memory: 25kB
46.	0.002	323694.222	↓ 6.0	6	1	→ Nested Loop Left Join (cost=4.62..211.60 rows=1 width=64) (actual time=323694.102..323694.222 rows=6 loops=1) Join Filter: (sample.invno = ps.invno)
47.	0.070	7186.600	↓ 6.0	6	1	→ Hash Join (cost=4.62..211.57 rows=1 width=64) (actual time=7186.481..7186.600 rows=6 loops=1) Hash Cond: (public.customer.cid = o.cid) Join Filter: ((o.rej_date < public.invoice.create_date) AND ((public.invoice.create_date - o.rej_date) < '14 days'::interval))
48.	0.020	89.947	↑ 1.0	10	1	→ Nested Loop (cost=0.00..206.90 rows=10 width=32) (actual time=89.713..89.947 rows=10 loops=1)
49.	0.022	38.787	↑ 1.0	10	1	→ Nested Loop (cost=0.00..171.89 rows=10 width=38) (actual time=38.690..38.787 rows=10 loops=1)
50.	0.005	0.005	↑ 1.0	10	1	→ CTE Scan on sample (cost=0.00..0.20 rows=10 width=8) (actual time=0.001..0.005 rows=10 loops=1)
51.	38.760	38.760	↑ 1.0	1	10	→ Index Scan using invoice_invno_index on invoice (cost=0.00..17.16 rows=1 width=30) (actual time=3.876..3.876 rows=1 loops=10) Index Cond: (invno = sample.invno)
52.	51.140	51.140	↑ 1.0	1	10	→ Index Scan using customer_pno_index on customer (cost=0.00..3.49 rows=1 width=24) (actual time=5.114..5.114 rows=1 loops=10) Index Cond: (pno = public.invoice.pno)
53.	0.107	7096.583	↑ 2.3	61	1	→ Hash (cost=2.84..2.84 rows=142 width=48) (actual time=7096.583..7096.583 rows=61 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 5kB
54.	7096.476	7096.476	↑ 2.3	61	1	→ CTE Scan on old_rejections o (cost=0.00..2.84 rows=142 width=48) (actual time=381.626..7096.476 rows=61 loops=1)
55.	316507.620	316507.620	↓ 0.0	0	6	→ CTE Scan on paid_since_rejected ps (cost=0.00..0.02 rows=1 width=8) (actual time=52751.270..52751.270 rows=0 loops=6)
56.	0.015	0.015	↑ 1.0	10	1	→ CTE Scan on sample (cost=0.00..0.20 rows=10 width=8) (actual time=0.014..0.015 rows=10 loops=1)
57.	0.001	323694.279	↓ 2.0	2	1	→ Hash (cost=0.02..0.02 rows=1 width=49) (actual time=323694.279..323694.279 rows=2 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 1kB
58.	323694.278	323694.278	↓ 2.0	2	1	→ CTE Scan on rejections_0_14_days (cost=0.00..0.02 rows=1 width=49) (actual time=323694.276..323694.278 rows=2 loops=1)

Hash Left Join  (cost=12463520.78..12463521.03 rows=10 width=49) (actual time=323694.301..323694.308 rows=10 loops=1)
  Hash Cond: (sample.invno = rejections_0_14_days.invno)
  CTE sample
    ->  Limit  (cost=0.00..0.45 rows=10 width=8) (actual time=0.012..0.038 rows=10 loops=1)
          ->  Seq Scan on invoice  (cost=0.00..1594783.41 rows=35204141 width=8) (actual time=0.010..0.032 rows=10 loops=1)
  CTE sample_cids
    ->  HashAggregate  (cost=206.93..207.03 rows=10 width=8) (actual time=80.700..80.708 rows=10 loops=1)
          ->  Nested Loop  (cost=0.00..206.90 rows=10 width=8) (actual time=0.046..80.677 rows=10 loops=1)
                ->  Nested Loop  (cost=0.00..171.89 rows=10 width=14) (actual time=0.019..31.689 rows=10 loops=1)
                      ->  CTE Scan on sample  (cost=0.00..0.20 rows=10 width=8) (actual time=0.001..0.037 rows=10 loops=1)
                      ->  Index Scan using invoice_invno_index on invoice  (cost=0.00..17.16 rows=1 width=22) (actual time=3.162..3.162 rows=1 loops=10)
                            Index Cond: (invno = sample.invno)
                ->  Index Scan using customer_pno_index on customer  (cost=0.00..3.49 rows=1 width=24) (actual time=4.894..4.895 rows=1 loops=10)
                      Index Cond: (pno = public.invoice.pno)
  CTE old_rejections
    ->  Hash Join  (cost=1007.03..369486.94 rows=142 width=51) (actual time=381.620..7096.124 rows=61 loops=1)
          Hash Cond: (rejection.pno = public.customer.pno)
          ->  Seq Scan on rejection  (cost=0.00..340821.19 rows=7372677 width=58) (actual time=18.155..6261.890 rows=7577476 loops=1)
                Filter: (reason IS NOT NULL)
          ->  Hash  (cost=1004.08..1004.08 rows=236 width=24) (actual time=143.453..143.453 rows=11 loops=1)
                Buckets: 1024  Batches: 1  Memory Usage: 1kB
                ->  Nested Loop  (cost=0.00..1004.08 rows=236 width=24) (actual time=85.957..143.423 rows=11 loops=1)
                      ->  CTE Scan on sample_cids  (cost=0.00..0.20 rows=10 width=8) (actual time=80.702..80.727 rows=10 loops=1)
                      ->  Index Scan using customer_cid_idx on customer  (cost=0.00..100.09 rows=24 width=24) (actual time=6.265..6.266 rows=1 loops=10)
                            Index Cond: (cid = sample_cids.cid)
  CTE payments
    ->  Group  (cost=11148902.33..11375432.30 rows=30203996 width=16) (actual time=267071.189..306680.168 rows=27670712 loops=1)
          ->  Sort  (cost=11148902.33..11224412.32 rows=30203996 width=16) (actual time=267071.185..301343.442 rows=30179254 loops=1)
                Sort Key: public.customer.cid, public.inv_paid_hist.date
                Sort Method: external merge  Disk: 760456kB
                ->  Hash Join  (cost=2742721.08..6880138.71 rows=30203996 width=16) (actual time=46615.192..212618.663 rows=30179254 loops=1)
                      Hash Cond: (public.invoice.pno = public.customer.pno)
                      ->  Hash Join  (cost=2241110.17..5122141.91 rows=30203996 width=22) (actual time=34498.868..126619.982 rows=30179122 loops=1)
                            Hash Cond: (public.inv_paid_hist.invno = public.invoice.invno)
                            ->  Seq Scan on inv_paid_hist  (cost=0.00..680819.96 rows=30203996 width=16) (actual time=4.545..25552.430 rows=30179122 loops=1)
                            ->  Hash  (cost=1594783.41..1594783.41 rows=35204141 width=22) (actual time=34490.577..34490.577 rows=34848073 loops=1)
                                  Buckets: 1048576  Batches: 4  Memory Usage: 463014kB
                                  ->  Seq Scan on invoice  (cost=0.00..1594783.41 rows=35204141 width=22) (actual time=0.010..26307.253 rows=34848073 loops=1)
                      ->  Hash  (cost=276568.96..276568.96 rows=12257596 width=24) (actual time=12112.167..12112.167 rows=12284213 loops=1)
                            Buckets: 1048576  Batches: 2  Memory Usage: 339167kB
                            ->  Seq Scan on customer  (cost=0.00..276568.96 rows=12257596 width=24) (actual time=0.004..8732.980 rows=12284213 loops=1)
  CTE paid_since_rejected
    ->  HashAggregate  (cost=718182.35..718182.36 rows=1 width=8) (actual time=316507.619..316507.619 rows=0 loops=1)
          ->  Nested Loop  (cost=207.03..718182.35 rows=1 width=8) (actual time=316507.616..316507.616 rows=0 loops=1)
                Join Filter: ((payments.pay_date > o.rej_date) AND (public.customer.cid = o.cid) AND ((public.invoice.create_date - o.rej_date) < '14 days'::interval))
                ->  CTE Scan on old_rejections o  (cost=0.00..2.84 rows=142 width=16) (actual time=0.001..0.006 rows=61 loops=1)
                ->  Materialize  (cost=207.03..717560.16 rows=194 width=40) (actual time=4381.125..5188.648 rows=11 loops=61)
                      ->  Hash Join  (cost=207.03..717559.19 rows=194 width=40) (actual time=267248.614..316507.477 rows=11 loops=1)
                            Hash Cond: (payments.cid = public.customer.cid)
                            Join Filter: (payments.pay_date < public.invoice.create_date)
                            ->  CTE Scan on payments  (cost=0.00..604079.92 rows=30203996 width=16) (actual time=267071.192..314267.563 rows=27670712 loops=1)
                            ->  Hash  (cost=206.90..206.90 rows=10 width=24) (actual time=0.240..0.240 rows=10 loops=1)
                                  Buckets: 1024  Batches: 1  Memory Usage: 1kB
                                  ->  Nested Loop  (cost=0.00..206.90 rows=10 width=24) (actual time=0.061..0.237 rows=10 loops=1)
                                        ->  Nested Loop  (cost=0.00..171.89 rows=10 width=30) (actual time=0.035..0.091 rows=10 loops=1)
                                              ->  CTE Scan on sample  (cost=0.00..0.20 rows=10 width=8) (actual time=0.002..0.003 rows=10 loops=1)
                                              ->  Index Scan using invoice_invno_index on invoice  (cost=0.00..17.16 rows=1 width=30) (actual time=0.008..0.008 rows=1 loops=10)
                                                    Index Cond: (invno = sample.invno)
                                        ->  Index Scan using customer_pno_index on customer  (cost=0.00..3.49 rows=1 width=24) (actual time=0.013..0.014 rows=1 loops=10)
                                              Index Cond: (pno = public.invoice.pno)
  CTE rejections_0_14_days
    ->  HashAggregate  (cost=211.66..211.67 rows=1 width=64) (actual time=323694.274..323694.274 rows=2 loops=1)
          ->  WindowAgg  (cost=211.61..211.65 rows=1 width=64) (actual time=323694.256..323694.263 rows=6 loops=1)
                ->  Sort  (cost=211.61..211.62 rows=1 width=64) (actual time=323694.232..323694.233 rows=6 loops=1)
                      Sort Key: public.invoice.invno, o.rej_date
                      Sort Method: quicksort  Memory: 25kB
                      ->  Nested Loop Left Join  (cost=4.62..211.60 rows=1 width=64) (actual time=323694.102..323694.222 rows=6 loops=1)
                            Join Filter: (sample.invno = ps.invno)
                            ->  Hash Join  (cost=4.62..211.57 rows=1 width=64) (actual time=7186.481..7186.600 rows=6 loops=1)
                                  Hash Cond: (public.customer.cid = o.cid)
                                  Join Filter: ((o.rej_date < public.invoice.create_date) AND ((public.invoice.create_date - o.rej_date) < '14 days'::interval))
                                  ->  Nested Loop  (cost=0.00..206.90 rows=10 width=32) (actual time=89.713..89.947 rows=10 loops=1)
                                        ->  Nested Loop  (cost=0.00..171.89 rows=10 width=38) (actual time=38.690..38.787 rows=10 loops=1)
                                              ->  CTE Scan on sample  (cost=0.00..0.20 rows=10 width=8) (actual time=0.001..0.005 rows=10 loops=1)
                                              ->  Index Scan using invoice_invno_index on invoice  (cost=0.00..17.16 rows=1 width=30) (actual time=3.876..3.876 rows=1 loops=10)
                                                    Index Cond: (invno = sample.invno)
                                        ->  Index Scan using customer_pno_index on customer  (cost=0.00..3.49 rows=1 width=24) (actual time=5.114..5.114 rows=1 loops=10)
                                              Index Cond: (pno = public.invoice.pno)
                                  ->  Hash  (cost=2.84..2.84 rows=142 width=48) (actual time=7096.583..7096.583 rows=61 loops=1)
                                        Buckets: 1024  Batches: 1  Memory Usage: 5kB
                                        ->  CTE Scan on old_rejections o  (cost=0.00..2.84 rows=142 width=48) (actual time=381.626..7096.476 rows=61 loops=1)
                            ->  CTE Scan on paid_since_rejected ps  (cost=0.00..0.02 rows=1 width=8) (actual time=52751.270..52751.270 rows=0 loops=6)
  ->  CTE Scan on sample  (cost=0.00..0.20 rows=10 width=8) (actual time=0.014..0.015 rows=10 loops=1)
  ->  Hash  (cost=0.02..0.02 rows=1 width=49) (actual time=323694.279..323694.279 rows=2 loops=1)
        Buckets: 1024  Batches: 1  Memory Usage: 1kB
        ->  CTE Scan on rejections_0_14_days  (cost=0.00..0.02 rows=1 width=49) (actual time=323694.276..323694.278 rows=2 loops=1)
Total runtime: 329622.797 ms

Per node type stats

node type	count	sum of times	% of query
CTE Scan	10	961646.730 ms	297.1 %
Group	1	5336.726 ms	1.6 %
Hash	6	11562.652 ms	3.6 %
Hash Join	5	143394.014 ms	44.3 %
Hash Left Join	1	0.014 ms	0.0 %
HashAggregate	3	0.045 ms	0.0 %
Index Scan	7	233.350 ms	0.1 %
Limit	1	0.006 ms	0.0 %
Materialize	1	0.051 ms	0.0 %
Nested Loop	8	0.244 ms	0.0 %
Nested Loop Left Join	1	0.002 ms	0.0 %
Seq Scan	5	66854.585 ms	20.7 %
Sort	2	88724.790 ms	27.4 %
WindowAgg	1	0.030 ms	0.0 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
customer	5	8895.870 ms	2.7 %
Index Scan	4	162.890 ms	1.8 %
Seq Scan	1	8732.980 ms	98.2 %
inv_paid_hist	1	25552.430 ms	7.9 %
Seq Scan	1	25552.430 ms	100.0 %
invoice	5	26377.745 ms	8.1 %
Index Scan	3	70.460 ms	0.3 %
Seq Scan	2	26307.285 ms	99.7 %
rejection	1	6261.890 ms	1.9 %
Seq Scan	1	6261.890 ms	100.0 %

explain.depesz.com

A tool for finding a real cause for slow queries.

Result: KKe

Colorize:

Columns visibility:

Per node type stats

Per table stats