Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

create table t as
    select 1 as merchant_id, 8328 as product_id, 'store_a' as store_id, 'new_product' as product_status union all
    select 2 as merchant_id, 9392 as product_id, 'store_b' as store_id, 'new_product' as product_status union all
    select 3 as merchant_id, 3828 as product_id, 'store_b' as store_id, 'old_product' as product_status union all
    select 2 as merchant_id, 8328 as product_id, 'store_a' as store_id, 'new_product' as product_status union all
    select 3 as merchant_id, 9392 as product_id, 'store_b' as store_id, 'new_product' as product_status union all
    select 4 as merchant_id, 3828 as product_id, 'store_a' as store_id, 'old_product' as product_status 
6 rows affected

select t.*,
             (case when min(store_id) over (partition by product_id) = 
                        max(store_id) over (partition by product_id)
                   then min(store_id) over (partition by product_id) || ' only'
                   else 'both'
              end) as product_state
      from t
      where store_id in ('store_a', 'store_b')


merchant_id
product_id
store_id
product_status
product_state


3
3828
store_b
old_product
both

4
3828
store_a
old_product
both

1
8328
store_a
new_product
store_a only

2
8328
store_a
new_product
store_a only

2
9392
store_b
new_product
store_b only

3
9392
store_b
new_product
store_b only


    select merchant_id, product_status, product_state, count(*) as cnt
    from (select t.*,
                 (case when min(store_id) over (partition by product_id) = 
                            max(store_id) over (partition by product_id)
                       then min(store_id) over (partition by product_id) || ' only'
                       else 'both'
                  end) as product_state
          from t
          where store_id in ('store_a', 'store_b')
         ) mp
    group by merchant_id, product_status, product_state;


merchant_id
product_status
product_state
cnt


1
new_product
store_a only
1

2
new_product
store_a only
1

2
new_product
store_b only
1

3
new_product
store_b only
1

3
old_product
both
1

4
old_product
both
1

merchant_id	product_id	store_id	product_status	product_state
3	3828	store_b	old_product	both
4	3828	store_a	old_product	both
1	8328	store_a	new_product	store_a only
2	8328	store_a	new_product	store_a only
2	9392	store_b	new_product	store_b only
3	9392	store_b	new_product	store_b only