Searching one term in multiple fields with Postgres

Question

I'm trying to search the same text across to fields in my database for a livesearch box.

SELECT DISTINCT u.id, u.username FROM
users AS u, user_invoice AS ui, user_roles AS ur, roles AS r WHERE
u.id = ur.user_id AND
ur.role_id = r.id AND
r.name = 'teacher' AND
(
    ui.user_id = u.id AND
    CAST(ui.invoice AS TEXT) = 'searchterm'
)

This query searches the invoice table and returns results properly and extremely quickly.

SELECT DISTINCT u.id, u.username FROM
users AS u, user_invoice AS ui, user_roles AS ur, roles AS r WHERE
u.id = ur.user_id AND
ur.role_id = r.id AND
r.name = 'teacher' AND
(u.username like '%searchterm%')

This query searches for a matching username and returns extremely quickly as well.

But when I combine the two like this:

SELECT DISTINCT u.id, u.username FROM
users AS u, user_invoice AS ui, user_roles AS ur, roles AS r WHERE
u.id = ur.user_id AND
ur.role_id = r.id AND
r.name = 'teacher' AND
(
    u.username like '%searchterm%' OR
    (
        ui.user_id = u.id AND
        CAST(ui.invoice AS TEXT) = 'searchterm'
    )
)

It returns the proper results but take almost a minute to do so. What am I doing wrong?

EDIT: EXPLAINs of my queries:

First: http://explain.depesz.com/s/PvS

Second: http://explain.depesz.com/s/D5c

Combined: http://explain.depesz.com/s/Dhf

---

Edited for mistake in copying the cast lines.

Answer 1

Here's how I solve this problem in my main app.

I have a main entity that I want users to be able to search for. Call it customer. This entity has associated detail records in a 1:n contact (for phone, email, etc) table.

I define a view, customer_quicksearch, that calculates a quicksearch key - a text field containing the concatenation of contact records for a customer along with some of the customer fields directly.

I've added triggers to customer and contact customer_summary table. The customer trigger adds a record to customer_summary when a row is inserted into customer and delete the row when the customer record is deleted. They update customer_summary by SELECTing an updated quicksearch key from `customer_quicksearch. I could use a SQL function for this instead of a view, but found the view both more useful and faster. With a view it's quicker to calculate the quicksearch keys for all customers, say, after a bulk insert or update.

CREATE VIEW customer_quicksearch AS
SELECT
        customer.id AS customer_id, array_to_string(ARRAY[
                customer.code,
                customer.name,
                string_agg(array_to_string(ARRAY[
                        contact.email::text,contact.altemail::text, contact.mobile_phone, contact.work_phone, contact.home_phone, contact.fax
                ],'|'),'|')
        ], '|') AS quicksearch_key
FROM customer
LEFT OUTER JOIN contact ON (customer.id = contact.customer_id)
GROUP BY customer.id;

and one of the triggers:

CREATE OR REPLACE FUNCTION customer_summary_update_for_contact() RETURNS trigger AS $$
DECLARE
    _customer_id integer;
BEGIN
    -- When a contact is added/removed/changed we have to regenerate the customer search key
    IF tg_op = 'INSERT' OR tg_op = 'UPDATE' THEN
      _customer_id = NEW.customer_id;
    ELSE
      _customer_id = OLD.customer_id;
    END IF;
    UPDATE customer_summary
    SET quicksearch_key = (SELECT quicksearch_key FROM customer_quicksearch WHERE customer_id = _customer_id)
    WHERE customer_id = _customer_id;
    RETURN NULL;
END;
$$
LANGUAGE 'plpgsql'
SET search_path = 'public';

CREATE TRIGGER customer_summary_update_for_contact_trg AFTER INSERT OR UPDATE OR DELETE ON contact
FOR EACH ROW EXECUTE PROCEDURE customer_summary_update_for_contact();

You also need a trigger on customer to handle insert, update and delete of customer, maintaining the customer_summary record for that customer appropriately.

The customer_summary table contains records that include a quicksearch_key that's a pipe-concatenation of fields, like:

'1800MA|1800 MAKE IT BUILDERS|[email protected]|1234 5678|0499 999 999'
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ ^^^^^^^^^^^^
[from customer record]        [from 1st contact record]             [from another contact record]

This is searched with a simple LIKE pattern. I could add a text_pattern_ops index on it for improved performance if I was doing prefix searches, but since I'm mostly doing searches with no left or right anchor - LIKE '%search%' - there's no benefit.