Re: What to index to speed up my UNION views?

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(at)svana(dot)org> 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.