pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Simple Optimization Problem
3+ messages / 3 participants
[nested] [flat]

* Simple Optimization Problem
@ 1999-03-23 15:32  secret <secret@kearneydev.com>
  0 siblings, 1 reply; 3+ messages in thread

From: secret @ 1999-03-23 15:32 UTC (permalink / raw)
  To: pgsql-sql

    I need to run a report using some parameters that are optional, I'm
doing this by labeling "0" as the optional value.  IE here is a
simplified example:

CREATE TABLE po (po_id int4 PRIMARY KEY, data text);
    (insert a bunch of rows)

ftc=> explain select * from po where po_id=8888;
NOTICE:  QUERY PLAN:

Index Scan using ipo_poid_units on po  (cost=2.05 size=1 width=94)

EXPLAIN

ftc=> explain select * from po where (po_id=8888 or 0=8888);
NOTICE:  QUERY PLAN:

Seq Scan on po  (cost=449.96 size=1751 width=94)

EXPLAIN

    I was hoping PostgreSQL could optimize out the boolean condition
given in the where clause, but it causes it to disregard the index
instead of throwing out 0=8888 in the first stage.

    This is the only way I can think to do this, I have crystal reports
send through the SQL instead of doing the whole thing itself(Which
involves returning all possible rows... Sigh)... This query is taking 20
minutes as a result... Is there any hope in 6.5 of the optimizer
handling this better?

David Secret
MIS Director
Kearney Development Co., Inc.




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: [SQL] Simple Optimization Problem
@ 1999-05-10 04:31  Bruce Momjian <maillist@candle.pha.pa.us>
  parent: secret <secret@kearneydev.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Bruce Momjian @ 1999-05-10 04:31 UTC (permalink / raw)
  To: secret <secret@kearneydev.com>; +Cc: pgsql-sql


I have added this to our TODO list as:

	process const=const parts of OR clause first 


>     I need to run a report using some parameters that are optional, I'm
> doing this by labeling "0" as the optional value.  IE here is a
> simplified example:
> 
> CREATE TABLE po (po_id int4 PRIMARY KEY, data text);
>     (insert a bunch of rows)
> 
> ftc=> explain select * from po where po_id=8888;
> NOTICE:  QUERY PLAN:
> 
> Index Scan using ipo_poid_units on po  (cost=2.05 size=1 width=94)
> 
> EXPLAIN
> 
> ftc=> explain select * from po where (po_id=8888 or 0=8888);
> NOTICE:  QUERY PLAN:
> 
> Seq Scan on po  (cost=449.96 size=1751 width=94)
> 
> EXPLAIN
> 
>     I was hoping PostgreSQL could optimize out the boolean condition
> given in the where clause, but it causes it to disregard the index
> instead of throwing out 0=8888 in the first stage.
> 
>     This is the only way I can think to do this, I have crystal reports
> send through the SQL instead of doing the whole thing itself(Which
> involves returning all possible rows... Sigh)... This query is taking 20
> minutes as a result... Is there any hope in 6.5 of the optimizer
> handling this better?
> 
> David Secret
> MIS Director
> Kearney Development Co., Inc.
> 
> 
> 


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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: [SQL] Simple Optimization Problem
@ 1999-05-10 16:29  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Bruce Momjian <maillist@candle.pha.pa.us>
  0 siblings, 0 replies; 3+ messages in thread

From: Tom Lane @ 1999-05-10 16:29 UTC (permalink / raw)
  To: Bruce Momjian <maillist@candle.pha.pa.us>; +Cc: secret <secret@kearneydev.com>; pgsql-sql

Bruce Momjian <maillist@candle.pha.pa.us> writes:
> I have added this to our TODO list as:
> 	process const=const parts of OR clause first 

I think that would be quite the wrong way to approach it.  What we
need is a general-purpose rewriter phase that tries to evaluate
constant subexpressions, and is applied after the rest of rewrite
but before planner/optimizer.  In this case

	select * from po where (po_id=8888 or 0=8888);

would be reduced to

	select * from po where (po_id=8888 or FALSE);

and then

	select * from po where (po_id=8888);

which the optimizer knows what to do with.  (cnfify() does some of this
but not enough.)

>> Is there any hope in 6.5 of the optimizer
>> handling this better?

Not for 6.5.  6.6 or 6.7 maybe...

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~1999-05-10 16:29 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-03-23 15:32 Simple Optimization Problem secret <secret@kearneydev.com>
1999-05-10 04:31 ` Bruce Momjian <maillist@candle.pha.pa.us>
1999-05-10 16:29   ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox