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?

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: wiki.postgresql.org/wiki/Slow_Query_Questions — user330315
– user330315, Commented Sep 7, 2015 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
– joop, Commented Sep 7, 2015 at 10:54
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
– Jakub Kania, Commented Sep 7, 2015 at 11:20

Patrick · Accepted Answer · 2015-09-07 11:01:30Z

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;

answered Sep 7, 2015 at 11:01

Patrick

33k7 gold badges73 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Denis Kulagin Over a year ago

That's what I've been looking for! What's about performance? (Haven't found any notes about that on the official page).

Collectives™ on Stack Overflow

PostgreSQL: intersect index scans on the same index

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related