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

> 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)

I believe this reduction is done by cnfify when it removes duplicates as
its last step.


>
> 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)
>

Yes, sure looks like that is what is happening.
>
> SPI_prepare may need to be tested, along with your example, to see how it responds.

I don't think it has actual values in the prepare, but just
place-holders, so it doesn't do the reduction, and my code wouldn't do
it either.

It is only when they use constants, and want to re-run the query with
new constants that could cause a problem.

--
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026