0

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)
5
  • 1
    The description is too vague for me (virtual key, padded number, ...). Can you show the actual SQL? Commented Oct 31 at 11:35
  • Please show the DDL for all relevant tables and your view. Commented Oct 31 at 13:05
  • 1
    I don't understand why you are creating a virtual "key". Why not just pass through the actual product.id column and the optimizer can base itself on that? Commented Oct 31 at 13:05
  • Thanks for your response. I have added some SQL for your reference. The problem is that we cannot just pass the product_id, as we have many distinct usage items for a single product_id, but we are required to reference the usage item by a single ID due to software restriction. That is the problem. Commented Oct 31 at 13:35
  • What does make_id do? Peoplevwill only see your response to their comments if you reply by putting a comment under theirs with @ + their handle! Commented Oct 31 at 14:09

0

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.