Re: partitioned table query question

On Dec 10, 2007, at 5:50 PM, Tom Lane wrote:

> Erik Jones <erik(at)myemma(dot)com> writes:
>> Forgive me if I'm nagging on this, I just want to understand this
>> better. Why does evaluating a CHECK constraint like 'CHECK some_id %
>> 100 = 32' against WHERE clause like 'WHERE some_id=1132' need to know
>> anything about equality properites of %? Or, rather, why does it
>> stop there? Can't it just substitute the given value for some_id in
>> to the check expression, execute it and check the result value for
>> TRUE/FALSE?
>
> What "given value"?

The where clause in the select query.
>
> What you're missing is that the condition "a = b" does not mean that
> "f(a) = f(b)" for every function f. It is possible to define
> constraints on equality that would make that true, but such
> constraints
> would be far stronger than what is required to make btree (or even
> hash)
> indexes work.

In the table constraint CHECK (some_id % 100 = 32), isn't that just f
(a) = 32?

> In the example I gave, we are able to conclude that 3 is unequal to 9
> not because of any a-priori knowledge, but because we apply the
> specific
> operator to the specific constants and find out that it yields false.
> Our knowledge of the consistency requirements that are imposed on
> btree
> equality operators then allows us to determine that the two original
> conditions can't be true at the same time.
>
> This does *not* imply assuming that the two constants are really "the
> same" in the sense that no other operator in the system could tell
> them
> apart. This isn't mere academic hairsplitting: there actually are
> standard equality operators in the system for which such a conclusion
> would fail. I already mentioned float comparison, and numeric
> comparison has similar behaviors --- for instance,
>
> regression=# select '0.00'::numeric = '0.0'::numeric;
> ?column?
> ----------
> t
> (1 row)
>
> regression=# select text('0.00'::numeric) = text('0.0'::numeric);
> ?column?
> ----------
> f
> (1 row)

I get your arguments wrt the known (to the planner) semantics of the
equality operator. I guess what I don't understand is that given the
query

SELECT COUNT(*)
FROM table
WHERE some_id=34;

on a table with the much discussed constraint (34 % 100) = 32 isn't
simply evaluated as a one-time filter whenever whatever constraint
exclusion code examines child partition tables' constraints.

Again, though, is there some better way to go about implementing some
kind of hash based partitioning in postgres besides this that would
be more natural wrt queries?

Erik Jones

Software Developer | Emma®
erik(at)myemma(dot)com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com