Query across schemas in PostgreSQL In PostgreSQL, I have a table which, due to sharding, exists across many schemas.  In sub-optimal cases, I need to execute a query on all of these schemas (due to an exception which violates the sharding interface.. but that's another story).  What is an efficient way to do this?  Assuming the tables have an index across the data that is interesting to this particular query (that is going across all schemas.)
A construct like the following should work for you:

select *
from (
  select *
  from schemaA.tableA
  union all
  select *
  from schemaB.tableB
  union all
  ...
)
where columnA = "value";


You'll have to check the execution plans to see if the optimizer is smart enough to push the where clause into the subquery for each table.



The other thing to remember is that you can generate your queries by querying the metadata for a list of schema and table names.