Reports

I am now building the query dynamically from the service based on the parameters. This causes the query to now use indices more often, but not yet in all cases. Here is the EXPLAIN ANALYZE of the new query:

Sort  (cost=19.00..19.00 rows=1 width=443) (actual time=0.037..0.038 rows=0 loops=1)
  Sort Key: ba.external_id
  Sort Method: quicksort  Memory: 25kB
  ->  Hash Right Join  (cost=6.23..18.99 rows=1 width=443) (actual time=0.021..0.022 rows=0 loops=1)
        Hash Cond: (process_cell.external_id = ba.process_cell_external_id)
        ->  Seq Scan on process_cell  (cost=0.00..12.00 rows=200 width=64) (never executed)
        ->  Hash  (cost=6.21..6.21 rows=1 width=411) (actual time=0.015..0.016 rows=0 loops=1)
              Buckets: 1024  Batches: 1  Memory Usage: 8kB
              ->  Index Scan using idx_ba_organization_id_external_id on business_asset ba  (cost=0.42..6.21 rows=1 width=411) (actual time=0.015..0.015 rows=0 loops=1)
                    Index Cond: (organization_id = '4970f599-44ab-4bab-aee4-455b995fd22b'::uuid)
"                    Filter: (((external_id ~~* concat('%', 'FA'::text, '%')) OR (description ~~* concat('%', 'FA'::text, '%')) OR (properties_yaml ~~* concat('%', 'FA'::text, '%'))) AND ((external_id ~~* concat('%', 'FF'::text, '%')) OR (description ~~* concat('%', 'FF'::text, '%')) OR (properties_yaml ~~* concat('%', 'FF'::text, '%'))))"
Planning Time: 1.388 ms
Execution Time: 0.071 ms

One example for the generated query is:

SELECT ba.*, process_cell.owners
        FROM business_asset ba
        LEFT JOIN process_cell
          ON ba.process_cell_external_id = process_cell.external_id
    
WHERE ba.organization_id = :organizationId
  AND (ba.external_id ILIKE CONCAT('%', replace(replace(:searchTerm0, '%', '\%'), '_', '\_'), '%') OR ba.description ILIKE CONCAT('%', replace(replace(:searchTerm0, '%', '\%'), '_', '\_'), '%') OR ba.properties_yaml ILIKE CONCAT('%', replace(replace(:searchTerm0, '%', '\%'), '_', '\_'), '%'))
  AND (ba.external_id ILIKE CONCAT('%', replace(replace(:searchTerm1, '%', '\%'), '_', '\_'), '%') OR ba.description ILIKE CONCAT('%', replace(replace(:searchTerm1, '%', '\%'), '_', '\_'), '%') OR ba.properties_yaml ILIKE CONCAT('%', replace(replace(:searchTerm1, '%', '\%'), '_', '\_'), '%'))
ORDER BY ba.external_id
LIMIT :pageSize OFFSET :offset;

As you can see it now only uses filters if the according parameters are set. Otherwise it now generated the lookups for the keywords instead of looping inside of the query.

From what I can see I still have to optimize the index usage on `process_cell`. I am happy to receive any feedback on my question and the result and what next steps would be useful. Also if I missinterpret something, please hit me up.

79633886