#	exclusive	inclusive	rows x	rows	loops	node
1.	17.616	1,649.080	↑ 1.0	10	1	→ Limit (cost=161,335.89..161,335.91 rows=10 width=98) (actual time=1,631.462..1,649.080 rows=10 loops=1)
2.	52.041	1,631.464	↑ 1,163.2	10	1	→ Sort (cost=161,335.89..161,364.97 rows=11,632 width=98) (actual time=1,631.461..1,631.464 rows=10 loops=1) Sort Key: (CASE WHEN (rx.quantity > '0'::double precision) THEN (((round(((rx.net_cost / rx.quantity))::numeric, 4))::double precision - dt.median) * rx.quantity) ELSE '0'::double precision END) DESC Sort Method: top-N heapsort Memory: 26kB
3.	0.643	1,579.423	↓ 7.0	80,934	1	→ Gather (cost=8,239.14..161,084.52 rows=11,632 width=98) (actual time=14.206..1,579.423 rows=80,934 loops=1) Workers Planned: 4 Workers Launched: 4
4.	79.696	1,578.780	↓ 5.6	16,187	5	→ Hash Join (cost=7,239.14..158,681.41 rows=2,908 width=98) (actual time=26.024..1,578.780 rows=16,187 loops=5) Hash Cond: ((rx.presentation_code)::text = product.bnf_code)
5.	330.308	1,477.503	↓ 1.4	31,062	5	→ Hash Join (cost=154.02..151,105.11 rows=21,836 width=50) (actual time=1.793..1,477.503 rows=31,062 loops=5) Hash Cond: ((rx.practice_id)::text = (practice.code)::text)
6.	261.367	1,146.277	↑ 1.3	1,954,251	5	→ Append (cost=0.00..141,572.17 rows=2,442,815 width=46) (actual time=0.024..1,146.277 rows=1,954,251 loops=5)
7.	0.000	0.000	↓ 0.0	0	5	→ Parallel Seq Scan on frontend_prescription rx (cost=0.00..0.00 rows=1 width=100) (actual time=0.000..0.000 rows=0 loops=5) Filter: (processing_date = '2018-10-01'::date)
8.	884.910	884.910	↑ 1.3	1,954,251	5	→ Parallel Seq Scan on frontend_prescription_201810 rx_1 (cost=0.00..141,572.17 rows=2,442,814 width=46) (actual time=0.023..884.910 rows=1,954,251 loops=5) Filter: (processing_date = '2018-10-01'::date)
9.	0.059	0.918	↑ 1.0	140	5	→ Hash (cost=152.27..152.27 rows=140 width=11) (actual time=0.917..0.918 rows=140 loops=5) Buckets: 1024 Batches: 1 Memory Usage: 14kB
10.	0.762	0.859	↑ 1.0	140	5	→ Bitmap Heap Scan on frontend_practice practice (cost=2.77..152.27 rows=140 width=11) (actual time=0.126..0.859 rows=140 loops=5) Recheck Cond: ((ccg_id)::text = '99P'::text) Heap Blocks: exact=83
11.	0.097	0.097	↑ 1.0	140	5	→ Bitmap Index Scan on frontend_practice_af7f1f50 (cost=0.00..2.74 rows=140 width=0) (actual time=0.097..0.097 rows=140 loops=5) Index Cond: ((ccg_id)::text = '99P'::text)
12.	2.799	21.581	↑ 1.0	2,741	5	→ Hash (cost=7,050.86..7,050.86 rows=2,741 width=25) (actual time=21.581..21.581 rows=2,741 loops=5) Buckets: 4096 Batches: 1 Memory Usage: 204kB
13.	16.642	18.782	↑ 1.0	2,741	5	→ Nested Loop (cost=0.42..7,050.86 rows=2,741 width=25) (actual time=0.072..18.782 rows=2,741 loops=5)
14.	2.135	2.135	↑ 1.0	2,741	5	→ Seq Scan on tmp_medians dt (cost=0.00..155.66 rows=2,741 width=20) (actual time=0.033..2.135 rows=2,741 loops=5) Filter: (date = '2018-10-01'::date) Rows Removed by Filter: 5472
15.	0.005	0.005	↑ 1.0	1	13,705	→ Index Scan using dmd_product_temp_pkey on dmd_product product (cost=0.42..2.51 rows=1 width=21) (actual time=0.005..0.005 rows=1 loops=13,705) Index Cond: (dmdid = dt.product_id)

Limit  (cost=161335.89..161335.91 rows=10 width=98) (actual time=1631.462..1649.080 rows=10 loops=1)                                                                                                           
  ->  Sort  (cost=161335.89..161364.97 rows=11632 width=98) (actual time=1631.461..1631.464 rows=10 loops=1)                                                                                                   
        Sort Key: (CASE WHEN (rx.quantity > '0'::double precision) THEN (((round(((rx.net_cost / rx.quantity))::numeric, 4))::double precision - dt.median) * rx.quantity) ELSE '0'::double precision END) DESC
        Sort Method: top-N heapsort  Memory: 26kB                                                                                                                                                              
        ->  Gather  (cost=8239.14..161084.52 rows=11632 width=98) (actual time=14.206..1579.423 rows=80934 loops=1)                                                                                            
              Workers Planned: 4                                                                                                                                                                               
              Workers Launched: 4                                                                                                                                                                              
              ->  Hash Join  (cost=7239.14..158681.41 rows=2908 width=98) (actual time=26.024..1578.780 rows=16187 loops=5)                                                                                    
                    Hash Cond: ((rx.presentation_code)::text = product.bnf_code)                                                                                                                               
                    ->  Hash Join  (cost=154.02..151105.11 rows=21836 width=50) (actual time=1.793..1477.503 rows=31062 loops=5)                                                                               
                          Hash Cond: ((rx.practice_id)::text = (practice.code)::text)                                                                                                                          
                          ->  Append  (cost=0.00..141572.17 rows=2442815 width=46) (actual time=0.024..1146.277 rows=1954251 loops=5)                                                                          
                                ->  Parallel Seq Scan on frontend_prescription rx  (cost=0.00..0.00 rows=1 width=100) (actual time=0.000..0.000 rows=0 loops=5)                                                
                                      Filter: (processing_date = '2018-10-01'::date)                                                                                                                           
                                ->  Parallel Seq Scan on frontend_prescription_201810 rx_1  (cost=0.00..141572.17 rows=2442814 width=46) (actual time=0.023..884.910 rows=1954251 loops=5)                     
                                      Filter: (processing_date = '2018-10-01'::date)                                                                                                                           
                          ->  Hash  (cost=152.27..152.27 rows=140 width=11) (actual time=0.917..0.918 rows=140 loops=5)                                                                                        
                                Buckets: 1024  Batches: 1  Memory Usage: 14kB                                                                                                                                  
                                ->  Bitmap Heap Scan on frontend_practice practice  (cost=2.77..152.27 rows=140 width=11) (actual time=0.126..0.859 rows=140 loops=5)                                          
                                      Recheck Cond: ((ccg_id)::text = '99P'::text)                                                                                                                             
                                      Heap Blocks: exact=83                                                                                                                                                    
                                      ->  Bitmap Index Scan on frontend_practice_af7f1f50  (cost=0.00..2.74 rows=140 width=0) (actual time=0.097..0.097 rows=140 loops=5)                                      
                                            Index Cond: ((ccg_id)::text = '99P'::text)                                                                                                                         
                    ->  Hash  (cost=7050.86..7050.86 rows=2741 width=25) (actual time=21.581..21.581 rows=2741 loops=5)                                                                                        
                          Buckets: 4096  Batches: 1  Memory Usage: 204kB                                                                                                                                       
                          ->  Nested Loop  (cost=0.42..7050.86 rows=2741 width=25) (actual time=0.072..18.782 rows=2741 loops=5)                                                                               
                                ->  Seq Scan on tmp_medians dt  (cost=0.00..155.66 rows=2741 width=20) (actual time=0.033..2.135 rows=2741 loops=5)                                                            
                                      Filter: (date = '2018-10-01'::date)                                                                                                                                      
                                      Rows Removed by Filter: 5472                                                                                                                                             
                                ->  Index Scan using dmd_product_temp_pkey on dmd_product product  (cost=0.42..2.51 rows=1 width=21) (actual time=0.005..0.005 rows=1 loops=13705)                             
                                      Index Cond: (dmdid = dt.product_id)                                                                                                                                      
Planning time: 4.928 ms                                                                                                                                                                                        
Execution time: 1649.201 ms

Per node type stats

node type	count	sum of times	% of query
Append	1	261.367 ms	15.8 %
Bitmap Heap Scan	1	0.762 ms	0.0 %
Bitmap Index Scan	1	0.097 ms	0.0 %
Gather	1	0.643 ms	0.0 %
Hash	2	2.858 ms	0.2 %
Hash Join	2	410.004 ms	24.9 %
Index Scan	1	0.005 ms	0.0 %
Limit	1	17.616 ms	1.1 %
Nested Loop	1	16.642 ms	1.0 %
Parallel Seq Scan	2	884.910 ms	53.7 %
Seq Scan	1	2.135 ms	0.1 %
Sort	1	52.041 ms	3.2 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
dmd_product	1	0.005 ms	0.0 %
Index Scan	1	0.005 ms	100.0 %
frontend_practice	1	0.762 ms	0.0 %
Bitmap Heap Scan	1	0.762 ms	100.0 %
frontend_prescription	1	0.000 ms	0.0 %
Parallel Seq Scan	1	0.000 ms	0.0 %
frontend_prescription_201810	1	884.910 ms	53.7 %
Parallel Seq Scan	1	884.910 ms	100.0 %
tmp_medians	1	2.135 ms	0.1 %
Seq Scan	1	2.135 ms	100.0 %

explain.depesz.com

PostgreSQL's explain analyze made readable

Result: AEQf

Color mode:

Visible columns:

Per node type stats

Per table stats