SELECT * FROM pkey WHERE kemail ~ 'greg@turnstep.com'; SELECT * FROM pkey WHERE kname ~ 'Mullane'; SELECT * FROM pkey WHERE kemail ~ 'greg@turnstep.com' UNION SELECT * FROM pkey WHERE kname ~ 'Mullane';
Sometimes UNION will not work: (ctid)
ERROR: Unable to identify an ordering operator '<' for type 'txtidx' Use an explicit ordering operator or modify the query
UNION alone does not make sense, as the two will never be equal! (hard-coded a constant)
SELECT fingerprint,kemail,kname,'email' AS "matched on" FROM pkey WHERE kemail ~ 'greg@turnstep.com' UNION ALL SELECT fingerprint,kemail,kname,'name' AS "matched on" FROM pkey WHERE kname ~ 'Mullane';
QUERY PLAN
------------------------------------------------------------
Append (C=0.00..9503.25 R=2 width=240) (AT=11453.20..22586.40 R=2 L=1)
-> Subquery Scan "*SELECT* 1" (C=0.00..4751.62 R=1 width=240) (AT=11453.19..11453.36 R=1 L=1)
-> Seq Scan on pkey (C=0.00..4751.62 R=1 width=240) (AT=11453.15..11453.31 R=1 L=1)
Filter: (kemail ~ 'greg@turnstep.com'::text)
-> Subquery Scan "*SELECT* 2" (C=0.00..4751.62 R=1 width=240) (AT=11132.85..11133.01 R=1 L=1)
-> Seq Scan on pkey (C=0.00..4751.62 R=1 width=240) (AT=11132.80..11132.96 R=1 L=1)
Filter: (kname ~ 'Greg Sabino Mullane'::text)
Total runtime: 22586.89 msec
Return just the bare minimum from the subselects:
SELECT * FROM pkey p1, (SELECT fingerprint,'Email' AS "matched on" FROM pkey WHERE kemail ~ 'greg@turnstep.com' UNION ALL SELECT fingerprint,'Name' AS "matched on" FROM pkey WHERE kname ~ 'Greg Sabino Mullane' ) AS p2 WHERE p1.fingerprint = p2.fingerprint; -- Prettier?: SELECT * FROM pkey p1 JOIN (SELECT fingerprint,'Email' AS "matched on" FROM pkey WHERE kemail ~ 'greg@turnstep.com' UNION ALL SELECT fingerprint,'Name' AS "matched on" FROM pkey WHERE kname ~ 'Greg Sabino Mullane' ) AS p2 USING (fingerprint);
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (C=0.00..9513.30 R=2 width=304) (AT=11599.58..22779.91 R=2 L=1)
-> Subquery Scan p2 (C=0.00..9503.25 R=2 width=44) (AT=11473.02..22636.16 R=2 L=1)
-> Append (C=0.00..9503.25 R=2 width=44) (AT=11473.01..22636.12 R=2 L=1)
-> Subquery Scan "*SELECT* 1" (C=0.00..4751.62 R=1 width=44) (AT=11473.00..11473.16 R=1 L=1)
-> Seq Scan on pkey (C=0.00..4751.62 R=1 width=44) (AT=11472.98..11473.14 R=1 L=1)
Filter: (kemail ~ 'greg@turnstep.com'::text)
-> Subquery Scan "*SELECT* 2" (C=0.00..4751.62 R=1 width=44) (AT=11162.77..11162.93 R=1 L=1)
-> Seq Scan on pkey (C=0.00..4751.62 R=1 width=44) (AT=11162.75..11162.91 R=1 L=1)
Filter: (kname ~ 'Greg Sabino Mullane'::text)
-> Index Scan using pkey_pkey on pkey p1 (C=0.00..5.01 R=1 width=240) (AT=71.72..71.79 R=1 L=2)
Index Cond: (p1.fingerprint = "outer".fingerprint)
Total runtime: 22780.57 msec
Revert: if we do not care about how we got there..
SELECT * FROM pkey WHERE kemail ~ 'greg@turnstep.com' OR kname ~ 'Greg Sabino Mullane'
QUERY PLAN
-------------------------------------------------------------------------------------------
Seq Scan on pkey (C=0.00..5029.15 R=1 width=240) (AT=11517.36..11517.52 R=1 L=1)
Filter: ((kemail ~ 'greg@turnstep.com'::text) OR (kname ~ 'Greg Sabino Mullane'::text))
Total runtime: 11517.70 msec
The challenge: how do to both things at once? Side-effect functions
CREATE TABLE match ( what VARCHAR, fingerprint VARCHAR, ctime TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP ); CREATE OR REPLACE FUNCTION match(VARCHAR,VARCHAR) RETURNS BOOL AS ' INSERT INTO match VALUES($1,$2); SELECT \'true\'::boolean; ' LANGUAGE SQL immutable;
SELECT * FROM pkey WHERE
(kemail ~ 'greg@turnstep.com' AND match('Email', fingerprint))
OR
(kname ~ 'Greg Sabino Mullane' AND match('Name', fingerprint))
QUERY PLAN
-----------------------------------------------------------------------
Seq Scan on pkey (C=0.00..5584.20 R=1 width=240) (AT=11621.06..11621.23 R=1 L=1)
Filter: (((kemail ~ 'greg@turnstep.com'::text) AND "match"('Email'::character varying, fingerprint))
OR ((kname ~ 'Greg Sabino Mullane'::text) AND "match"('Name'::character varying, fingerprint)))
Total runtime: 11621.39 msec
Getting the information back out to the application
BEGIN; SELECT 1 FROM pkey p WHERE (kemail ~ 'greg@turnstep.com' AND match('Email',fingerprint)) OR (kname ~ 'Greg Sabino Mullane' AND match('Name',p.fingerprint)); SELECT p.* FROM pkey p, match m WHERE m.ctime = CURRENT_TIMESTAMP AND m.fingerprint = p.fingerprint; COMMIT;
Same time as before, but with a 12.01 msec fetch from the "match" table
Starting to resemble a function...
CREATE OR REPLACE FUNCTION searchme(VARCHAR) RETURNS SETOF VARCHAR AS ' DECLARE finger RECORD; BEGIN PERFORM 1 FROM pkey p WHERE (kemail ~ $1 AND match(\'Email\',fingerprint)) OR (kname ~ $1 AND match(\'Name\',p.fingerprint)); FOR finger IN SELECT p.fingerprint || m.what AS f FROM pkey p, match m WHERE m.ctime = CURRENT_TIMESTAMP AND m.fingerprint = p.fingerprint LOOP RETURN NEXT finger.f; END LOOP; RETURN finger; END; ' LANGUAGE PLPGSQL;
One more way - use a very temporary table
BEGIN;
DELETE FROM match;
SELECT 1 FROM pkey p WHERE
(kemail ~ 'greg@turnstep.com' AND match('Email',fingerprint))
OR
(kname ~ 'Greg Sabino Mullane' AND match('Name',fingerprint));
SELECT p.* FROM pkey p, match m
WHERE m.fingerprint = p.fingerprint;
COMMIT;