Bad performances on manage view
Since !349 (merged) (and #384 (closed)), the performances on the /manage view are terribly slow.
In !349 (merged), the request was updated to use the cache, but using the cache takes a long time (on big DB) to be filled, and it implies a join at the execution.
Using the code from !349 (merged)
EXPLAIN SELECT COUNT(rel_is0.eid_from)
FROM cw_CWEType AS _ETYPE, is_relation AS rel_is0
WHERE rel_is0.eid_to=_ETYPE.cw_eid AND _ETYPE.cw_name='Manifestation';
QUERY PLAN
─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
Aggregate (cost=17041.91..17041.92 rows=1 width=8)
-> Nested Loop (cost=830.75..16976.70 rows=26085 width=4)
-> Seq Scan on cw_cwetype _etype (cost=0.00..3.56 rows=1 width=4)
Filter: ((cw_name)::text = 'Manifestation'::text)
-> Bitmap Heap Scan on is_relation rel_is0 (cost=830.75..16232.08 rows=74106 width=8)
Recheck Cond: (eid_to = _etype.cw_eid)
-> Bitmap Index Scan on idx_15f78b634a58c4349a6e02242be9d597 (cost=0.00..812.22 rows=74106 width=0)
Index Cond: (eid_to = _etype.cw_eid)
(8 lignes)
we can observe the join.
and using the previous code, there is no join, and only the index is used.
EXPLAIN SELECT COUNT(_X.cw_eid)
FROM cw_Manifestation AS _X;
QUERY PLAN
─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
Finalize Aggregate (cost=7780.79..7780.80 rows=1 width=8)
-> Gather (cost=7780.58..7780.79 rows=2 width=8)
Workers Planned: 2
-> Partial Aggregate (cost=6780.58..6780.59 rows=1 width=8)
-> Parallel Index Only Scan using cw_manifestation_pkey on cw_manifestation _x (cost=0.42..6448.73 rows=132741 width=4)
(5 lignes)
In [12]: %timeit rql("Any COUNT(X) WHERE X is ETYPE, ETYPE name %(x)s", {"x": "Manifestation"})
68.8 ms ± 399 µs per loop (mean ± std. dev. of 7 runs, 10 loops each)
In [13]: %timeit rql("Any COUNT(X) WHERE X is %(x)s" % {"x": "Manifestation"})
24.8 ms ± 1.31 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)
On a small db, the “naive” version takes 24ms while the version from !349 (merged) takes 68ms (3 times longer). On a big db, it may takes several minutes.