Searching on "minimally covers" times out

[junbolau]

The search query for "Minimally covers" under modular curves is taking too long. Example: ( https://beta.lmfdb.org/ModularCurve/Q/?covers=1.1.0.a.1 ). Discussion with Edgar and David suggests that there is no clear solution.

[edgarcosta]

this is the best I could manage:

lmfdb=# explain analyze with foo as MATERIALIZED (select * from gps_gl2zhat_fine where parents @> '{1.1.0.a.1}'::text[]) select label from foo order by "coarse_level", "coarse_index" LIMIT 1000;
                                                                     QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=306233.05..306235.55 rows=1000 width=40) (actual time=23.327..23.362 rows=373 loops=1)
   CTE foo
     ->  Bitmap Heap Scan on gps_gl2zhat_fine  (cost=800.39..299934.02 rows=84179 width=2636) (actual time=0.110..1.279 rows=373 loops=1)
           Recheck Cond: (parents @> '{1.1.0.a.1}'::text[])
           Heap Blocks: exact=347
           ->  Bitmap Index Scan on gps_gl2zhat_fine_parents_gin  (cost=0.00..779.34 rows=84179 width=0) (actual time=0.066..0.066 rows=373 loops=1)
                 Index Cond: (parents @> '{1.1.0.a.1}'::text[])
   ->  Sort  (cost=6299.02..6509.47 rows=84179 width=40) (actual time=2.046..2.061 rows=373 loops=1)
         Sort Key: foo.coarse_level, foo.coarse_index
         Sort Method: quicksort  Memory: 54kB
         ->  CTE Scan on foo  (cost=0.00..1683.58 rows=84179 width=40) (actual time=0.117..1.937 rows=373 loops=1)
 Planning Time: 2.221 ms
 JIT:
   Functions: 7
   Options: Inlining false, Optimization false, Expressions true, Deforming true
   Timing: Generation 3.365 ms, Inlining 0.000 ms, Optimization 1.907 ms, Emission 19.138 ms, Total 24.411 ms
 Execution Time: 26.884 ms
(17 rows)

Time: 29.856 ms

[edgarcosta]

versus the unmaterialized version

lmfdb=# explain analyze with foo as (select * from gps_gl2zhat_fine where parents @> '{1.1.0.a.1}'::text[]) select label from foo order by "coarse_level", "coarse_index" LIMIT 1000;
                                                                                   QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.56..202362.21 rows=1000 width=29) (actual time=12.682..21691.984 rows=373 loops=1)
   ->  Index Scan using gps_gl2zhat_fine_co_co_ge_co_co_le_fi on gps_gl2zhat_fine  (cost=0.56..17034601.31 rows=84179 width=29) (actual time=0.031..21679.258 rows=373 loops=1)
         Filter: (parents @> '{1.1.0.a.1}'::text[])
         Rows Removed by Filter: 16835619
 Planning Time: 0.514 ms
 JIT:
   Functions: 5
   Options: Inlining false, Optimization false, Expressions true, Deforming true
   Timing: Generation 1.565 ms, Inlining 0.000 ms, Optimization 0.876 ms, Emission 11.667 ms, Total 14.108 ms
 Execution Time: 21693.692 ms
(10 rows)

Time: 21696.310 ms (00:21.696)