sql - 只返回符合所有条件的行

下面是一个粗略的模式：

create table images (
    image_id serial primary key,
    user_id int references users(user_id),
    date_created timestamp with time zone
);

create table images_tags (
    images_tag_id serial primary key,
    image_id int references images(image_id),
    tag_id int references tags(tag_id)
);

输出应该如下所示：

{"images":[
    {"image_id":1, "tag_ids":[1, 2, 3]},
    ....
]}

允许用户根据用户ID、标记和偏移量过滤图像。例如，有人可以说image_id，这将为他们提供来自"user_id":1, "tags":[1, 2], "offset_image_id":5001的所有图像，同时具有标记1和2，以及小于等于500的user_id。
棘手的部分是“同时拥有标签1和2”。返回所有包含1、2或两者的图像更直接（更快）。除了聚合，我看不到其他方法，但它要慢得多。
有人帮我快点吗？
下面是我正在使用的查询，它非常慢：

select * from (
    select i.*,u.handle,array_agg(t.tag_id) as tag_ids, array_agg(tag.name) as tag_names from (
        select i.image_id, i.user_id, i.description, i.url, i.date_created from images i
        where (?=-1 or i.user_id=?)
        and (?=-1 or i.image_id <= ?)
        and exists(
            select 1 from image_tags t
            where t.image_id=i.image_id
            and (?=-1 or user_id=?)
            and (?=-1 or t.tag_id in (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?))
        )
        order by i.image_id desc
    ) i
    left join image_tags t on t.image_id=i.image_id
    left join tag using (tag_id) --not totally necessary
    left join users u on i.user_id=u.user_id --not totally necessary
    group by i.image_id,i.user_id,i.description,i.url,i.date_created,u.handle) sub
where (?=-1 or sub.tag_ids @> ?)
limit 100;

最佳答案

当此语句的执行计划确定后，在准备时，PostgresSQL规划器不知道这些?=-1条件中的哪个是真的。
因此，它必须制定一个计划，可能对特定的user_id进行筛选，或者不进行筛选，也可能对image_id的范围进行筛选，或者不进行筛选，或者对特定的tag_id集合进行筛选，或者不进行筛选。这可能是一个愚蠢的、未优化的计划，无法利用索引。
虽然您当前使用的覆盖所有情况的大型通用查询策略是正确的，但是为了提高性能，您可能需要放弃它，转而使用它，或者在实际填充的参数化条件下生成最小的查询。
在这样一个生成的查询中，?=-1 or ...将消失，只有实际需要的连接才会出现，而可疑的t.tag_id in (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)将消失或减少到严格必要的程度。
如果给定某些参数集时仍然很慢，那么您将有一个更容易优化的起点。
至于问题的要点，测试所有标记的精确匹配，您可能需要在内部子查询中尝试惯用形式：

SELECT image_id FROM image_tags
  WHERE tag_id in (?,?,...)
  GROUP BY image_id HAVING count(*)=?

其中最后一个?是作为参数传递的标记数。
（并作为外部条件完全移除sub.tag_ids @> ?）。

关于sql - 只返回符合所有条件的行，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/23778632/