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(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026