#	exclusive	inclusive	rows x	rows	loops	node
1.	0.004	2,355.463	↑ 1.0	25	1	→ Limit (cost=138,954.79..161,697.01 rows=25 width=156) (actual time=2,349.886..2,355.463 rows=25 loops=1)
2.	0.043	2,355.459	↑ 1,109.7	25	1	→ Merge Left Join (cost=138,954.79..25,375,541.56 rows=27,742 width=156) (actual time=2,349.886..2,355.459 rows=25 loops=1) Merge Cond: (br.billingrunid = q2.billingrunid)
3.	0.019	19.767	↑ 1,109.7	25	1	→ Merge Left Join (cost=2,627.84..5,113.93 rows=27,742 width=124) (actual time=19.463..19.767 rows=25 loops=1) Merge Cond: (br.billingrunid = idr.billingrunid)
4.	0.298	0.298	↑ 1,109.7	25	1	→ Index Scan Backward using billingrun_pkey on billingrun br (cost=0.29..2,007.53 rows=27,742 width=116) (actual time=0.011..0.298 rows=25 loops=1)
5.	6.256	19.450	↑ 5,460.0	5	1	→ Sort (cost=2,627.55..2,695.80 rows=27,300 width=12) (actual time=19.448..19.450 rows=5 loops=1) Sort Key: idr.billingrunid Sort Method: quicksort Memory: 2048kB
6.	13.194	13.194	↑ 1.0	27,300	1	→ Seq Scan on invdocrun idr (cost=0.00..616.00 rows=27,300 width=12) (actual time=0.138..13.194 rows=27,300 loops=1)
7.	7.875	2,330.024	↑ 11.1	18	1	→ Sort (cost=136,326.96..136,327.46 rows=200 width=36) (actual time=2,330.022..2,330.024 rows=18 loops=1) Sort Key: q2.billingrunid Sort Method: quicksort Memory: 1151kB
8.	1.806	2,322.149	↓ 74.8	14,957	1	→ Subquery Scan on q2 (cost=136,313.82..136,319.32 rows=200 width=36) (actual time=2,310.193..2,322.149 rows=14,957 loops=1)
9.	33.264	2,320.343	↓ 74.8	14,957	1	→ HashAggregate (cost=136,313.82..136,317.32 rows=200 width=22) (actual time=2,310.193..2,320.343 rows=14,957 loops=1) Group Key: bm.billingrunid
10.	575.229	2,287.079	↑ 15.3	17,719	1	→ HashAggregate (cost=126,825.70..129,536.59 rows=271,089 width=14) (actual time=2,281.188..2,287.079 rows=17,719 loops=1) Group Key: bm.billingrunid, pv.internalparametervaluecode
11.	732.861	1,711.850	↓ 1.0	2,369,212	1	→ Hash Join (cost=79.34..109,060.82 rows=2,368,650 width=14) (actual time=1.683..1,711.850 rows=2,369,212 loops=1) Hash Cond: ((bm.billingmessagetypeid)::text = (bmt.billingmessagetypeid)::text)
12.	977.435	977.435	↓ 1.0	2,369,212	1	→ Seq Scan on billingmessage bm (cost=0.00..76,412.55 rows=2,368,650 width=27) (actual time=0.117..977.435 rows=2,369,212 loops=1) Filter: ((billingphase)::text = 'billing'::text) Rows Removed by Filter: 53521
13.	0.103	1.554	↓ 1.0	321	1	→ Hash (cost=75.34..75.34 rows=320 width=53) (actual time=1.554..1.554 rows=321 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 26kB
14.	0.157	1.451	↓ 1.0	321	1	→ Hash Join (cost=53.74..75.34 rows=320 width=53) (actual time=1.053..1.451 rows=321 loops=1) Hash Cond: (bmt.billingmessageseverityparamid = pv.parametervalueid)
15.	0.277	0.277	↓ 1.0	321	1	→ Seq Scan on billingmessagetype bmt (cost=0.00..17.20 rows=320 width=47) (actual time=0.026..0.277 rows=321 loops=1)
16.	0.136	1.017	↑ 1.2	878	1	→ Hash (cost=40.55..40.55 rows=1,055 width=14) (actual time=1.017..1.017 rows=878 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 36kB
17.	0.881	0.881	↑ 1.2	878	1	→ Seq Scan on parametervalue pv (cost=0.00..40.55 rows=1,055 width=14) (actual time=0.021..0.881 rows=878 loops=1)
18.						SubPlan (for Merge Left Join)
19.	0.000	0.550	↓ 0.0	0	25	→ Limit (cost=0.43..1.62 rows=1 width=4) (actual time=0.022..0.022 rows=0 loops=25)
20.	0.550	0.550	↓ 0.0	0	25	→ Index Scan using invdoc_invdocrunid on invdoc id (cost=0.43..1,537.98 rows=1,288 width=4) (actual time=0.022..0.022 rows=0 loops=25) Index Cond: (invdocrunid = idr.invdocrunid)
21.	0.075	5.075	↑ 1.0	1	25	→ Aggregate (cost=907.96..907.97 rows=1 width=0) (actual time=0.202..0.203 rows=1 loops=25)
22.	5.000	5.000	↑ 289.0	5	25	→ Index Only Scan using invoice_billingrunid_index on invoice (cost=0.43..904.35 rows=1,445 width=0) (actual time=0.054..0.200 rows=5 loops=25) Index Cond: (billingrunid = br.billingrunid) Heap Fetches: 134

Planning time	:	3.165 ms
Execution time	:	2,360.800 ms

Limit  (cost=138954.79..161697.01 rows=25 width=156) (actual time=2349.886..2355.463 rows=25 loops=1)
  ->  Merge Left Join  (cost=138954.79..25375541.56 rows=27742 width=156) (actual time=2349.886..2355.459 rows=25 loops=1)
        Merge Cond: (br.billingrunid = q2.billingrunid)
        ->  Merge Left Join  (cost=2627.84..5113.93 rows=27742 width=124) (actual time=19.463..19.767 rows=25 loops=1)
              Merge Cond: (br.billingrunid = idr.billingrunid)
              ->  Index Scan Backward using billingrun_pkey on billingrun br  (cost=0.29..2007.53 rows=27742 width=116) (actual time=0.011..0.298 rows=25 loops=1)
              ->  Sort  (cost=2627.55..2695.80 rows=27300 width=12) (actual time=19.448..19.450 rows=5 loops=1)
                    Sort Key: idr.billingrunid
                    Sort Method: quicksort  Memory: 2048kB
                    ->  Seq Scan on invdocrun idr  (cost=0.00..616.00 rows=27300 width=12) (actual time=0.138..13.194 rows=27300 loops=1)
        ->  Sort  (cost=136326.96..136327.46 rows=200 width=36) (actual time=2330.022..2330.024 rows=18 loops=1)
              Sort Key: q2.billingrunid
              Sort Method: quicksort  Memory: 1151kB
              ->  Subquery Scan on q2  (cost=136313.82..136319.32 rows=200 width=36) (actual time=2310.193..2322.149 rows=14957 loops=1)
                    ->  HashAggregate  (cost=136313.82..136317.32 rows=200 width=22) (actual time=2310.193..2320.343 rows=14957 loops=1)
                          Group Key: bm.billingrunid
                          ->  HashAggregate  (cost=126825.70..129536.59 rows=271089 width=14) (actual time=2281.188..2287.079 rows=17719 loops=1)
                                Group Key: bm.billingrunid, pv.internalparametervaluecode
                                ->  Hash Join  (cost=79.34..109060.82 rows=2368650 width=14) (actual time=1.683..1711.850 rows=2369212 loops=1)
                                      Hash Cond: ((bm.billingmessagetypeid)::text = (bmt.billingmessagetypeid)::text)
                                      ->  Seq Scan on billingmessage bm  (cost=0.00..76412.55 rows=2368650 width=27) (actual time=0.117..977.435 rows=2369212 loops=1)
                                            Filter: ((billingphase)::text = 'billing'::text)
                                            Rows Removed by Filter: 53521
                                      ->  Hash  (cost=75.34..75.34 rows=320 width=53) (actual time=1.554..1.554 rows=321 loops=1)
                                            Buckets: 1024  Batches: 1  Memory Usage: 26kB
                                            ->  Hash Join  (cost=53.74..75.34 rows=320 width=53) (actual time=1.053..1.451 rows=321 loops=1)
                                                  Hash Cond: (bmt.billingmessageseverityparamid = pv.parametervalueid)
                                                  ->  Seq Scan on billingmessagetype bmt  (cost=0.00..17.20 rows=320 width=47) (actual time=0.026..0.277 rows=321 loops=1)
                                                  ->  Hash  (cost=40.55..40.55 rows=1055 width=14) (actual time=1.017..1.017 rows=878 loops=1)
                                                        Buckets: 1024  Batches: 1  Memory Usage: 36kB
                                                        ->  Seq Scan on parametervalue pv  (cost=0.00..40.55 rows=1055 width=14) (actual time=0.021..0.881 rows=878 loops=1)
        SubPlan 1
          ->  Limit  (cost=0.43..1.62 rows=1 width=4) (actual time=0.022..0.022 rows=0 loops=25)
                ->  Index Scan using invdoc_invdocrunid on invdoc id  (cost=0.43..1537.98 rows=1288 width=4) (actual time=0.022..0.022 rows=0 loops=25)
                      Index Cond: (invdocrunid = idr.invdocrunid)
        SubPlan 2
          ->  Aggregate  (cost=907.96..907.97 rows=1 width=0) (actual time=0.202..0.203 rows=1 loops=25)
                ->  Index Only Scan using invoice_billingrunid_index on invoice  (cost=0.43..904.35 rows=1445 width=0) (actual time=0.054..0.200 rows=5 loops=25)
                      Index Cond: (billingrunid = br.billingrunid)
                      Heap Fetches: 134
Planning time: 3.165 ms
Execution time: 2360.800 ms

Per node type stats

node type	count	sum of times	% of query
Aggregate	1	0.075 ms	0.0 %
Hash	2	0.239 ms	0.0 %
Hash Join	2	733.018 ms	31.1 %
HashAggregate	2	608.493 ms	25.8 %
Index Only Scan	1	5.000 ms	0.2 %
Index Scan	1	0.550 ms	0.0 %
Index Scan Backward	1	0.298 ms	0.0 %
Limit	2	0.004 ms	0.0 %
Merge Left Join	2	0.062 ms	0.0 %
Seq Scan	4	991.787 ms	42.1 %
Sort	2	14.131 ms	0.6 %
Subquery Scan	1	1.806 ms	0.1 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
billingmessage	1	977.435 ms	41.5 %
Seq Scan	1	977.435 ms	100.0 %
billingmessagetype	1	0.277 ms	0.0 %
Seq Scan	1	0.277 ms	100.0 %
billingrun	1	0.298 ms	0.0 %
Index Scan Backward	1	0.298 ms	100.0 %
invdoc	1	0.550 ms	0.0 %
Index Scan	1	0.550 ms	100.0 %
invdocrun	1	13.194 ms	0.6 %
Seq Scan	1	13.194 ms	100.0 %
invoice	1	5.000 ms	0.2 %
Index Only Scan	1	5.000 ms	100.0 %
parametervalue	1	0.881 ms	0.0 %
Seq Scan	1	0.881 ms	100.0 %

explain.depesz.com

PostgreSQL's explain analyze made readable

Result: OW8

Color mode:

Visible columns:

Per node type stats

Per table stats