URL list should be sorted before given to the user!?

[pblain]

Migrated from here: aodn/aodn-portal#2686

[ggalibert]

@smancini we need to identify which layers would make sense to be provided sorted and in which way.

For example, I'm suggesting to now sort ACORN hourly files alphabetically (equivalent to chronologically in this case): aodn/geoserver-config#567

[ghost]

I don't think we should be on a blitz to sort as many collections as possible while there are performance problems partially caused by trying to sort huge tables.

See: https://github.com/aodn/issues/issues/199

Having sorts behind synchronous web requests has the potential to cause a bunch more performance problems, so I'd suggest this needs to be approached delicately from a systems perspective, and not considered as a "content enhancement", because sorting is not free.

For example, the acorn change immediately makes the query take 3 times as long to complete. This might be acceptable in the context of that individual use case, but this is just one small example of how this can drastically worsen performance:

"Hash Join  (cost=6993.49..36430.79 rows=47605 width=191) (actual time=71.228..623.440 rows=45588 loops=1)"
"  Hash Cond: (f.id = t.file_id)"
"  ->  Seq Scan on indexed_file f  (cost=0.00..13552.45 rows=363945 width=125) (actual time=0.045..233.309 rows=362985 loops=1)"
"  ->  Hash  (cost=5979.42..5979.42 rows=47605 width=46) (actual time=70.742..70.742 rows=45588 loops=1)"
"        Buckets: 65536  Batches: 2  Memory Usage: 2302kB"
"        ->  Bitmap Heap Scan on timeseries t  (cost=1781.36..5979.42 rows=47605 width=46) (actual time=6.323..41.233 rows=45588 loops=1)"
"              Recheck Cond: (site_code = 'BONC, Bonney Coast'::text)"
"              Heap Blocks: exact=2317"
"              ->  Bitmap Index Scan on timeseries_deployment_instrument_depth_uc  (cost=0.00..1769.46 rows=47605 width=0) (actual time=5.986..5.986 rows=45588 loops=1)"
"                    Index Cond: (site_code = 'BONC, Bonney Coast'::text)"
"Planning time: 0.256 ms"
"Execution time: 642.099 ms"


"Sort  (cost=44525.42..44644.43 rows=47605 width=191) (actual time=1660.670..1783.368 rows=45588 loops=1)"
"  Sort Key: f.url"
"  Sort Method: external merge  Disk: 9208kB"
"  ->  Hash Join  (cost=6993.49..36430.79 rows=47605 width=191) (actual time=66.149..619.725 rows=45588 loops=1)"
"        Hash Cond: (f.id = t.file_id)"
"        ->  Seq Scan on indexed_file f  (cost=0.00..13552.45 rows=363945 width=125) (actual time=0.008..226.878 rows=362985 loops=1)"
"        ->  Hash  (cost=5979.42..5979.42 rows=47605 width=46) (actual time=65.806..65.806 rows=45588 loops=1)"
"              Buckets: 65536  Batches: 2  Memory Usage: 2302kB"
"              ->  Bitmap Heap Scan on timeseries t  (cost=1781.36..5979.42 rows=47605 width=46) (actual time=4.769..36.110 rows=45588 loops=1)"
"                    Recheck Cond: (site_code = 'BONC, Bonney Coast'::text)"
"                    Heap Blocks: exact=2317"
"                    ->  Bitmap Index Scan on timeseries_deployment_instrument_depth_uc  (cost=0.00..1769.46 rows=47605 width=0) (actual time=4.434..4.434 rows=45588 loops=1)"
"                          Index Cond: (site_code = 'BONC, Bonney Coast'::text)"
"Planning time: 0.260 ms"
"Execution time: 1804.543 ms"

In any case, like I said, this is not a simple content enhancement and must be considered in the context of the current database performance/metrics/tuning initiative(s).

[ggalibert]

I don't think this case is quite as comparable as https://github.com/aodn/issues/issues/199 because here we're not trying to sort huge amount of data, we're trying to sort a number of file URLs which is considerably smaller than a number of data in a dataset. Like shown by your benchmark, it would only take less than 2sec to retrieve the whole list of files from BONC which equates to only 45000 rows. In addition, the number of files is not going to grow exponentially like a data collections would (instruments are collecting data at a consistently growing rate) because we're only going to get 1 file every 1hour, consistently.

I think in the case of radar URLs it is pretty safe to sort and yet would enhance the user experience significantly.

[ggalibert]

In terms of amount of URLs, radar collections are probably the biggest with satellite data being second.

gsla_dm is 6000 files growing at 1 file per day.
srs_sst_l3c_1d_day_n19_gridded_url is 3300 files growing at 1 file per day.

Then probably comes the moorings because of the amount of instruments deployed on a mooring.

anmn_ts is 5700 files.

anfog_dm is 255 files.

Is this a performance problem to query and sort 45000 rows? 6000 rows? Note that this is the worse case scenario when the user requests the whole collection.

[ghost]

It's not necessarily a performance problem per se, but we do need to look at it in the context of the current performance tuning/metrics work. For example, introducing external disk sorts should be looked at in terms of the configuration around sort memory configuration etc. because it seems like a small thing but has the potential to contribute to an overall poorly performing database.

We just need to be very clear on the scope, size and growth rate of tables where this sort of thing is enabled, which is why I suggest aligning it with the other database work which is seeking optimise and improve performance. Otherwise we end up with a situation where the left hand isn't talking to the right hand and the optimisation work is undermined by overly zealous content enhancements, or content enhancements are undermined by overly zealous optimisation work!

[lbesnard]

@ggalibert can we close. I don't think this is an important task. and as mentioned in the comments given the asynchronous system in place, not easily achievable