Squeeze the hell out of the system you have

Re:Squeeze the hell out of the system you have by franckpachot on Saturday August 12, 2023 @02:24AM (#37097560)

With an index on User (createdAt, id) and one on ContactMethod ( primary,ContactMethod,userId), it should be fast (check that the the execution plan starts with User). Except if lot of recent users have no phones, but that will not be better in a single table (except if columnar storage)

[ Parent | on HN ]

Re:Squeeze the hell out of the system you have by aidos on Saturday August 12, 2023 @04:16AM (#37098140)

I imagine so too. You’ll be able to interate over the users in order and hit an instant index on contact method.

    select
      *
    from
      user
    where
      exists (
        select
          true
        from
          contact_method cm
        where
          cm.contact_id = contact.id
          and cm.method = 'phone'
          and cm.primary
      )
    order by
      created_at desc
    limit 10

    —- partial index is even faster
    create index primary_phone on contact_method (contact_id) where method = 'phone' and primary;

[ Parent | on HN ]

Re:Squeeze the hell out of the system you have by yashap on Sunday August 13, 2023 @08:15PM (#37116021)

See my reply here: https://news.ycombinator.com/item?id=37116015

[ Parent | on HN ]