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.