Shaun Thomas <sthomas@xxxxxxxxxxxxxxxx> writes: > The first part of the question stands, though... Why isn't the optimizer > substituting these values? a.created_date should be exactly equivalent > to '2012-05-05', but it's clearly not being treated that way. No version of Postgres has ever substituted constants in the way you're imagining, and I wouldn't hold my breath waiting for it to happen. The reason is that "x = constant" only creates a requirement for x to be btree-equal to the constant, and btree equality doesn't guarantee equality for all purposes. In this example we'd have to assume that btree-equality guaranteed identical results from the date + interval addition operator. While that happens to be true for this operator, the planner can't know that. A real-world example of the kind of case I'm worried about is that in IEEE-spec float arithmetic, minus zero and plus zero compare equal --- but there are functions that give different results for the two values. Another is that the char(n) type's equality operator will say that 'foo' and 'foo ' are equal, but those values are definitely distinguishable by some operations, eg length(). There are some cases where the planner can effectively propagate constants, but they rely on transitivity of btree equality operators. For instance if we have x = constant and x = y, with compatible equality operators, we can deduce y = constant. But that doesn't imply that y *is* the constant, just that it's btree-equal to it. There have been some discussions of inventing a stronger notion of equality than btree equality, so that we could know when it's safe to make this type of substitution; but nothing's been done about that. Personally I think it's fairly rare that any real win would come from this type of constant substitution, and so it's very likely that adding it would just create a net drag on performance (because of the added planner cycles spent looking for substitution opportunities, which would happen in every query whether it got any benefit or not). Another point here is that at least for the one side of your BETWEEN operator, b.created_dt >= a.created_dt, we could in fact combine that with a.created_dt = '2012-05-05' to deduce b.created_dt >= '2012-05-05', because we know from the btree opclass for dates that these = and >= operators have compatible semantics. Again though, it seems likely that the cost of looking for such opportunities would outweigh the benefits. In this particular example I don't think it'd do much good --- the reason the planner isn't picking a plan similar to the "fast" one is that it doesn't know that the BETWEEN with variable limits will select only a relatively small part of the table. Providing a constant limit for just one side wouldn't fix that. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
