Re: Feature suggestions (long) - Mailing list pgsql-hackers

On Mon, May 19, 2003 at 10:46:04AM +0200, Zeugswetter Andreas SB SD wrote:
> > Partitions
> > ==========
>
> > Next stage would be teaching the planner. The conditions would be
> > pseudo-constraints on the partitions. Hence if the conditions and the
> > constraints form a non-intersecting set, you can skip that partition
> > altogether.
>
> Make that "normal check constraints", and make the planner consider
> constraints,
> and I think that by itself combined with the current featureset will
> be much more powerful than any of the "partitioning" features out there.
> (This is mainly needed to optimize selects on the big union all view)

Very true. This would give you most of the benefits as well as helping in
other areas. I know there are functions within postgresql which attempt to
determine whether a condition is always true or false. All (ha!) you should
need to do would be to alter the SeqScan and IndexScan nodes to build the
expression:

(check_condition_a) AND (check_condition_b) AND ... AND (query_conditions)

and see if it simplifies to either TRUE or FALSE. Note that a simplification
would be more useful to strip irrelevent clauses from the query_conditions.

> Imho if a dba starts to partition, he usually needs to be more involved
> than the average user, so I think he should be able cope with compexity.
> What imho would help, is a tool that generates a suggested rule set,
> indexes and actions, which the dba can review and apply. I do not think
> new SQL syntax would really help, since that would somehow hide the great
> existing power of the rule system. A tool would teach the dba, and empower
> him to use it.

But the RULE system is not really suited to this. I havn't written it all
out but by my calculations the number of rules required is about (N^2+N)/2
where N is the number of partitions. That's one UPDATE rule for each pair of
tables plus a set of INSERT rules (DELETE requires nothing special). And
each of those rules will be used every single time that table is queried
(both inserts and updates). That's not terribly efficient.

Mind you, maybe there's a better way of doing it. I havn't totally gotten my
head around rules. Maybe it indicates that improvements could be made to the
rule system.

That's why what I'm suggesting pushing it down to a storage management
level, where the difficult issues simply go away.

> And yes, creating several smaller tables and adding the appropriate rules
> usually makes the VLDB life much easier compared to growing single tables
> into the TB range.

Amen.
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> "the West won the world not by the superiority of its ideas or values or
> religion but rather by its superiority in applying organized violence.
> Westerners often forget this fact, non-Westerners never do."
>   - Samuel P. Huntington