Thread: 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
I think you are looking for UNION. 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 ; Artur Rataj wrote: > > 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
Artur, You are right about the missing union. Still, if most tables are alike (at least they have id and string) you should think about inheritance. Create the base table/class with id and string and have all the other tables inherit from it. Them you can do: SELECT id from base_table t1, base_table t2 where t1.string = 'string1' and t2.string='string2' and t1.id=t2.id; JLL Artur Rataj wrote: > > 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
On Mon, 30 Dec 2002, Jean-Luc Lachance wrote: > Still, if most tables are alike (at least they have id and string) you > should think about > inheritance. Create the base table/class with id and string and have all > the other tables inherit from it. > I have tested it -- the indices were used and the queries were fast even when searching the base tables. Best regards, Artur