explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PiuJ

Settings
# exclusive inclusive rows x rows loops node
1. 37,483.898 2,209,195.637 ↓ 46.8 1,872,011 1

GroupAggregate (cost=89,013,183.33..89,248,715.52 rows=40,000 width=61) (actual time=2,140,757.784..2,209,195.637 rows=1,872,011 loops=1)

  • Group Key: "*SELECT* 1".pos_outlet_id, "*SELECT* 1".item_code
2. 66,291.190 2,171,711.739 ↓ 1.1 16,518,003 1

Sort (cost=89,013,183.33..89,052,355.36 rows=15,668,813 width=66) (actual time=2,140,757.682..2,171,711.739 rows=16,518,003 loops=1)

  • Sort Key: "*SELECT* 1".pos_outlet_id, "*SELECT* 1".item_code
  • Sort Method: external merge Disk: 846,088kB
3. 1,092.762 2,105,420.549 ↓ 1.1 16,518,003 1

Append (cost=31,120,830.39..86,497,984.57 rows=15,668,813 width=66) (actual time=1,446,654.442..2,105,420.549 rows=16,518,003 loops=1)

4. 1,197.793 1,707,426.353 ↓ 1.0 11,614,786 1

Subquery Scan on *SELECT* 1 (cost=31,120,830.39..44,028,067.51 rows=11,457,859 width=66) (actual time=1,446,654.440..1,707,426.353 rows=11,614,786 loops=1)

5. 3,667.990 1,706,228.560 ↓ 1.0 11,614,786 1

Hash Left Join (cost=31,120,830.39..43,913,488.92 rows=11,457,859 width=102) (actual time=1,446,654.439..1,706,228.560 rows=11,614,786 loops=1)

  • Hash Cond: (oi.id = rrt.order_item_id)
6. 54,212.408 1,701,928.687 ↓ 1.0 11,614,786 1

Hash Join (cost=31,107,261.90..43,441,448.68 rows=11,457,859 width=44) (actual time=1,446,018.757..1,701,928.687 rows=11,614,786 loops=1)

  • Hash Cond: (oi.item_id = it.id)
7. 54,963.778 332,018.796 ↓ 1.0 13,657,927 1

Hash Join (cost=3,387,319.54..10,979,342.73 rows=13,291,666 width=39) (actual time=55,201.512..332,018.796 rows=13,657,927 loops=1)

  • Hash Cond: (oi.order_id = o.id)
8. 222,389.422 222,389.422 ↑ 1.0 125,016,947 1

Seq Scan on order_items oi (cost=0.00..4,041,130.58 rows=125,435,797 width=22) (actual time=0.017..222,389.422 rows=125,016,947 loops=1)

  • Filter: ((((category)::text <> 'ENGROS'::text) OR (category IS NULL)) AND (((category)::text <> 'ENGROS'::text) OR (category IS NULL)))
  • Rows Removed by Filter: 580,386
9. 1,767.592 54,665.596 ↑ 1.2 8,339,814 1

Hash (cost=3,199,257.97..3,199,257.97 rows=9,725,966 width=25) (actual time=54,665.596..54,665.596 rows=8,339,814 loops=1)

  • Buckets: 4,194,304 Batches: 4 Memory Usage: 154,824kB
10. 52,713.281 52,898.004 ↑ 1.2 8,339,814 1

Bitmap Heap Scan on orders o (cost=133,257.50..3,199,257.97 rows=9,725,966 width=25) (actual time=192.431..52,898.004 rows=8,339,814 loops=1)

  • Recheck Cond: ((finished_at >= ((now() - '92 days'::interval))::date) AND (finished_at <= ((now())::date -'00:00:01'::interval)))
  • Rows Removed by Index Recheck: 78,688,324
  • Filter: (status = 210)
  • Rows Removed by Filter: 4,780,196
  • Heap Blocks: lossy=2,640,168
11. 184.723 184.723 ↓ 2.0 26,402,560 1

Bitmap Index Scan on orders_finat_brin (cost=0.00..130,826.01 rows=13,047,399 width=0) (actual time=184.723..184.723 rows=26,402,560 loops=1)

  • Index Cond: ((finished_at >= ((now() - '92 days'::interval))::date) AND (finished_at <= ((now())::date - '00:00:01'::interval)))
12. 70,218.775 1,315,697.483 ↓ 1.0 204,707,663 1

Hash (cost=24,169,565.16..24,169,565.16 rows=204,246,416 width=13) (actual time=1,315,697.483..1,315,697.483 rows=204,707,663 loops=1)

  • Buckets: 8,388,608 Batches: 64 Memory Usage: 215,764kB
13. 1,245,478.708 1,245,478.708 ↓ 1.0 204,707,663 1

Seq Scan on items it (cost=0.00..24,169,565.16 rows=204,246,416 width=13) (actual time=0.027..1,245,478.708 rows=204,707,663 loops=1)

14. 63.016 631.883 ↑ 2.3 171,301 1

Hash (cost=8,665.44..8,665.44 rows=392,244 width=33) (actual time=631.883..631.883 rows=171,301 loops=1)

  • Buckets: 524,288 Batches: 1 Memory Usage: 12,143kB
15. 568.867 568.867 ↓ 1.0 392,440 1

Seq Scan on return_refund_items rrt (cost=0.00..8,665.44 rows=392,244 width=33) (actual time=17.015..568.867 rows=392,440 loops=1)

16. 62.918 133,872.752 ↑ 1.1 406,111 1

Subquery Scan on *SELECT* 2 (cost=7,437,624.80..10,634,711.22 rows=458,107 width=66) (actual time=110,729.683..133,872.752 rows=406,111 loops=1)

17. 679.301 133,809.834 ↑ 1.1 406,111 1

Nested Loop (cost=7,437,624.80..10,630,130.15 rows=458,107 width=102) (actual time=110,729.681..133,809.834 rows=406,111 loops=1)

18. 1,061.750 113,745.994 ↑ 1.1 412,437 1

Hash Join (cost=7,437,624.23..7,578,068.80 rows=458,107 width=63) (actual time=110,698.210..113,745.994 rows=412,437 loops=1)

  • Hash Cond: (dr.dispensed_drug_id = dd.id)
19. 1,987.204 1,987.204 ↓ 1.0 4,394,731 1

Seq Scan on drugs dr (cost=0.00..119,389.00 rows=4,393,200 width=17) (actual time=0.019..1,987.204 rows=4,394,731 loops=1)

20. 73.361 110,697.040 ↑ 1.1 164,977 1

Hash (cost=7,435,447.17..7,435,447.17 rows=174,165 width=54) (actual time=110,697.039..110,697.040 rows=164,977 loops=1)

  • Buckets: 262,144 Batches: 1 Memory Usage: 12,993kB
21. 55.827 110,623.679 ↑ 1.1 164,977 1

Hash Left Join (cost=4,321,901.14..7,435,447.17 rows=174,165 width=54) (actual time=84,081.606..110,623.679 rows=164,977 loops=1)

  • Hash Cond: (oi_1.id = rrt_1.order_item_id)
22. 2,323.612 110,438.131 ↑ 1.1 164,977 1

Hash Join (cost=4,308,332.65..7,421,005.46 rows=174,165 width=29) (actual time=83,946.322..110,438.131 rows=164,977 loops=1)

  • Hash Cond: (oi_1.order_id = o_1.id)
23. 1,385.282 53,501.393 ↓ 1.0 1,664,866 1

Gather (cost=921,013.11..3,924,750.88 rows=1,643,626 width=12) (actual time=27,344.225..53,501.393 rows=1,664,866 loops=1)

  • Workers Planned: 3
  • Workers Launched: 3
  • -> Nested Loop (cost=920,013.11..3759388.28 rows=530,202 width=12) (actual time=27,338.064..52413.051rows=416,216 loops=4)
24. 2,961.509 30,410.635 ↑ 1.3 417,413 4 / 4

Hash Join (cost=920,012.55..1,022,541.31 rows=538,782 width=8) (actual time=27,337.167..30,410.635 rows=417,413 loops=4)

  • Hash Cond: (dd.prescription_id = p.id)
25. 188.187 188.187 ↑ 1.3 417,652 4 / 4

Parallel Seq Scan on dispensed_drugs dd (cost=0.00..23,103.82 rows=538,782 width=8) (actual time=0.069..188.187 rows=417,652 loops=4)

26. 2,907.859 27,260.939 ↓ 1.0 14,957,123 4 / 4

Hash (cost=674,827.02..674,827.02 rows=14,944,602 width=8) (actual time=27,260.939..27,260.939 rows=14,957,123 loops=4)

  • Buckets: 8,388,608 Batches: 4 Memory Usage: 211,377kB
27. 24,353.080 24,353.080 ↓ 1.0 14,957,123 4 / 4

Seq Scan on prescriptions p (cost=0.00..674,827.02 rows=14,944,602 width=8) (actual time=0.055..24,353.080 rows=14,957,123 loops=4)

28. 21,705.476 21,705.476 ↑ 1.0 1 1,669,652 / 4

Index Scan using indx_order_items_2 on order_items oi_1 (cost=0.57..5.07 rows=1 width=8) (actual time=0.052..0.052 rows=1 loops=1,669,652)

  • Index Cond: (id = p.order_item_id)
  • Filter: ((((category)::text <> 'ENGROS'::text) OR (category IS NULL)) AND (((category)::text <> 'ENGROS'::text) OR (category IS NULL)))
29. 2,117.149 54,613.126 ↑ 1.2 8,339,814 1

Hash (cost=3,199,257.97..3,199,257.97 rows=9,725,966 width=25) (actual time=54,613.126..54,613.126 rows=8,339,814 loops=1)

  • Buckets: 4,194,304 Batches: 4 Memory Usage: 154,824kB
30. 52,304.583 52,495.977 ↑ 1.2 8,339,814 1

Bitmap Heap Scan on orders o_1 (cost=133,257.50..3,199,257.97 rows=9,725,966 width=25) (actual time=199.075..52,495.977 rows=8,339,814 loops=1)

  • Recheck Cond: ((finished_at >= ((now() - '92 days'::interval))::date) AND (finished_at <= ((now())::date - '00:00:01'::interval)))
  • Rows Removed by Index Recheck: 78,688,324
  • Filter: (status = 210)
  • Rows Removed by Filter: 4,780,196
  • Heap Blocks: lossy=2,640,168
31. 191.394 191.394 ↓ 2.0 26,402,560 1

Bitmap Index Scan on orders_finat_brin (cost=0.00..130,826.01 rows=13,047,399 width=0) (actual time=191.394..191.394 rows=26,402,560 loops=1)

  • Index Cond: ((finished_at >= ((now() - '92 days'::interval))::date) AND (finished_at <= ((now())::date - '00:00:01'::interval)))
32. 52.178 129.721 ↑ 2.3 171,301 1

Hash (cost=8,665.44..8,665.44 rows=392,244 width=33) (actual time=129.721..129.721 rows=171,301 loops=1)

  • Buckets: 524,288 Batches: 1 Memory Usage: 12,143kB
33. 77.543 77.543 ↓ 1.0 392,440 1

Seq Scan on return_refund_items rrt_1 (cost=0.00..8,665.44 rows=392,244 width=33) (actual time=0.013..77.543 rows=392,440 loops=1)

34. 19,384.539 19,384.539 ↑ 1.0 1 412,437

Index Scan using items_pkey on items it_1 (cost=0.57..6.61 rows=1 width=13) (actual time=0.036..0.047 rows=1 loops=412,437)

  • Index Cond: (id = dr.item_id)
35. 690.659 263,028.682 ↓ 1.2 4,497,106 1

Subquery Scan on *SELECT* 3 (cost=9,900,591.63..31,835,205.85 rows=3,752,847 width=66) (actual time=96,456.896..263,028.682 rows=4,497,106 loops=1)

36. 6,994.463 262,338.023 ↓ 1.2 4,497,106 1

Nested Loop (cost=9,900,591.63..31,797,677.38 rows=3,752,847 width=102) (actual time=96,456.894..262,338.023 rows=4,497,106 loops=1)

37. 10,678.736 132,698.220 ↓ 1.2 4,542,420 1

Hash Join (cost=9,900,591.06..11,124,219.33 rows=3,753,097 width=63) (actual time=96,455.508..132,698.220 rows=4,542,420 loops=1)

  • Hash Cond: (ndd.prescription_id = p_1.id)
38. 26,507.937 26,507.937 ↓ 1.0 36,002,268 1

Seq Scan on non_dispensed_drugs ndd (cost=0.00..1,006,132.20 rows=35,993,020 width=17) (actual time=0.011..26,507.937 rows=36,002,268 loops=1)

39. 1,109.375 95,511.547 ↓ 1.2 1,868,697 1

Hash (cost=9,881,111.45..9,881,111.45 rows=1,558,369 width=54) (actual time=95,511.547..95,511.547 rows=1,868,697 loops=1)

  • Buckets: 2,097,152 Batches: 1 Memory Usage: 140,332kB
  • -> Hash Left Join (cost=4,321,900.58..9881111.45 rows=1,558,369 width=54) (actual time=64,686.855..94924.927 rows=1,868,697loops=1)
  • Hash Cond: (oi_2.id = rrt_2.order_item_id)
40. 8,317.855 94,265.789 ↓ 1.2 1,868,697 1

Hash Join (cost=4,308,332.09..9,859,729.70 rows=1,558,369 width=29) (actual time=64,547.335..94,265.789 rows=1,868,697 loops=1)

  • Hash Cond: (oi_2.order_id = o_2.id)
41. 1,027.330 30,277.190 ↓ 1.0 14,752,757 1

Gather (cost=921,012.55..6,026,120.46 rows=14,706,601 width=12) (actual time=6,628.311..30,277.190 rows=14,752,757 loops=1)

  • Workers Planned: 4
  • Workers Launched: 4
42. 13,331.870 29,249.860 ↑ 1.2 2,950,551 5 / 5

Hash Join (cost=920,012.55..4,554,460.35 rows=3,676,650 width=12) (actual time=7,946.627..29,249.860 rows=2,950,551 loops=5)

  • Hash Cond: (oi_2.id = p_1.order_item_id)
43. 8,049.315 8,049.315 ↑ 1.3 25,003,389 5 / 5

Parallel Seq Scan on order_items oi_2 (cost=0.00..2,627,933.64 rows=31,358,949 width=8) (actual time=0.031..8,049.315 rows=25,003,389 loops=5)

  • Filter: ((((category)::text <> 'ENGROS'::text) OR (category IS NULL)) AND (((category)::text <> 'ENGROS'::text) OR (category IS NULL)))
  • Rows Removed by Filter: 116,077
44. 2,916.390 7,868.675 ↑ 1.0 14,752,861 5 / 5

Hash (cost=674,827.02..674,827.02 rows=14,944,602 width=8) (actual time=7,868.675..7,868.675 rows=14,752,861 loops=5)

  • Buckets: 8,388,608 Batches: 4 Memory Usage: 209,645kB
45. 4,952.285 4,952.285 ↓ 1.0 14,957,123 5 / 5

Seq Scan on prescriptions p_1 (cost=0.00..674,827.02 rows=14,944,602 width=8) (actual time=0.054..4,952.285 rows=14,957,123 loops=5)

46. 2,541.298 55,670.744 ↑ 1.2 8,339,814 1

Hash (cost=3,199,257.97..3,199,257.97 rows=9,725,966 width=25) (actual time=55,670.744..55,670.744 rows=8,339,814 loops=1)

  • Buckets: 4,194,304 Batches: 4 Memory Usage: 154,824kB
47. 52,945.880 53,129.446 ↑ 1.2 8,339,814 1

Bitmap Heap Scan on orders o_2 (cost=133,257.50..3,199,257.97 rows=9,725,966 width=25) (actual time=191.336..53,129.446 rows=8,339,814 loops=1)

  • Recheck Cond: ((finished_at >= ((now() - '92 days'::interval))::date) AND (finished_at <= ((now())::date - '00:00:01'::interval)))
  • Rows Removed by Index Recheck: 78,688,324
  • Filter: (status = 210)
  • Rows Removed by Filter: 4,780,196
  • Heap Blocks: lossy=2,640,168
48. 183.566 183.566 ↓ 2.0 26,402,560 1

Bitmap Index Scan on orders_finat_brin (cost=0.00..130,826.01 rows=13,047,399 width=0) (actual time=183.566..183.566 rows=26,402,560 loops=1)

  • Index Cond: ((finished_at >= ((now() - '92 days'::interval))::date) AND (finished_at <= ((now())::date - '00:00:01'::interval)))
49. 49.019 136.383 ↑ 2.3 171,301 1

Hash (cost=8,665.44..8,665.44 rows=392,244 width=33) (actual time=136.383..136.383 rows=171,301 loops=1)

  • Buckets: 524,288 Batches: 1 Memory Usage: 12,143kB
50. 87.364 87.364 ↓ 1.0 392,440 1

Seq Scan on return_refund_items rrt_2 (cost=0.00..8,665.44 rows=392,244 width=33) (actual time=0.014..87.364 rows=392,440 loops=1)

51. 122,645.340 122,645.340 ↑ 1.0 1 4,542,420

Index Scan using items_pkey on items it_2 (cost=0.57..5.46 rows=1 width=13) (actual time=0.012..0.027 rows=1 loops=4,542,420)

  • Index Cond: (id = ndd.item_id)
Planning time : 13.864 ms
Execution time : 2,209,428.948 ms