Number field search is slow

jwj61 commented 3 months ago

From the feedback page, the following search times out:

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

edgarcosta commented 3 months ago


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 commented 3 months ago

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

edgarcosta commented 3 months ago

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 commented 2 months ago

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

LMFDB / lmfdb

Number field search is slow #5970