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