0

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?

Community
  • 1
  • 1
Denis Kulagin
  • 8,472
  • 17
  • 60
  • 129
  • 1
    What exactly is your question? Are you looking for the `interesct` operator? I also don't see how a specific index _type_ is relevant for your question. Do you have performance problems with that statement? If yes then please read this: https://wiki.postgresql.org/wiki/Slow_Query_Questions –  Sep 07 '15 at 10:35
  • The double NOT EXISTS() query generates an index scan for the inner query on the junction table (even for this very small rowcount=7) – joop Sep 07 '15 at 10:54
  • @joop I'll give it a try! – Denis Kulagin Sep 07 '15 at 11:01
  • What is the exact problem here? If you want to use postgres you could change the data structure and use arrays to store items. – Jakub Kania Sep 07 '15 at 11:20

1 Answers1

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;
Patrick
  • 29,357
  • 6
  • 62
  • 90
  • That's what I've been looking for! What's about performance? (Haven't found any notes about that on the official page). – Denis Kulagin Sep 07 '15 at 11:13