On Mon, Mar 27, 2006 at 11:44:00AM +0100, Ashley Moran wrote: > 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. <snip> Just to make sure it's not something obvious, but: > 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; You're using UNION rather than UNION ALL where. There's a big difference and I imagine you actually want the latter. It also makes a big difference in query optimisation. If that doesn't fix it, come back with the EXPLAIN ANALYZE output of your query. Have a nice day, -- Martijn van Oosterhout <kleptog@xxxxxxxxx> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
Attachment:
signature.asc
Description: Digital signature
