Post AQ8vEyv37RSGI94dLU by rvr@fosstodon.org
 (DIR) More posts by rvr@fosstodon.org
 (DIR) Post #AQ8vEyTkkvcWvUIpnc by hughsie@mastodon.social
       2022-11-30T18:53:10Z
       
       0 likes, 0 repeats
       
       Hey SQL people; the LVFS does this a few times a second on a big dataset: "SELECT * FROM table WHERE start < 123 AND end > 123;" -- is there a smart way to handle this? Indexes won't work. SQLAlchemy on PostgreSQL if that matters. I suspect this is what IntRangeType is designed for.
       
 (DIR) Post #AQ8vEyv37RSGI94dLU by rvr@fosstodon.org
       2022-11-30T19:39:10Z
       
       0 likes, 0 repeats
       
       @hughsie Why do you say that indexes won't work? I guess that an index (id, start, end) would speed up that query.
       
 (DIR) Post #AQ8vEzHNmPJrPPWT9k by hughsie@mastodon.social
       2022-11-30T22:02:32Z
       
       0 likes, 0 repeats
       
       @rvr hmm, I tried a multicolumn index of "start, end" but that didn't speed things up; EXPLAIN said it was using one index then doing a sequential scan iirc. I saw you put 'id' in your index example; why?
       
 (DIR) Post #AQ8vEzgCI9AWeN8Hpo by baloo@sfba.social
       2022-11-30T22:12:30Z
       
       0 likes, 0 repeats
       
       @hughsie @rvr Because you first lookup the matching row via the index, but you then need to fetch the whole row because you need the extra content (select *) not in the index itself. To do that you need the primary key to be in the index? (this is a guess)
       
 (DIR) Post #AQ8vF0172NtnhEuzR2 by mirek@rodina-sucha.cz
       2022-11-30T22:16:24Z
       
       0 likes, 0 repeats
       
       @baloo @hughsie @rvr but for that you would need (start, end, id) index. BTW I would try if WHERE start < 123 AND NOT (end < 123) makes some difference. The different ordering can make a difference in picking up the index.