Re: Views with unions

Dnia nie 16. lutego 2003 19:51, Tom Lane napisał:
> Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> writes:
> > Yeah, but I think what he's hoping is that it'll notice that
> > "key=1 and key=3" would be noticed as a false condition so that it
> > doesn't scan those tables since a row presumably can't satisify both. The

Yes, that is what I expected.

>
> Yes, this is the key point: we won't put in an optimization that wins on
> a small class of queries unless there is no material cost added for
> planning cases where it doesn't apply.
>
> > In addition, you'd have to be careful to make it work correctly with
> > operator overloading, since someone could make operators whose
> > semantics in cross-datatype comparisons are wierd.
>
> It'd be a pretty considerable amount of work to optimize a plan tree
> fully for this sort of thing (eg, suppressing unnecessary joins), and
> I doubt it's worth the trouble.

Ok, perhaps I should give some explaination about my case.

We are gathering lots of log data in a few tables. Each table grows by some
300.000...500.000 rows a day. With average row size of 100 bytes we get up to
50MB of data per day. Keeping data for 1 year only gives us some 18GB per
table. Also, in each table there is a field with very low cardinality (5..20
unique values). This field appears in most of our queries to the table, in
'where' clause (mostly key_field = 5, some times key_field in (1,2,3)).

What I was thinking of is to implement some kind of horizontal table
partitioning. I wanted to split physical storage of data to few smaller
tables. In my case it could be come 12 subtables, 1..2 GB each. Now, with
'union-all' view (and lots of rules, of course) I could simultate partitioned
table as Oracle implements it. IMHO while querying this view (supertable) for
one or few 'key_field' values it should be much faster for scan 5 GB of 3
partitions (subtables) than 18GB for one big table.

I realize it is not the only solution. Perhaps it could be implemented by a
function taking key_filed value and returning all rows from proper table
(p[lus functions for insert/update/delete). Perhaps application code (log
feeder and report module) could be recoded to know about splitted tables.
Still I think it is 'elegant' and clear.

I wait for your comments,

Mariusz Czulada