routes with very long running postgres requests

Some routes

/occurrence/species ~ BIEN::BIEN_occurrence_species
/occurrence/genus ~ BIEN::BIEN_occurrence_genus
/occurrence/family ~ BIEN::BIEN_occurrence_family
/occurrence/spatial ~ BIEN::BIEN_occurrence_spatialpolygons
/occurrence/count ~ BIEN::BIEN_occurrence_records_per_species

sometimes take a very long time to run - and this isn't just a unicorn server or caddy server thing - have checked that the request is taking a long time on the postgres side of things - looks like there are indices on the table view_full_occurrence_individual so that can't be it i assume

thoughts @ojalaquellueva ?

I can send some eg postgres requests behind the API requests and you can try on your server and see if they're also taking a long time. if they just take a long time and there's no way to speed up, may need to serve these long running requests in a separate sort of async service so as not to bog down the main API

e.g., query that takes a long time:

SELECT scrubbed_species_binomial, latitude, longitude,date_collected,datasource,dataset,dataowner,custodial_institution_codes,collection_code,a.datasource_id     
    FROM (
        SELECT * FROM view_full_occurrence_individual 
        WHERE higher_plant_group IS NOT NULL AND is_geovalid =1 
            AND latitude BETWEEN  27.31 AND 37.29 
            AND longitude BETWEEN  -117.13  AND  -108.62 
    ) a
    WHERE st_intersects(ST_GeographyFromText('SRID=4326; POLYGON((-114.125 34.230,-112.346 34.230,-112.346 32.450,-114.125 32.450,-114.125 34.230)) '),a.geom) 
    AND (is_cultivated = 0 OR is_cultivated IS NULL) 
    AND is_new_world = 1  
    AND ( native_status IS NULL OR native_status NOT IN ( 'I', 'Ie' ) ) 
    AND higher_plant_group IS NOT NULL 
    AND (is_geovalid = 1 OR is_geovalid IS NULL) 
    ORDER BY scrubbed_species_binomial;

on my server: This query takes at least 5+ minutes, didn't wait for it to finish on vegbiendev.nceas.ucsb.edu: takes ~ 1 min 20 sec

Even at the shorter time of the vegbiendev.nceas.ucsb.edu server, that's too long for a normal REST API route - could these longer queries be sped up? Additional indices perhaps? Not sure why the difference in my server and vegbiendev.nceas.ucsb.edu - must be different setups.

In another example

on my server this takes 5+ minutes (didn't wait for it to finish)

SELECT scrubbed_genus, scrubbed_species_binomial ,latitude, longitude,date_collected, datasource,dataset,dataowner,custodial_institution_codes,collection_code,view_full_occurrence_individual.datasource_id
    FROM view_full_occurrence_individual
    WHERE scrubbed_genus in ( 'Quercus' ) AND (is_cultivated = 0 OR is_cultivated IS NULL) AND is_new_world = 1  AND ( native_status IS NULL OR native_status NOT IN ( 'I', 'Ie' ) )
    AND higher_plant_group IS NOT NULL AND (is_geovalid = 1 OR is_geovalid IS NULL)
    ORDER BY scrubbed_species_binomial;

but doing the same SQL query on vegbiendev.nceas.ucsb.edu takes ~ 1 min 40 sec

sckott / bienapi

routes with very long running postgres requests #11