Here is a simple example of my problem:
CREATE TABLE test1 (id SERIAL, key TEXT UNIQUE, value TEXT); CREATE TABLE test2 (id SERIAL, key TEXT UNIQUE, value TEXT); INSERT INTO test1 (key, value) SELECT i::TEXT, 'ABC' || i::TEXT FROM generate_series(0, 1000000) AS i; INSERT INTO test2 (key, value) SELECT i::TEXT, 'ABC' || (i+1000)::TEXT FROM generate_series(0, 600000) AS i; INSERT INTO test2 (key, value) SELECT i::TEXT, 'ABC' || (i+1000)::TEXT FROM generate_series(1000000, 1200000) AS i; CREATE INDEX test1_key ON test1 (key); CREATE INDEX test1_value ON test1 (value); CREATE INDEX test2_key ON test2 (key); CREATE INDEX test2_value ON test2 (value); VACUUM FULL VERBOSE ANALYZE test1; VACUUM FULL VERBOSE ANALYZE test2;
This is a query that I am currently using, but which takes more than 6 seconds.
EXPLAIN ANALYZE SELECT test1.key AS key1, test1.value AS value1, test2.key AS key2, test2.value AS value2 FROM test1 LEFT OUTER JOIN test2 ON (test1.key = test2.key) WHERE test1.value = 'ABC1234' OR test2.value = 'ABC1234'; key1 | value1 | key2 | value2
In both tables, only very few datasets will meet the requirements, but this does not seem to be the case. Instead, I can use a query like this:
EXPLAIN ANALYZE SELECT coalesce(test1.key, test3.key1) AS key1, coalesce(test1.value, test3.value1) AS value1, coalesce(test2.key, test3.key2) AS key2, coalesce(test2.value, test3.value2) AS value2 FROM (SELECT test1.key AS key1, test1.value AS value1, test2.key AS key2, test2.value AS value2 FROM (SELECT key, value FROM test1 WHERE value = 'ABC1234') AS test1 FULL JOIN (SELECT key, value FROM test2 WHERE value = 'ABC1234') AS test2 ON (test1.key = test2.key)) AS test3 LEFT OUTER JOIN test1 ON (test1.key = test3.key2) LEFT OUTER JOIN test2 ON (test2.key = test3.key1) WHERE test1.key IS NOT NULL; key1 | value1 | key2 | value2
The following query is simpler, but still too slow:
EXPLAIN ANALYZE SELECT test1.key AS key1, test1.value AS value1, test2.key AS key2, test2.value AS value2 FROM test1 LEFT OUTER JOIN test2 ON (test1.key = test2.key) WHERE test1.value = 'ABC1234' OR EXISTS (SELECT 1 FROM test2 t WHERE t.key = test1.key AND t.value = 'ABC1234'); key1 | value1 | key2 | value2 ------+---------+------+--------- 1234 | ABC1234 | 1234 | ABC2234 234 | ABC234 | 234 | ABC1234 (2 rows) QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------- Merge Left Join (cost=0.00..8446826.32 rows=500001 width=32) (actual time=615.706..1651.370 rows=2 loops=1) Merge Cond: (test1.key = test2.key) -> Index Scan using test1_key on test1 (cost=0.00..8398983.25 rows=500001 width=15) (actual time=28.449..734.567 rows=2 loops=1) Filter: ((value = 'ABC1234'::text) OR (alternatives: SubPlan 1 or hashed SubPlan 2)) SubPlan 1 -> Index Scan using test2_key on test2 t (cost=0.00..8.36 rows=1 width=0) (never executed) Index Cond: (key = $0) Filter: (value = 'ABC1234'::text) SubPlan 2 -> Index Scan using test2_value on test2 t (cost=0.00..8.37 rows=1 width=7) (actual time=0.376..0.380 rows=1 loops=1) Index Cond: (value = 'ABC1234'::text) -> Index Scan using test2_key on test2 (cost=0.00..39593.05 rows=800002 width=17) (actual time=0.019..498.456 rows=348894 loops=1) Total runtime: 1651.453 ms (13 rows)
So my question is: is there a simple query that will lead to a similar quick execution plan, similar to the second query, or perhaps an index or some hint to the scheduler.
(I know that for this example it would be wise to have only one table with both values in it, but in reality the tables are more complex and the table layout cannot be easily changed.)
PostgreSQL Version: 9.0.3 shared_buffers = 64MB effective_cache_size = 32MB work_mem = 16MB maintenance_work_mem = 32MB temp_buffers = 8MB wal_buffers= 1MB
EDIT: At the suggestion of Kipotlov, there is a version of UNION here. Why doesn't a regular OR query pick such a good plan?
EXPLAIN ANALYZE SELECT test1.key AS key1, test1.value AS value1, test2.key AS key2, test2.value AS value2 FROM test1 LEFT OUTER JOIN test2 ON (test1.key = test2.key) WHERE test1.value = 'ABC1234' UNION SELECT test1.key AS key1, test1.value AS value1, test2.key AS key2, test2.value AS value2 FROM test1 LEFT OUTER JOIN test2 ON (test1.key = test2.key) WHERE test2.value = 'ABC1234'; key1 | value1 | key2 | value2 ------+---------+------+--------- 1234 | ABC1234 | 1234 | ABC2234 234 | ABC234 | 234 | ABC1234 (2 rows) QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------ Unique (cost=33.64..33.66 rows=2 width=32) (actual time=0.114..0.119 rows=2 loops=1) -> Sort (cost=33.64..33.64 rows=2 width=32) (actual time=0.111..0.113 rows=2 loops=1) Sort Key: public.test1.key, public.test1.value, public.test2.key, public.test2.value Sort Method: quicksort Memory: 17kB -> Append (cost=0.00..33.63 rows=2 width=32) (actual time=0.046..0.097 rows=2 loops=1) -> Nested Loop Left Join (cost=0.00..16.81 rows=1 width=32) (actual time=0.044..0.050 rows=1 loops=1) -> Index Scan using test1_value_key on test1 (cost=0.00..8.44 rows=1 width=15) (actual time=0.023..0.024 rows=1 loops=1) Index Cond: (value = 'ABC1234'::text) -> Index Scan using test2_key on test2 (cost=0.00..8.36 rows=1 width=17) (actual time=0.014..0.016 rows=1 loops=1) Index Cond: (public.test1.key = public.test2.key) -> Nested Loop (cost=0.00..16.80 rows=1 width=32) (actual time=0.036..0.041 rows=1 loops=1) -> Index Scan using test2_value_key on test2 (cost=0.00..8.41 rows=1 width=17) (actual time=0.019..0.020 rows=1 loops=1) Index Cond: (value = 'ABC1234'::text) -> Index Scan using test1_key on test1 (cost=0.00..8.38 rows=1 width=15) (actual time=0.013..0.015 rows=1 loops=1) Index Cond: (public.test1.key = public.test2.key) Total runtime: 0.173 ms (16 rows)