Skip site navigation (1) Skip section navigation (2)

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

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 (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-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.


In response to

Responses

pgsql-hackers by date

Next:From: TaralDate: 1998-10-07 21:19:01
Subject: Oops in snprintf.c
Previous:From: Bruce MomjianDate: 1998-10-07 17:56:18
Subject: Re: [HACKERS] RE: [GENERAL] Long update query ? (also Re: [GENERAL] CNF vs. DNF)

pgsql-general by date

Next:From: Bruce MomjianDate: 1998-10-07 21:33:07
Subject: Re: [HACKERS] RE: [GENERAL] Long update query ? (also Re: [GENERAL] CNF vs. DNF)
Previous:From: Fran FabrizioDate: 1998-10-07 20:23:07
Subject: timezone dilemma

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group