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.
>
> 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.

BTW, you might also find inheritance to be of use:
http://www.postgresql.org/docs/8.1/interactive/ddl-inherit.html
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461