I had a bright idea that has not worked quite as well as I thought. We have a web sites for selling cars that we are trying to expand to vans, bikes etc. We get a datafeed containing prices and technical data updated nightly (for cars it's about 2.3GB, others are smaller). This comes into SQL Server as one database per vehicle type, and we export it as CSV data, which is COPY'd into Postgres. The current version of the application uses Hibernate. Now, as the database for each vehicle type has an idential schema I thought it would be possible to use views to access the underlying data so we don't have to duplicate the mappings for each data type. So for example, there is a table "capmod" which stores vehicle models. Unfortunately, the primary key column for the table is not unique across all dataset databases, so a model id used to identify a car model in the car database may also identiffy a van model in the van database. So, I created a view like this: CREATE OR REPLACE VIEW capmod AS SELECT 'cap_car'::character varying::character varying(10) AS "vehicle_type", car_capmod.cmod_code, ... FROM cap_car.car_capmod UNION SELECT 'cap_lcv'::character varying::character varying(10) AS "vehicle_type", lcv_capmod.cmod_code, ... FROM cap_lcv.lcv_capmod; I've removed all the actual data columns. Effectively this makes the primary key for the view composite based on vehicle_type and cmod_code. The problem is that performance has taken a massive hit. Maybe the answer to this is simple - I just need to make sure that the index on the underlying car_capmod, lcv_capmod, XXX_capmod tables is hit in a query such as "SELECT * FROM capmod where vehicle_type = 'cap_car' and cmod_code=1234". Failing that I will have to include a vehicle_type column in each of the underlying tables, but I want to avoid anything that complicates the import procedure (which is already very slow). I'd be very grateful for any advice Cheers Ashley Moran
