On Thu, 7 Sep 2000, Oliver Smith wrote:
> Each stone gives a specific set of attributes, but combining it with
> silver & plat gives one type of jewellery, while gold & electrum
> produce a different type.
>
> The reason for including this is so that a chart can be produced
> which shows what attributes each stone modifies, how much those
> attributes are modified for each stone+metal combo, and what type
> of jewellery is produced in the combo.
Ah, I see.
> As my own side note, on Postgres 7.0.2, I at one point tried creating
> a view which said
>
> CREATE VIEW silver_view AS
> SELECT * from jcombo_query WHERE metal_uid = 1 ;
>
> and so on for elec, gold, plat. And then used these to simplify the
> main query. This caused Postgres to go away permanently, and I had
> to manually delete the database.
I'll try that when I get home, I'd like to see what explain says in
this case (if it even runs). My guess is that it would only make
postgres' job more difficult since views are implemented as rewrite rules,
this would just add another layer of rewrites that it would have work
through.
> And the original query was so slow, that I decided to, for the time
> being, do a
>
> SELECT * INTO jewellery FROM metals_query ORDER BY stone_uid ;
Yeah, tables for temp storage help, esp if you've got data that
you don't need to interpret multiple times (like in the original
one, the jcombo_query view getting joined with itself 4 times
ends up being 12 joins - rather larger set of joins to try to
work with).
On my machine, just putting the jcombo_query data in a temporary
table and using that rather than the view cut the time down to nearly
nothing. In the original form, even just doing explain took like
half a minute or something of that sort.