I am integrating data display software that expects tables for a dynamic use case, and have opted to produce views for the display software to consume. Essentially, the data display issues queries of the form select * from table where id = X. Crucially, the software always accesses data by where id = X.
Some of the data I'd like to view are queries that appear as children of other entities, but have no physical table backing. For instance, I have tables product, customer, and customer_product_usage, and I'd like to produce a query product_day_usage under product, that shows product accesses per day (no physical table).
The problem is when I'd like to display a single product_day_usage item, the software essentially issues a select * from product_day_usage where id = X. Since product_day_usage has no natural key, I have included a virtual key in the view, taking half of the top UUID bits from the parent product together with a padded number, indicating the row number of the product_day_usage for the particular product.
This works, but since the database has no physical id to search through, it must generate the ids for every single row and scan for any particular product_day_usage item I'm viewing. Ideally, I could teach PostgreSQL to understand (rewrite?) select * from product_day_usage where id = X into
select * from product_day_usage
where id = X
and ...[for product where id starts with first half of X]
Can some such index be made? Any other suggestions are also welcome. (Hopefully without materialising the view)
EDIT:
Some commenters wish to have some code. Essentially, the problem is we have a view like:
create view product_day_usage (id, timestamp, count) as
select
make_id (product_id, row_number () over (order by date_trunc ('day', timestamp)))
, date_trunc ('day', timestamp)
, count (*)
from customer_product_usage
group by product_id, date_trunc ('day', timestamp)
We need to optimise for
select * from product_day_usage where id = X
With the proposed virtual id scheme, the idea is to try to optimise the query into
select
make_id (product_id, row_number () over (order by date_trunc ('day', timestamp))
, date_trunc ('day', timestamp)
, count (*)
from customer_product_usage
group by product_id, date_trunc ('day', timestamp)
having make_id (product_id, row_number () over (order by date_trunc ('day', timestamp)) = X
-- HOW TO TEACH DATABASE ABOUT THIS?
and substring (product_id ::text for 16) = substring (X for 16)
product.idcolumn and the optimizer can base itself on that?make_iddo? Peoplevwill only see your response to their comments if you reply by putting a comment under theirs with@+ their handle!