yEL5j94T

1-- we swap this index instead pf adding new index and these will function the same for equality queries
2drop index email_addresses_organization_id_value_index
3create index email_addresses_organization_id_value_index on email_addresses(organization_id, value text_pattern_ops)
4
5-- the proposal in the PR I submitted would generate this query
6-- with a like added this like will use the text_pattern_ops and essentially do a starts_with? check
7explain analyze select ea.student_id,
8ea.organization_id,
9s.duplicate_student_flag_id
10from email_addresses ea
11join students s on s.id = ea.student_id
12where ea.organization_id = 126
13and value like 'aedodge27%'
14and split_part(btrim(value), '@', 2) = 'gmail.com'
15and split_part(split_part(btrim(value), '@', 1), '+', 1) = 'aedodge27'
16
17-- the query in Jared's PR.  When matched this will need to do additional queries so we are giving this an advantage
18explain analyze select * from email_addresses
19where email_addresses.organization_id = 126 and prefix = 'aedodge27' and  suffix = 'gmail.com'
20
21-- this is a more traditional way of doing what Jared changes do
22-- without adding actual data fields
23-- requires an additional query on match as well
24create index traditional_idx on email_addresses(split_part(split_part(btrim(value), '@', 1), '+', 1), split_part(btrim(value), '@', 2), organization_id) 
25explain analyze select * from email_addresses
26where email_addresses.organization_id = 126 and split_part(btrim(value), '@', 2) = 'gmail.com'
27and split_part(split_part(btrim(value), '@', 1), '+', 1) = 'aedodge27'
28
29-- the mean on server time after 10 runs each is 0.027, 0.032, 0.059 respectively; Such small differences cannot be measured from the client.