Repository: skyportal/healpix-alchemy (language: python)

Issue:
Add benchmark of integrated probability
Add benchmark of integrated probability within the union of a set of fields.

As reported by @mcoughlin and @Tohuvavohu, this operation is slower than it should be:

```
pytest --benchmark-group-by=fullfunc --benchmark-sort=fullname --benchmark-json benchmark_results.json healpix_alchemy/tests/benchmarks/test_benchmarks.py ======================================================================================================== test session starts =========================================================================================================
platform darwin -- Python 3.8.13, pytest-6.2.5, py-1.11.0, pluggy-1.0.0
benchmark: 3.4.1 (defaults: timer=time.perf_counter disable_gc=False min_rounds=5 min_time=0.000005 max_time=1.0 calibration_precision=10 warmup=False warmup_iterations=100000)
rootdir: /Users/lpsinger/src/healpix-alchemy, configfile: pyproject.toml
plugins: benchmark-3.4.1, doctestplus-0.11.2, postgresql-3.1.2, cov-3.0.0
collected 10 items                                                                                                                                                                                                                   

healpix_alchemy/tests/benchmarks/test_benchmarks.py ..........                                                                                                                                                                 [100%]
Wrote benchmark data in: <_io.BufferedWriter name='benchmark_results.json'>



------------------------------------------------- benchmark 'healpix_alchemy/tests/benchmarks/test_benchmarks.py::test_integrated_probability': 10 tests ------------------------------------------------
Name (time in s)                            Min                 Max                Mean            StdDev              Median               IQR            Outliers     OPS            Rounds  Iterations
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
test_integrated_probability[10000]       2.2970 (4.37)       2.3157 (4.17)       2.3075 (4.24)     0.0069 (1.0)        2.3079 (4.16)     0.0081 (1.0)           2;0  0.4334 (0.24)          5           1
test_integrated_probability[1291]      129.9643 (247.25)   131.1182 (235.83)   130.4707 (239.47)   0.4317 (62.57)    130.4522 (235.29)   0.5523 (68.46)         2;0  0.0077 (0.00)          5           1
test_integrated_probability[166]        61.9228 (117.81)    65.9366 (118.60)    62.9024 (115.45)   1.7139 (248.39)    62.0978 (112.00)   1.4208 (176.13)        1;1  0.0159 (0.01)          5           1
test_integrated_probability[1]           0.5256 (1.0)        0.5560 (1.0)        0.5448 (1.0)      0.0146 (2.11)       0.5544 (1.0)      0.0247 (3.07)          1;0  1.8354 (1.0)           5           1
test_integrated_probability[21]         10.4779 (19.93)     10.9063 (19.62)     10.6447 (19.54)    0.1678 (24.32)     10.5684 (19.06)    0.2152 (26.68)         1;0  0.0939 (0.05)          5           1
test_integrated_probability[2]           0.9655 (1.84)       1.0280 (1.85)       0.9987 (1.83)     0.0251 (3.64)       1.0072 (1.82)     0.0389 (4.83)          2;0  1.0013 (0.55)          5           1
test_integrated_probability[3593]       25.7560 (49.00)     25.9841 (46.74)     25.8426 (47.43)    0.0882 (12.79)     25.8226 (46.57)    0.1113 (13.79)         1;0  0.0387 (0.02)          5           1
test_integrated_probability[464]       120.1516 (228.58)   121.1324 (217.87)   120.4673 (221.11)   0.3862 (55.97)    120.3677 (217.10)   0.3683 (45.66)         1;0  0.0083 (0.00)          5           1
test_integrated_probability[59]         27.8548 (52.99)     28.8851 (51.95)     28.3581 (52.05)    0.4449 (64.48)     28.5542 (51.50)    0.7297 (90.45)         2;0  0.0353 (0.02)          5           1
test_integrated_probability[7]           3.5211 (6.70)       3.6586 (6.58)       3.5808 (6.57)     0.0544 (7.88)       3.5570 (6.42)     0.0771 (9.55)          2;0  0.2793 (0.15)          5           1
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Legend:
  Outliers: 1 Standard Deviation from Mean; 1.5 IQR (InterQuartile Range) from 1st Quartile and 3rd Quartile.
  OPS: Operations Per Second, computed as 1 / Mean
================================================================================================== 10 passed in 2787.96s (0:46:27) ===================================================================================================
```


Discussion / hints:
@mcoughlin, I had time to isolate this test case for the performance of the integrated probability calculation, but I haven't had time yet to try to tune the query and optimize it. Do you have a student who is an SQL wizard that you could assign to take a look at it?

Fix patch:
diff --git a/README.md b/README.md
index 2c12893..5e59871 100644
--- a/README.md
+++ b/README.md
@@ -371,6 +371,15 @@ Load a sky map for LIGO/Virgo event [GW200115_042309] ([S200115j]) into the
 
 ```
 
+Last, run [`ANALYZE`](https://www.postgresql.org/docs/current/sql-analyze.html)
+to prepare the data for use:
+
+```pycon
+>>> session.execute('ANALYZE')
+<sqlalchemy.engine.cursor.CursorResult object at 0x...>
+
+```
+
 ### Sample Queries
 
 #### What is the area of each field?
diff --git a/pyproject.toml b/pyproject.toml
index 1fecf27..9a9e0fa 100644
--- a/pyproject.toml
+++ b/pyproject.toml
@@ -33,6 +33,7 @@ pytest-postgresql = "<4.0.0"  # pytest-postgresql 4.0.0 uses psycopg3, but sqlal
 addopts = "--doctest-glob *.md"
 doctest_plus = true
 doctest_optionflags = [
+    "ELLIPSIS",
     "FLOAT_CMP"
 ]
 filterwarnings = [


Test patch:
diff --git a/healpix_alchemy/tests/benchmarks/test_benchmarks.py b/healpix_alchemy/tests/benchmarks/test_benchmarks.py
index cf18551..32fcdb2 100644
--- a/healpix_alchemy/tests/benchmarks/test_benchmarks.py
+++ b/healpix_alchemy/tests/benchmarks/test_benchmarks.py
@@ -12,6 +12,7 @@
 def bench(benchmark, session):
 
     def _func(query):
+        session.execute('ANALYZE')
         return benchmark(lambda: session.execute(query).all())
 
     return _func