explain.depesz.com

PostgreSQL's explain analyze made readable

Result: uWfA

Settings
# exclusive inclusive rows x rows loops node
1. 0.756 10,794.263 ↓ 12.0 12 1

GroupAggregate (cost=22,137.41..22,137.47 rows=1 width=25) (actual time=10,793.525..10,794.263 rows=12 loops=1)

  • Group Key: ncso.date, ((ncso.date <> rx.processing_date))
2. 1.412 10,793.507 ↓ 431.0 431 1

Sort (cost=22,137.41..22,137.42 rows=1 width=30) (actual time=10,793.407..10,793.507 rows=431 loops=1)

  • Sort Key: ncso.date, ((ncso.date <> rx.processing_date))
  • Sort Method: quicksort Memory: 58kB
3. 1.288 10,792.095 ↓ 431.0 431 1

Nested Loop (cost=2,975.20..22,137.40 rows=1 width=30) (actual time=1,276.739..10,792.095 rows=431 loops=1)

4. 1.542 10,785.635 ↓ 431.0 431 1

Nested Loop (cost=2,974.92..22,136.54 rows=1 width=37) (actual time=1,276.717..10,785.635 rows=431 loops=1)

  • Join Filter: (ncso.vmpp_id = vmpp.vppid)
5. 2.820 10,780.214 ↓ 431.0 431 1

Nested Loop (cost=2,974.64..22,134.70 rows=1 width=45) (actual time=1,276.694..10,780.214 rows=431 loops=1)

6. 24.481 10,770.067 ↓ 215.5 431 1

Hash Join (cost=2,974.22..22,130.78 rows=2 width=72) (actual time=1,276.653..10,770.067 rows=431 loops=1)

  • Hash Cond: ((rx.presentation_code)::text = product.bnf_code)
  • Join Filter: ((rx.processing_date = ncso.date) OR ((rx.processing_date = '2018-10-01'::date) AND (ncso.date > rx.processing_date)))
  • Rows Removed by Join Filter: 4324
7. 5.953 10,725.984 ↑ 1.3 10,788 1

Append (cost=0.00..19,081.34 rows=13,825 width=31) (actual time=5.740..10,725.984 rows=10,788 loops=1)

8. 0.007 0.007 ↓ 0.0 0 1

Seq Scan on frontend_prescription rx (cost=0.00..0.00 rows=1 width=76) (actual time=0.007..0.007 rows=0 loops=1)

  • Filter: ((processing_date >= '2017-12-01'::date) AND (processing_date <= '2018-12-01'::date) AND ((practice_id)::text = 'G81090'::text))
9. 1,234.835 1,239.222 ↑ 1.3 997 1

Bitmap Heap Scan on frontend_prescription_201712 rx_1 (cost=15.75..1,730.77 rows=1,254 width=31) (actual time=5.732..1,239.222 rows=997 loops=1)

  • Recheck Cond: ((practice_id)::text = 'G81090'::text)
  • Filter: ((processing_date >= '2017-12-01'::date) AND (processing_date <= '2018-12-01'::date))
  • Heap Blocks: exact=997
10. 4.387 4.387 ↑ 1.3 997 1

Bitmap Index Scan on idx_frontend_prescription_201712_practice_id (cost=0.00..15.44 rows=1,254 width=0) (actual time=4.387..4.387 rows=997 loops=1)

  • Index Cond: ((practice_id)::text = 'G81090'::text)
11. 835.613 839.782 ↑ 1.3 953 1

Bitmap Heap Scan on frontend_prescription_201801 rx_2 (cost=15.91..1,758.35 rows=1,274 width=31) (actual time=5.443..839.782 rows=953 loops=1)

  • Recheck Cond: ((practice_id)::text = 'G81090'::text)
  • Filter: ((processing_date >= '2017-12-01'::date) AND (processing_date <= '2018-12-01'::date))
  • Heap Blocks: exact=953
12. 4.169 4.169 ↑ 1.3 953 1

Bitmap Index Scan on idx_frontend_prescription_201801_practice_id (cost=0.00..15.59 rows=1,274 width=0) (actual time=4.169..4.169 rows=953 loops=1)

  • Index Cond: ((practice_id)::text = 'G81090'::text)
13. 971.567 974.133 ↑ 1.3 941 1

Bitmap Heap Scan on frontend_prescription_201802 rx_3 (cost=15.57..1,698.77 rows=1,230 width=31) (actual time=3.885..974.133 rows=941 loops=1)

  • Recheck Cond: ((practice_id)::text = 'G81090'::text)
  • Filter: ((processing_date >= '2017-12-01'::date) AND (processing_date <= '2018-12-01'::date))
  • Heap Blocks: exact=941
14. 2.566 2.566 ↑ 1.3 941 1

Bitmap Index Scan on idx_frontend_prescription_201802_practice_id (cost=0.00..15.26 rows=1,230 width=0) (actual time=2.566..2.566 rows=941 loops=1)

  • Index Cond: ((practice_id)::text = 'G81090'::text)
15. 1,021.422 1,023.527 ↑ 1.2 1,011 1

Bitmap Heap Scan on frontend_prescription_201803 rx_4 (cost=15.76..1,733.55 rows=1,255 width=31) (actual time=2.348..1,023.527 rows=1,011 loops=1)

  • Recheck Cond: ((practice_id)::text = 'G81090'::text)
  • Filter: ((processing_date >= '2017-12-01'::date) AND (processing_date <= '2018-12-01'::date))
  • Heap Blocks: exact=1011
16. 2.105 2.105 ↑ 1.2 1,011 1

Bitmap Index Scan on idx_frontend_prescription_201803_practice_id (cost=0.00..15.45 rows=1,255 width=0) (actual time=2.105..2.105 rows=1,011 loops=1)

  • Index Cond: ((practice_id)::text = 'G81090'::text)
17. 940.171 944.293 ↑ 1.3 984 1

Bitmap Heap Scan on frontend_prescription_201804 rx_5 (cost=15.72..1,725.13 rows=1,250 width=31) (actual time=5.468..944.293 rows=984 loops=1)

  • Recheck Cond: ((practice_id)::text = 'G81090'::text)
  • Filter: ((processing_date >= '2017-12-01'::date) AND (processing_date <= '2018-12-01'::date))
  • Heap Blocks: exact=984
18. 4.122 4.122 ↑ 1.3 984 1

Bitmap Index Scan on idx_frontend_prescription_201804_practice_id (cost=0.00..15.41 rows=1,250 width=0) (actual time=4.122..4.122 rows=984 loops=1)

  • Index Cond: ((practice_id)::text = 'G81090'::text)
19. 915.138 918.105 ↑ 1.3 1,000 1

Bitmap Heap Scan on frontend_prescription_201805 rx_6 (cost=15.82..1,741.74 rows=1,262 width=31) (actual time=4.043..918.105 rows=1,000 loops=1)

  • Recheck Cond: ((practice_id)::text = 'G81090'::text)
  • Filter: ((processing_date >= '2017-12-01'::date) AND (processing_date <= '2018-12-01'::date))
  • Heap Blocks: exact=1000
20. 2.967 2.967 ↑ 1.3 1,000 1

Bitmap Index Scan on idx_frontend_prescription_201805_practice_id (cost=0.00..15.50 rows=1,262 width=0) (actual time=2.967..2.967 rows=1,000 loops=1)

  • Index Cond: ((practice_id)::text = 'G81090'::text)
21. 937.087 939.937 ↑ 1.3 988 1

Bitmap Heap Scan on frontend_prescription_201806 rx_7 (cost=15.86..1,749.73 rows=1,268 width=31) (actual time=4.299..939.937 rows=988 loops=1)

  • Recheck Cond: ((practice_id)::text = 'G81090'::text)
  • Filter: ((processing_date >= '2017-12-01'::date) AND (processing_date <= '2018-12-01'::date))
  • Heap Blocks: exact=988
22. 2.850 2.850 ↑ 1.3 988 1

Bitmap Index Scan on idx_frontend_prescription_201806_practice_id (cost=0.00..15.54 rows=1,268 width=0) (actual time=2.850..2.850 rows=988 loops=1)

  • Index Cond: ((practice_id)::text = 'G81090'::text)
23. 1,212.905 1,216.458 ↑ 1.3 1,011 1

Bitmap Heap Scan on frontend_prescription_201807 rx_8 (cost=15.84..1,745.56 rows=1,265 width=31) (actual time=4.804..1,216.458 rows=1,011 loops=1)

  • Recheck Cond: ((practice_id)::text = 'G81090'::text)
  • Filter: ((processing_date >= '2017-12-01'::date) AND (processing_date <= '2018-12-01'::date))
  • Heap Blocks: exact=1011
24. 3.553 3.553 ↑ 1.3 1,011 1

Bitmap Index Scan on idx_frontend_prescription_201807_practice_id (cost=0.00..15.52 rows=1,265 width=0) (actual time=3.553..3.553 rows=1,011 loops=1)

  • Index Cond: ((practice_id)::text = 'G81090'::text)
25. 860.944 864.930 ↑ 1.3 965 1

Bitmap Heap Scan on frontend_prescription_201808 rx_9 (cost=15.81..1,739.90 rows=1,261 width=31) (actual time=5.257..864.930 rows=965 loops=1)

  • Recheck Cond: ((practice_id)::text = 'G81090'::text)
  • Filter: ((processing_date >= '2017-12-01'::date) AND (processing_date <= '2018-12-01'::date))
  • Heap Blocks: exact=964
26. 3.986 3.986 ↑ 1.3 965 1

Bitmap Index Scan on idx_frontend_prescription_201808_practice_id (cost=0.00..15.49 rows=1,261 width=0) (actual time=3.986..3.986 rows=965 loops=1)

  • Index Cond: ((practice_id)::text = 'G81090'::text)
27. 897.354 899.967 ↑ 1.3 965 1

Bitmap Heap Scan on frontend_prescription_201809 rx_10 (cost=15.60..1,704.05 rows=1,234 width=31) (actual time=4.147..899.967 rows=965 loops=1)

  • Recheck Cond: ((practice_id)::text = 'G81090'::text)
  • Filter: ((processing_date >= '2017-12-01'::date) AND (processing_date <= '2018-12-01'::date))
  • Heap Blocks: exact=965
28. 2.613 2.613 ↑ 1.3 965 1

Bitmap Index Scan on idx_frontend_prescription_201809_practice_id (cost=0.00..15.29 rows=1,234 width=0) (actual time=2.613..2.613 rows=965 loops=1)

  • Index Cond: ((practice_id)::text = 'G81090'::text)
29. 854.205 859.670 ↑ 1.3 973 1

Bitmap Heap Scan on frontend_prescription_201810 rx_11 (cost=15.89..1,753.78 rows=1,271 width=31) (actual time=17.784..859.670 rows=973 loops=1)

  • Recheck Cond: ((practice_id)::text = 'G81090'::text)
  • Filter: ((processing_date >= '2017-12-01'::date) AND (processing_date <= '2018-12-01'::date))
  • Heap Blocks: exact=973
30. 5.465 5.465 ↑ 1.3 973 1

Bitmap Index Scan on idx_frontend_prescription_201810_practice_id (cost=0.00..15.57 rows=1,271 width=0) (actual time=5.465..5.465 rows=973 loops=1)

  • Index Cond: ((practice_id)::text = 'G81090'::text)
31. 9.099 19.602 ↓ 1.4 713 1

Hash (cost=2,967.74..2,967.74 rows=518 width=41) (actual time=19.601..19.602 rows=713 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 64kB
32. 0.748 10.503 ↓ 1.4 713 1

Nested Loop (cost=14.84..2,967.74 rows=518 width=41) (actual time=0.212..10.503 rows=713 loops=1)

33. 0.605 5.477 ↓ 1.4 713 1

Nested Loop (cost=14.42..1,946.84 rows=518 width=36) (actual time=0.190..5.477 rows=713 loops=1)

34. 0.448 0.594 ↓ 1.1 713 1

Bitmap Heap Scan on dmd_ncsoconcession ncso (cost=14.00..45.84 rows=656 width=16) (actual time=0.165..0.594 rows=713 loops=1)

  • Recheck Cond: ((date > '2017-12-01'::date) AND (date <= '2018-12-01'::date))
  • Heap Blocks: exact=11
35. 0.146 0.146 ↓ 1.1 713 1

Bitmap Index Scan on dmd_ncsoconcession_date_a1a20bea_uniq (cost=0.00..13.84 rows=656 width=0) (actual time=0.145..0.146 rows=713 loops=1)

  • Index Cond: ((date > '2017-12-01'::date) AND (date <= '2018-12-01'::date))
36. 4.278 4.278 ↑ 1.0 1 713

Index Scan using dmd_tariffprice_date_a7f529c9_uniq on dmd_tariffprice dt (cost=0.42..2.89 rows=1 width=24) (actual time=0.006..0.006 rows=1 loops=713)

  • Index Cond: ((date = ncso.date) AND (vmpp_id = ncso.vmpp_id))
37. 4.278 4.278 ↑ 1.0 1 713

Index Scan using dmd_product_temp_pkey on dmd_product product (cost=0.42..1.96 rows=1 width=21) (actual time=0.006..0.006 rows=1 loops=713)

  • Index Cond: (dmdid = dt.product_id)
38. 7.327 7.327 ↑ 1.0 1 431

Index Scan using frontend_presentation_bnf_code_2de5959a2767b81f_like on frontend_presentation presentation (cost=0.42..1.95 rows=1 width=15) (actual time=0.017..0.017 rows=1 loops=431)

  • Index Cond: ((bnf_code)::text = product.bnf_code)
39. 3.879 3.879 ↑ 1.0 1 431

Index Scan using dmd_vmpp_pkey on dmd_vmpp vmpp (cost=0.29..1.82 rows=1 width=16) (actual time=0.009..0.009 rows=1 loops=431)

  • Index Cond: (vppid = dt.vmpp_id)
40. 5.172 5.172 ↑ 1.0 1 431

Index Only Scan using frontend_pct_code_1df55e2c36c298b2_like on frontend_pct (cost=0.28..0.85 rows=1 width=4) (actual time=0.011..0.012 rows=1 loops=431)

  • Index Cond: (code = (rx.pct_id)::text)
  • Heap Fetches: 0