Re: [HACKERS] RE: [GENERAL] Long update query ? (also Re: [GENERAL] CNF vs. DNF)

Bruce Momjian wrote:

> > The problem is that you cannot depend upon factoring to reduce these complex
> > statements. We need to retain a place holder (pointer) for each passed
> > parameter. Otherwise we need to re-(parse and plan) the statement before each
> > execution; thus, loosing one of the major benefits of PREPARE.
>
> I think we already have such a problem. When using optimization
> statistics, the optimizer checks the value of the constant to determine
> how many rows will be returned by a "x > 10" by looking at the min/max
> values for the column. Prepared queries where this value will change
> would make that a problem.

Gad Zooks. The future is here. I wonder how Vadim's SPI_Prepare() will respond
to this. I have not used it much, but I believe it accepts parameters.

For that matter, I seem to recall some kind of reduction going on in the query
plan. In 6.3.2 something like:

-- with an index on bar
EXPLAIN SELECT stuff FROM foo WHERE bar = 1 OR bar = 2;
-- does not use index; this is expected in 6.3.2

EXPLAIN SELECT stuff FROM foo WHERE bar = 1 OR bar = 1;
-- uses index; I speculated on some reduction going on here.

...

I just tried it with on out with our corp (6.3.2) database. _day is an indexed field
on dates.

corp=> explain select * from dates where _day = '1/1/99';
NOTICE: QUERY PLAN:

Index Scan on dates (cost=2.05 size=1 width=24)

EXPLAIN
corp=> explain select * from dates where _day = '1/1/99' or _day = '1/1/99';
NOTICE: QUERY PLAN:

Index Scan on dates (cost=2.05 size=1 width=24)

EXPLAIN
corp=> explain select * from dates where _day = '1/1/99' or _day = '1/2/99';
NOTICE: QUERY PLAN:

Seq Scan on dates (cost=91.27 size=219 width=24)

SPI_prepare may need to be tested, along with your example, to see how it responds.