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

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

From: Bruce Momjian <maillist(at)candle(dot)pha(dot)pa(dot)us>
To: daveh(at)insightdist(dot)com (David Hartwig)
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 21:33:07
Message-ID: 199810072133.RAA09983@candle.pha.pa.us (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-hackers
> 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


In response to

pgsql-hackers by date

Next:From: Brook MilliganDate: 1998-10-07 21:35:45
Subject: extra regression expected file
Previous:From: TaralDate: 1998-10-07 21:19:01
Subject: Oops in snprintf.c

pgsql-general by date

Next:From: Jose' SoaresDate: 1998-10-08 07:46:55
Subject: Re: [GENERAL] Row Nums
Previous:From: David HartwigDate: 1998-10-07 20:34:15
Subject: Re: [HACKERS] RE: [GENERAL] Long update query ? (also Re: [GENERAL] CNF vs. DNF)

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