Postgres subquery limits

Question

I have a query which tries to find similar contacts using name, phone and email values.

select *, ranking function(...) as score from
( select * from contacts where 
    'Dave' <% coalesce(contact_name1, '') 
  UNION
  select * from contacts_index where 
    '9782736623' <% coalesce(phone1, '')   
  UNION
  select * from contacts where 
    '[email protected]' <% coalesce(email1, '')  
  UNION 
  select * from contacts where 
    '26 Ashley Gardens' <% coalesce(address1, '')                          
)  AS sub
limit 1000 order by score desc;

I use the limit 1000 to prevent slow queries in the case where a very common name is queried for (and email, phone, address are null). This results in a huge number of records satisfying the where clause, and needing to be ranked.

However, I want to distribute this limit over the 4 subqueries, otherwise all the 1000 limit could be used up by the name subquery, and no records with similar email, phone or address would make it through.

Unfortunately, it seems that limit cannot be used in a subquery. Does anybody know how I can accomplish this

user330315 · Accepted Answer · 2018-02-23 16:49:24Z

3

This is one of the rare case where you actually need to put a sub-query of a union between parentheses:

select *, ranking function(...) as score 
from
( 
  (
    select * 
    from contacts where  'Dave' <% coalesce(contact_name1, '') 
    limit 250
  )
  UNION
  (
    select * 
    from contacts_index 
    where '9782736623' <% coalesce(phone1, '')   
    limit 250
  )
  UNION
  (
    select * 
    from contacts 
    where '[email protected]' <% coalesce(email1, '')  
    limit 250
  )
  UNION 
  (
    select * 
    from contacts 
    where '26 Ashley Gardens' <% coalesce(address1, '')
    limit 250
)  AS sub
order by score desc;

answered Feb 23, 2018 at 16:49

user330315

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

Collectives™ on Stack Overflow

Postgres subquery limits

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related