PostgreSQL: intersect index scans on the same index

Question

Trying to solve this problem: Sql: choose all baskets containing a set of particular items

In other words there is a table:

tbl_basket_item
--    
basketId itemId

1 2
1 3
1 4
2 3
2 4
3 2
3 4

itemId is indexed.

If I perform a scan on itemId=2, I'll get:

SELECT basketId FROM tbl_basket_item WHERE itemId = 2

1
3

If I perform a scan on itemId=4, I'll get:

SELECT basketId FROM tbl_basket_item WHERE itemId = 4

1
2
3

Can I now intersect those two scans to get:

SELECT basketId FROM tbl_basket WHERE
  basketId IN (SELECT basketId FROM tbl_basket_item WHERE itemId = 2) AND
  basketId IN (SELECT basketId FROM tbl_basket_item WHERE itemId = 4)

1
3

using some PostgeSQL advanced indexing techs, like bitmap indices?

Answer 1

You effectively get an "intersect index scan" (whatever that effectively is) by doing a standard INTERSECT on your SELECT:

SELECT basketId FROM tbl_basket_item WHERE itemId = 2
INTERSECT
SELECT basketId FROM tbl_basket_item WHERE itemId = 4;