Those GeoPackages do have spatial indexes ready but there is a small glitch in how the GeoPackage databases are created. That can be seen by reading the contents of the gpkg_geometry_columns table
SELECT ROWID, "table_name", "column_name", "geometry_type_name", "srs_id", "z", "m" FROM "gpkg_geometry_columns" ORDER BY ROWID
As you can see, all three tables (point, line, polygon) are registered into geometry_columns to include general geometries. QGIS, that can only handle one kind of geometries on the same layer, wants to know what geometries the source data contains and because it can't get that information from the metadata it must read all the geometries and check them one by one and that's slow.
You can create a GeoPackage that suits better for QGIS with these commands:
ogr2ogr -f gpkg test.gpkg railway_EPSG4326.gpkg railway_EPSG4326_point -nlt point
ogr2ogr -f gpkg -append -update test.gpkg railway_EPSG4326.gpkg railway_EPSG4326_line -nlt multilinestring
ogr2ogr -f gpkg -append -update test.gpkg railway_EPSG4326.gpkg railway_EPSG4326_polygon -nlt multipolygon
Now the metadata looks like this
and QGIS can make a connection and add data into the project in seconds while it used to take about one minute with my computer.
With just this database it would be enough to update the GEOMETRY_TYPE_NAME column to have values POINT, LINESTRING and POLYGON but generally that is not a safe
thing to do.
Consider to report these findings to the maintainers of the osmdata.xyz site.