Re: partitioned table query question

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Erik Jones <erik(at)myemma(dot)com>
Cc: Mason Hale <masonhale(at)gmail(dot)com>, pgsql-general(at)postgresql(dot)org
Subject: Re: partitioned table query question
Date: 2007-12-11 04:18:40
Message-ID: 27981.1197346720@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-hackers

Erik Jones <erik(at)myemma(dot)com> writes:
> 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.

I'm not sure how else to explain it: the fact that the WHERE clause
asserts that some operator named "=" will succeed on some_id and 34
is not sufficient grounds to assume that "some_id % 100" and "34 % 100"
will give the same result. Knowing that the "=" operator is a btree
equality operator gives us latitude to make certain conclusions, but
not that one, because there is no way to know whether the semantics
of the particular btree operator class have anything to do with the
behavior of "%".

If you dig in the PG archives you will find some discussions of
inventing a "real equality" flag for operators, which would authorize
the planner to make such deductions for any immutable operator/function.
The idea hasn't gone anywhere, partly because it's not clear that it
would really help in very many common cases. The fact that we could
*not* set the flag on such common cases as float and numeric equality
is a bit discouraging in that connection.

regards, tom lane

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Brett Neumeier 2007-12-11 04:43:11 question about warm standby databases in 8.2.5
Previous Message bookman bookman 2007-12-11 02:29:04 How can I insert NULL into column with the type of timestamp?

Browse pgsql-hackers by date

  From Date Subject
Next Message Trevor Talbot 2007-12-11 04:50:00 Re: partitioned table query question
Previous Message Pavan Deolasee 2007-12-11 04:08:31 Re: Release Note Changes