explain.depesz.com

PostgreSQL's explain analyze made readable

Result: AEQf

Settings
# 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)