Arvados

Looks like it's mostly waiting for postgres to figure out that 787 of 484K collections match.

Takes <1s if you add &count=none.

explain analyze SELECT COUNT("collections"."id") FROM "collections" WHERE (collections.owner_uuid NOT IN (SELECT target_uuid FROM materialized_permission_view WHERE trashed = 1) AND collections.is_trashed = false AND collections.uuid = collections.current_version_uuid) AND ((to_tsvector('english', substr(coalesce(owner_uuid,'') || ' ' || coalesce(modified_by_client_uuid,'') || ' ' || coalesce(modified_by_user_uuid,'') || ' ' || coalesce(portable_data_hash,'') || ' ' || coalesce(uuid,'') || ' ' || coalesce(name,'') || ' ' || coalesce(description,'') || ' ' || coalesce(properties::text,'') || ' ' || coalesce(file_names,''), 0, 8000)) @@ to_tsquery('test')) AND (1=0 OR 1=1 OR 1=0) AND (1=0 OR 1=0 OR 1=1))

 Aggregate  (cost=5510.48..5510.49 rows=1 width=4) (actual time=24189.179..24189.179 rows=1 loops=1)
   ->  Index Scan using index_collections_on_owner_uuid_and_name on collections  (cost=6.47..5510.46 rows=5 width=4) (actual time=45.039..24189.151 rows=32 loops=1)
         Filter: ((NOT (hashed SubPlan 1)) AND (to_tsvector('english'::regconfig, substr((((((((((((((((((COALESCE(owner_uuid, ''::character varying))::text || ' '::text) || (COALESCE(modified_by_client_uuid, ''::character varying))::text) || ' '::text) || (COALESCE(modified_by_user_uuid, ''::character varying))::text) || ' '::text) || (COALESCE(portable_data_hash, ''::character varying))::text) || ' '::text) || (COALESCE(uuid, ''::character varying))::text) || ' '::text) || (COALESCE(name, ''::character varying))::text) || ' '::text) || (COALESCE(description, ''::character varying))::text) || ' '::text) || COALESCE((properties)::text, ''::text)) || ' '::text) || COALESCE(file_names, ''::text)), 0, 8000)) @@ to_tsquery('test'::text)))
         Rows Removed by Filter: 479437
         SubPlan 1
           ->  Index Only Scan using permission_target_trashed on materialized_permission_view  (cost=0.28..6.04 rows=1 width=28) (actual time=0.017..0.048 rows=34 loops=1)
                 Index Cond: (trashed = 1)
                 Heap Fetches: 34
 Planning time: 0.278 ms
 Execution time: 24189.271 ms

The index uses substr(..., 1000000) but the query uses substr(..., 8000).

"explain analyze" execution time drops from 24 s to 0.3 s with the substr() argument changed to 1000000.