Boolean product of rows in multiple tables

Hallo, I would like to ask you the following:

There are tables table1 and table2. They both have
identical columns (id, string). If the tables would be joined
into a single table table3, the task would be

select id from table3 t1, table3 t2 where t1.string='string1'
and t2.string='string2' and t1.id=t2.id;

Thus, the task is to find all ids associated with both string1 and string2,
in any of the tables table1 and table2. Because there are very few such
combinations in the discussed case, the query could be fast. However, there
is not table3 but two separate tables table1 and table2. Instead of table3
t1, table3 t2 in the query, all combinations table<n> t1, table<n> t2 could
be used in distinct questions whose results would be joined, but there would
be four such distinct questions in case of the two tables table1 and table2
and still more of them if there were more tables or more strings.

I have tried to use various queries with the `or' operator, but then
postgres used sequential scans and they were very slow.

Is it possible to perform the described task fast in postgres, but without
creating a new table? Perhaps a view could be used, but are indices used
with views?

Best regards,
Artur Rataj

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

On Mon, 30 Dec 2002, Jean-Luc Lachance wrote:

I think you are looking for UNION.

If there would be one component more:

SELECT id from table1 t1, table2 t2 where t1.string = 'string1' and
t2.string='string2' and t1.id=t2.id
UNION
SELECT id from table1 t1, table1 t2 where t1.string = 'string1' and
t2.string='string2' and t1.id=t2.id
UNION
SELECT id from table2 t1, table2 t2 where t1.string = 'string1' and
t2.string='string2' and t1.id=t2.id

UNION
SELECT id from table2 t1, table1 t2 where t1.string = 'string1' and
t2.string='string2' and t1.id=t2.id

;

it would work in the discussed case. The problem is that there can be
several such tables and there can also be several strings, what could give a
relatively large number of such components, and in effect it could be slow.
A table being a sum of the several searched tables could be used with a
single fast `select', but because an arbitrary subset of the available
tables could possibly be searched, a relatively large number of tables being
sums of the tables in the arbitrary subsets would then be needed.

Best regards,
Artur Rataj