Re: Quesion about querying distributed databases - Mailing list pgsql-general

Adrian Klaver, thank you for the link. I asked the AI to create a query for me using FDW.

This is the sample query:

with filtered_products as (
    select p.product_id
    from products.product p
    where p.title ilike '%search_term%'
), category_filtered as (
    select ic.product_id
    from taxonomy.item_categories ic
    where ic.category_id = any(array['category_id_1', 'category_id_2'])
), attribute_filtered as (
    select ia.product_id
    from attributes.item_attributes ia
    where ia.attribute_id = any(array['attribute_id_1', 'attribute_id_2'])
), final_products as (
    select f.product_id
    from filtered_products f
    join category_filtered c on f.product_id = c.product_id
    join attribute_filtered a on f.product_id = a.product_id
    order by f.product_id -- replace with relevant sorting column
    limit 50 offset 0
)
select p.*
from products.product p
join final_products fp on p.product_id = fp.product_id;

The problem here is that it collects all of the product_id values from the ItemCategories table. Let's say each product is put in one category only. This means that we have 100 thousand records in the ItemCategories table. Thus, to show a list of 20 products on the website, this query first fetches 100 thousand product_id values from the remote server.

That's not scalable. Is there a workaround for this?

Thank you

Saeed