Number field search is slow

[jwj61]

From the feedback page, the following search times out:

https://www.lmfdb.org/NumberField/?degree=2&sort_order=h&sort_order=h

[edgarcosta]

lmfdb=# EXPLAIN ANALYZE SELECT "class_group", "coeffs", "degree", "r2", "disc_abs", "disc_sign", "galois_label", "label", "ramps", "used_grh", "cm", "is_galois", "torsion_order", "regulator", "rd", "grd", "monogenic", "num_ram" FROM "nf_fields" WHERE "degree" = 2 ORDER BY "class_number", "degree", "disc_abs", "disc_sign", "iso_number" LIMIT 50;
                                                                               QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=5938.35..6215.52 rows=50 width=232) (actual time=101555.912..101555.929 rows=50 loops=1)
   ->  Incremental Sort  (cost=5938.35..7476412.54 rows=1347641 width=232) (actual time=101555.910..101555.920 rows=50 loops=1)
         Sort Key: class_number, disc_abs, disc_sign, iso_number
         Presorted Key: class_number
         Full-sort Groups: 1  Sort Method: quicksort  Average Memory: 42kB  Peak Memory: 42kB
         Pre-sorted Groups: 1  Sort Method: top-N heapsort  Average Memory: 38kB  Peak Memory: 38kB
         ->  Index Scan using nf_fields_class_number_id on nf_fields  (cost=0.56..7390705.86 rows=1347641 width=232) (actual time=0.189..101423.677 rows=177169 loops=1)
               Filter: (degree = 2)
               Rows Removed by Filter: 10112321
 Planning Time: 3.294 ms
 Execution Time: 101556.213 ms
(11 rows)

[edgarcosta]

I will manually add an index on the right sort statement, and see if things improve

[edgarcosta]

fixed

lmfdb=# EXPLAIN ANALYZE SELECT "class_group", "coeffs", "degree", "r2", "disc_abs", "disc_sign", "galois_label", "label", "ramps", "used_grh", "cm", "is_galois", "torsion_order", "regulator", "rd", "grd", "monogenic", "num_ram" FROM "nf_fields" WHERE "degree" = 2 ORDER BY "class_number", "degree", "disc_abs", "disc_sign", "iso_number" LIMIT 50;
                                                                      QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.56..277.11 rows=50 width=232) (actual time=0.076..0.129 rows=50 loops=1)
   ->  Index Scan using nf_fields_cl_di_di_is on nf_fields  (cost=0.56..7460175.48 rows=1348802 width=232) (actual time=0.075..0.123 rows=50 loops=1)
         Filter: (degree = 2)
         Rows Removed by Filter: 25
 Planning Time: 0.601 ms
 Execution Time: 0.156 ms
(6 rows)

[jwj61]

The feedback page reports that this similar search times out:

https://www.lmfdb.org/NumberField/?degree=8&cm_field=yes&sort_order=h&sort_order=h

[roed314]

This kind of thing is always going to be whack-a-mole. We don't have the disc space to support all possible combinations of filtering and sorting. Is it worth changing the gunicorn timeout threshold so that we users can get some kind of slow answer via sequential scan when we don't have the right index? I don't know what consequences this would have, but I assume that we don't hit the timeout all that often, since it would be showing up in the flasklog otherwise.

[AndrewVSutherland]

Recording the URL from #6039 here for testing: https://www.lmfdb.org/NumberField/?degree=8&subfield=4.4.2304.1.

I agree that allowing a longer timeout would be desirable, but we should discuss this with @edgarcosta. The ideal solution might be to have the error message offer a button you can click to retry with a longer timeout, rather than changing the timeout across the board (this would help protect us from bots).

[edgarcosta]

I do not see an easy way to increase the timeout interactively.
At the moment, we have two timeouts 30s timeouts in place:

• the load balancing level
• and gunicorn
  These are both outside the app environment.

If we ever figure out how to do #6221, perhaps we can tackle this by informing the user that the query is taking longer than expected (which perhaps avoids the timeout). Still, we would need some interaction from the user to prevent abuse.

The right approach is to make the user aware of:

• beta.lmfdb.org, where the database is in a much nicer machine, or the code
• and that they can run long queries locally

[edgarcosta]

Another alternative, is to list the results unsurted, as many queries become sequential due to the sorting.

[AndrewVSutherland]

Hmm, how would that work? Is this something that could be triggered on timeout?

[roed314]

On its own #6221 won't solve the problem, since the user can't get to the search results page where they're able to download results. But we could probably add a link to the error message (if we got #6221 working).

[edgarcosta]

I agree, but both suffer from the technical issue of not knowing how to keep the connections alive without the timeouts kicking in.

@AndrewVSutherland , our timeout for searches is shorter than the load balancer/gunicorn timeouts, so the users get a flask error informing them of the problem.
Many of these queries fail, as the results are not rare enough, and since we asked for a limited number (and sorted), the planner prefers to sequential scan the table, as it expects to find the first X fairly quickly. This is not so much an issue if we do not ask for the results to be sorted, so we could simply offer an option to present unsorted results.

[AndrewVSutherland]

@edgarcosta I like this idea, maybe it would be worth trying it out to see how well it does on some of our hard queries.