From: | David Hartwig <daveh(at)insightdist(dot)com> |
---|---|
To: | Bruce Momjian <maillist(at)candle(dot)pha(dot)pa(dot)us> |
Cc: | taral(at)mail(dot)utexas(dot)edu, jwieck(at)debis(dot)com, hackers(at)postgreSQL(dot)org |
Subject: | Re: [HACKERS] RE: [GENERAL] Long update query ? (also Re: [GENERAL] CNF vs. DNF) |
Date: | 1998-10-07 20:34:15 |
Message-ID: | 361BD047.855CCF5D@insightdist.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-hackers |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 1998-10-07 21:33:07 | Re: [HACKERS] RE: [GENERAL] Long update query ? (also Re: [GENERAL] CNF vs. DNF) |
Previous Message | Fran Fabrizio | 1998-10-07 20:23:07 | timezone dilemma |
From | Date | Subject | |
---|---|---|---|
Next Message | Taral | 1998-10-07 21:19:01 | Oops in snprintf.c |
Previous Message | Bruce Momjian | 1998-10-07 17:56:18 | Re: [HACKERS] RE: [GENERAL] Long update query ? (also Re: [GENERAL] CNF vs. DNF) |