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

* Good Optimization
@ 1999-07-07 14:56  secret <secret@kearneydev.com>
  0 siblings, 3 replies; 27+ messages in thread

From: secret @ 1999-07-07 14:56 UTC (permalink / raw)
  To: pgsql-sql; John Ridout <johnridout@ctasystems.co.uk>


    There is a simple way to optimize SQL queries involving joins to
PostgreSQL that I think should be handled by Postgre?  If one is joining
a tables a,b on attribute "x" and if one has something like x=3 then it
helps A LOT to say: a.x=3 and b.x=3 in addition to saying a.x=b.x ...
The example below shoulds the radical speed gain of doing this, and I
think it isn't something real obvious to most people...

    Of course it could just be a common thing to do in SQL, anyway, just
thought I'd let you all know what I discovered.



Here is an example:

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

Nested Loop  (cost=401.34 size=6146 width=158)
  ->  Index Scan using ipo_po_id_units on po  (cost=2.05 size=2
width=94)
  ->  Index Scan using itickets_mpou on tickets  (cost=199.64 size=70650
width=6
4)

EXPLAIN
ftc=>

ftc=> explain select * from po,tickets where po_id=material_po and
po_id=8888 an
d material_po=8888;
NOTICE:  QUERY PLAN:

Nested Loop  (cost=21.42 size=268 width=158)
  ->  Index Scan using ipo_po_id_units on po  (cost=2.05 size=2
width=94)
  ->  Index Scan using itickets_material_po on tickets  (cost=9.68
size=3073 wid
th=64)

EXPLAIN
ftc=>




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

* Re: [SQL] Good Optimization
@ 1999-07-07 15:22  Roderick A. Anderson <raanders@altoplanos.net>
  parent: secret <secret@kearneydev.com>
  2 siblings, 2 replies; 27+ messages in thread

From: Roderick A. Anderson @ 1999-07-07 15:22 UTC (permalink / raw)
  To: pgsql-sql

On Wed, 7 Jul 1999, secret wrote:

> 
>     There is a simple way to optimize SQL queries involving joins to
> PostgreSQL that I think should be handled by Postgre?  If one is joining
> a tables a,b on attribute "x" and if one has something like x=3 then it
> helps A LOT to say: a.x=3 and b.x=3 in addition to saying a.x=b.x ...
> The example below shoulds the radical speed gain of doing this, and I
> think it isn't something real obvious to most people...
> 

Also if the optimizer works similar to Oracle then the order of
where statements would also help in actual performance.  I'm mostly a
lurker on this list but have never seen anything about this issue.
   When using Oracle, and won't it be nice when people mention PostgreSQL
functionality when comparing other databases, the statements at the
bottom should be the most restrictive and joins should be near the top.

	select * 
	  from po,tickets
	 where po_id=material_po
	   and po_id=8888 ;

Would be the best.  Whereas

	select *
	  from po,tickets
	 where po_id=8888
	   and po_id=material_po;

would do the join then select those tuples that have a po_id of 8888.

Of course this is probably what PostgreSQL is doing already without a
requirement for the positioning.

Rod
--
Roderick A. Anderson
raanders@altoplanos.net               Altoplanos Information Systems, Inc.
Voice: 208.765.6149                            212 S. 11th Street, Suite 5
FAX: 208.664.5299                                  Coeur d'Alene, ID 83814




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

* Re: [SQL] Good Optimization
@ 1999-07-07 15:42  Tom Lane <tgl@sss.pgh.pa.us>
  parent: secret <secret@kearneydev.com>
  2 siblings, 1 reply; 27+ messages in thread

From: Tom Lane @ 1999-07-07 15:42 UTC (permalink / raw)
  To: secret <secret@kearneydev.com>; +Cc: pgsql-sql; John Ridout <johnridout@ctasystems.co.uk>

secret <secret@kearneydev.com> writes:
>     There is a simple way to optimize SQL queries involving joins to
> PostgreSQL that I think should be handled by Postgre?  If one is joining
> a tables a,b on attribute "x" and if one has something like x=3 then it
> helps A LOT to say: a.x=3 and b.x=3 in addition to saying a.x=b.x ...
> The example below shoulds the radical speed gain of doing this, and I
> think it isn't something real obvious to most people...

How much *actual* speedup is there?  I don't trust the optimizer's
numbers as anything more than relative measures ;-)

I'm a bit surprised that you are getting a nested-loop plan and not
a merge or hash join.  With a merge join, at least, there ought not be
a large difference from providing the additional qual clause (I think).
What Postgres version are you using?

			regards, tom lane



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

* Re: [SQL] Good Optimization
@ 1999-07-07 15:44  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Roderick A. Anderson <raanders@altoplanos.net>
  1 sibling, 1 reply; 27+ messages in thread

From: Tom Lane @ 1999-07-07 15:44 UTC (permalink / raw)
  To: Roderick A. Anderson <raanders@altoplanos.net>; +Cc: pgsql-sql

"Roderick A. Anderson" <raanders@altoplanos.net> writes:
> Also if the optimizer works similar to Oracle then the order of
> where statements would also help in actual performance.  I'm mostly a
> lurker on this list but have never seen anything about this issue.
>    When using Oracle, and won't it be nice when people mention PostgreSQL
> functionality when comparing other databases, the statements at the
> bottom should be the most restrictive and joins should be near the top.

The Postgres optimizer doesn't care about the order of where clauses;
it'll pick out the clauses it thinks are most effective regardless
of the order you write them in.

			regards, tom lane



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

* Re: [SQL] Good Optimization
@ 1999-07-07 16:10  Roderick A. Anderson <raanders@altoplanos.net>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 27+ messages in thread

From: Roderick A. Anderson @ 1999-07-07 16:10 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql

On Wed, 7 Jul 1999, Tom Lane wrote:

> The Postgres optimizer doesn't care about the order of where clauses;
> it'll pick out the clauses it thinks are most effective regardless
> of the order you write them in.
> 
> 			regards, tom lane

As I figured it would be as soon as I opened my big mouth.

Rod
--
Roderick A. Anderson
raanders@altoplanos.net               Altoplanos Information Systems, Inc.
Voice: 208.765.6149                            212 S. 11th Street, Suite 5
FAX: 208.664.5299                                  Coeur d'Alene, ID 83814




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

* Re: [SQL] Good Optimization
@ 1999-07-07 16:45  Bruce Momjian <maillist@candle.pha.pa.us>
  parent: Roderick A. Anderson <raanders@altoplanos.net>
  1 sibling, 0 replies; 27+ messages in thread

From: Bruce Momjian @ 1999-07-07 16:45 UTC (permalink / raw)
  To: Roderick A. Anderson <raanders@altoplanos.net>; +Cc: pgsql-sql

> Also if the optimizer works similar to Oracle then the order of
> where statements would also help in actual performance.  I'm mostly a
> lurker on this list but have never seen anything about this issue.
>    When using Oracle, and won't it be nice when people mention PostgreSQL
> functionality when comparing other databases, the statements at the
> bottom should be the most restrictive and joins should be near the top.
> 
> 	select * 
> 	  from po,tickets
> 	 where po_id=material_po
> 	   and po_id=8888 ;
> 
> Would be the best.  Whereas
> 
> 	select *
> 	  from po,tickets
> 	 where po_id=8888
> 	   and po_id=material_po;
> 
> would do the join then select those tuples that have a po_id of 8888.
> 
> Of course this is probably what PostgreSQL is doing already without a
> requirement for the positioning.

This is not your mother's database.  We don't care about statement
ordering.  :-)


-- 
  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] 27+ messages in thread

* Re: [SQL] Good Optimization
@ 1999-07-07 17:22  Jan Wieck <wieck@debis.com>
  parent: secret <secret@kearneydev.com>
  2 siblings, 2 replies; 27+ messages in thread

From: wieck@debis.com @ 1999-07-07 17:22 UTC (permalink / raw)
  To: secret@kearneydev.com; +Cc: pgsql-sql; johnridout@ctasystems.co.uk

>     There is a simple way to optimize SQL queries involving joins to
> PostgreSQL that I think should be handled by Postgre?  If one is joining
> a tables a,b on attribute "x" and if one has something like x=3 then it
> helps A LOT to say: a.x=3 and b.x=3 in addition to saying a.x=b.x ...
> The example below shoulds the radical speed gain of doing this, and I
> think it isn't something real obvious to most people...
>
>     Of course it could just be a common thing to do in SQL, anyway, just
> thought I'd let you all know what I discovered.
>
>
>
> Here is an example:
> [...]

Hmmmmm,

    wouldn't   it   be  nice  to  do  such  things  automagically
    (rewriter? planner?).

    Even if I don't think that it is that  trivial  as  it  might
    look  like.  The  entire  queries  qualification  is  ONE big
    expression of cascaded  AND  and  OR  nodes.  Deciding  which
    constant  expressions  could  be added again with substituted
    Var nodes because there's  some  "VarX  ==  VarY"  and  don't
    falling into recursion traps isn't easy.

    But  no expression comparing two Var's of different RTE's can
    be put down into an  execution  subtree.  And  that  is  what
    reduces  the  amount  of  data to get joined. I think this is
    worth the efford.

    I think the best place for it will  be  at  the  top  of  the
    optimizer.    It's   more   an  optimization  issue  even  if
    implemented in rewriting technique.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#========================================= wieck@debis.com (Jan Wieck) #





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

* Re: [SQL] Good Optimization
@ 1999-07-07 18:00  Bruce Momjian <maillist@candle.pha.pa.us>
  parent: Jan Wieck <wieck@debis.com>
  1 sibling, 1 reply; 27+ messages in thread

From: Bruce Momjian @ 1999-07-07 18:00 UTC (permalink / raw)
  To: Jan Wieck <jwieck@debis.com>; +Cc: secret <secret@kearneydev.com>; pgsql-sql; johnridout@ctasystems.co.uk

> >     There is a simple way to optimize SQL queries involving joins to
> > PostgreSQL that I think should be handled by Postgre?  If one is joining
> > a tables a,b on attribute "x" and if one has something like x=3 then it
> > helps A LOT to say: a.x=3 and b.x=3 in addition to saying a.x=b.x ...
> > The example below shoulds the radical speed gain of doing this, and I
> > think it isn't something real obvious to most people...
> >
> >     Of course it could just be a common thing to do in SQL, anyway, just
> > thought I'd let you all know what I discovered.
> >
> >
> >
> > Here is an example:
> > [...]
> 
> Hmmmmm,
> 
>     wouldn't   it   be  nice  to  do  such  things  automagically
>     (rewriter? planner?).

Added to TODO:

	* In WHERE x=3 AND x=y, add y=3              


-- 
  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] 27+ messages in thread

* Re: [SQL] Good Optimization
@ 1999-07-07 18:10  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Jan Wieck <wieck@debis.com>
  1 sibling, 1 reply; 27+ messages in thread

From: Tom Lane @ 1999-07-07 18:10 UTC (permalink / raw)
  To: jwieck@debis.com; +Cc: secret@kearneydev.com; pgsql-sql; johnridout@ctasystems.co.uk

wieck@debis.com (Jan Wieck) writes:
>     I think the best place for it will  be  at  the  top  of  the
>     optimizer.    It's   more   an  optimization  issue  even  if
>     implemented in rewriting technique.

I have thought for some time that we need an additional phase in between
the rewriter and the planner/optimizer proper.  This phase would be the
right place to do constant-subexpression folding, cnfify(), and any
other rearrangements of the parse tree that would be likely to help the
planning process.  So far, the only specific example I had in mind was
the index restriction clauses that are added for LIKE and ~ matches with
a constant initial pattern --- those are currently done by gram.y but
I think they need to happen much later.  If this idea of propagating
restriction clauses across a join is really worthwhile, then this new
optimization phase would be the right place for that too.

The reason I'd prefer to keep it separate from the rewriter is that the
rewriter is mostly concerned with functionality (implementing views etc)
whereas this new module would be concerned with optimization.  Obviously
you do not want to start worrying about optimization until you've got
the fully expanded query available.

			regards, tom lane



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

* Re: [SQL] Good Optimization
@ 1999-07-07 19:09  Bruce Momjian <maillist@candle.pha.pa.us>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 27+ messages in thread

From: Bruce Momjian @ 1999-07-07 19:09 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Jan Wieck <jwieck@debis.com>; secret <secret@kearneydev.com>; pgsql-sql; johnridout@ctasystems.co.uk

> The reason I'd prefer to keep it separate from the rewriter is that the
> rewriter is mostly concerned with functionality (implementing views etc)
> whereas this new module would be concerned with optimization.  Obviously
> you do not want to start worrying about optimization until you've got
> the fully expanded query available.

Sounds good.

-- 
  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] 27+ messages in thread

* Re: [SQL] Good Optimization
@ 1999-07-08 08:09  Patrik Kudo <kudo@partitur.se>
  parent: Bruce Momjian <maillist@candle.pha.pa.us>
  0 siblings, 2 replies; 27+ messages in thread

From: Patrik Kudo @ 1999-07-08 08:09 UTC (permalink / raw)
  To: Bruce Momjian <maillist@candle.pha.pa.us>; +Cc: pgsql-sql

Bruce Momjian wrote:
> 
> Added to TODO:
> 
>         * In WHERE x=3 AND x=y, add y=3

I don't know if I'm way off, but wouldn't removing "x=y" improve
performance further?

-- 
ech`echo xiun|tr nu oc|sed 'sx\([sx]\)\([xoi]\)xo un\2\1 is xg'`ol
Känns det oklart? Fråga på!



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

* Re: [SQL] Good Optimization
@ 1999-07-08 09:04  Jan Wieck <wieck@debis.com>
  parent: Patrik Kudo <kudo@partitur.se>
  1 sibling, 3 replies; 27+ messages in thread

From: wieck@debis.com @ 1999-07-08 09:04 UTC (permalink / raw)
  To: kudo@partitur.se; +Cc: maillist@candle.pha.pa.us; pgsql-sql

>
> Bruce Momjian wrote:
> >=20
> > Added to TODO:
> >=20
> >         * In WHERE x=3D3 AND x=3Dy, add y=3D3
>
> I don't know if I'm way off, but wouldn't removing "x=3Dy" improve
> performance further?

    Maybe  in  the simple case above. But it will probably change
    the result set if someone issues

        SELECT a.x, b.y FROM a, b WHERE a.x >= 10
                                    AND a.x <= 19
                                    AND a.x = b.x;

    which should then get rewritten into

        SELECT a.x, b.y FROM a, b WHERE a.x >= 10
                                    AND a.x <= 19
                                    AND a.x = b.x
                                    AND b.x >= 10
                                    AND b.x <= 19;

    This time the "a.x = b.x" is important!

    IMHO we're improving optimization more and more on  the  cost
    of  query  parse/rewrite/optimize/plan time. Thus performance
    of statements that EXECUTE fast slows  down  more  and  more.
    Isn't   it   time   to   think   about  some  (maybe  shared)
    "parsetree->plan" cache that provides ready to use  plans  if
    only Const values have changed?


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#========================================= wieck@debis.com (Jan Wieck) #





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

* Re: [SQL] Good Optimization
@ 1999-07-08 09:23  Patrik Kudo <kudo@partitur.se>
  parent: Jan Wieck <wieck@debis.com>
  2 siblings, 1 reply; 27+ messages in thread

From: Patrik Kudo @ 1999-07-08 09:23 UTC (permalink / raw)
  To: Jan Wieck <jwieck@debis.com>; +Cc: maillist@candle.pha.pa.us; pgsql-sql

Jan Wieck wrote:
> 
> >
> > Bruce Momjian wrote:
> > >=20
> > > Added to TODO:
> > >=20
> > >         * In WHERE x=3D3 AND x=3Dy, add y=3D3
> >
> > I don't know if I'm way off, but wouldn't removing "x=3Dy" improve
> > performance further?
> 
>     Maybe  in  the simple case above. But it will probably change
>     the result set if someone issues
> 
>         SELECT a.x, b.y FROM a, b WHERE a.x >= 10
>                                     AND a.x <= 19
>                                     AND a.x = b.x;
> 
>     which should then get rewritten into
> 
>         SELECT a.x, b.y FROM a, b WHERE a.x >= 10
>                                     AND a.x <= 19
>                                     AND a.x = b.x
>                                     AND b.x >= 10
>                                     AND b.x <= 19;
> 
>     This time the "a.x = b.x" is important!

Ouch... didn't think that far. However, if an cache is implemented
it might be worth the extra overhead to optimize special cases like
the one I commented on. Since most systems use a fairly limited set
of queries (I suppose...) most of the queries will be cached quite
fast.

BTW, I just love the work all of you are doing! Postgres is awsome!

/Kudo



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

* Re: [SQL] Good Optimization
@ 1999-07-08 10:24  Jan Wieck <wieck@debis.com>
  parent: Patrik Kudo <kudo@partitur.se>
  0 siblings, 0 replies; 27+ messages in thread

From: wieck@debis.com @ 1999-07-08 10:24 UTC (permalink / raw)
  To: kudo@partitur.se; +Cc: jwieck@debis.com; maillist@candle.pha.pa.us; pgsql-sql

Patrik Kudo wrote:

> Jan Wieck wrote:
> >
> >     This time the "a.x = b.x" is important!
>
> Ouch... didn't think that far. However, if an cache is implemented
> it might be worth the extra overhead to optimize special cases like
> the one I commented on. Since most systems use a fairly limited set
> of queries (I suppose...) most of the queries will be cached quite
> fast.

    Exactly  the  fact  that  most  system  use  a limited set of
    queries made me think that a shared query->plan  cache,  that
    in  the best of all worlds isn't lost on backend termination,
    is worth the efford. And it would lower the  need  of  client
    side prepared execution plans as discussed sometimes.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#========================================= wieck@debis.com (Jan Wieck) #





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

* Re: [SQL] Good Optimization
@ 1999-07-08 15:01  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Jan Wieck <wieck@debis.com>
  2 siblings, 0 replies; 27+ messages in thread

From: Tom Lane @ 1999-07-08 15:01 UTC (permalink / raw)
  To: jwieck@debis.com; +Cc: kudo@partitur.se; maillist@candle.pha.pa.us; pgsql-sql

wieck@debis.com (Jan Wieck) writes:
>     IMHO we're improving optimization more and more on  the  cost
>     of  query  parse/rewrite/optimize/plan time. Thus performance
>     of statements that EXECUTE fast slows  down  more  and  more.
>     Isn't   it   time   to   think   about  some  (maybe  shared)
>     "parsetree->plan" cache that provides ready to use  plans  if
>     only Const values have changed?

Easier said than done, because the plan chosen by the optimizer may
well depend on the values of the constants.  For example, in

	SELECT * FROM t1, t2 WHERE t1.a = t2.a AND t1.b < 12;

the optimizer will attempt to estimate the fraction of t1's rows that
will pass the restriction "t1.b < 12", and then it will choose the
type of join depending on how many rows it thinks there will be.
If we require plans to be chosen without dependence on the values of
constants, we will have to give up a great deal of optimization.

I do not object to letting the user specifically say PREPARE xyz...
and then using that prepared plan; there are plenty of times when
trading off planning time against getting a narrowly-tailored plan
is a useful thing to do.  But we mustn't pre-empt the user's choice.
Note also that in a PREPARE context, it is known which items are
substitutable parameters and which are plain constants, so some
amount of optimization can still go on.

			regards, tom lane



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

* Re: [SQL] Good Optimization
@ 1999-07-08 15:40  Bruce Momjian <maillist@candle.pha.pa.us>
  parent: Patrik Kudo <kudo@partitur.se>
  1 sibling, 0 replies; 27+ messages in thread

From: Bruce Momjian @ 1999-07-08 15:40 UTC (permalink / raw)
  To: Patrik Kudo <kudo@partitur.se>; +Cc: pgsql-sql

[Charset iso-8859-1 unsupported, filtering to ASCII...]
> Bruce Momjian wrote:
> > 
> > Added to TODO:
> > 
> >         * In WHERE x=3 AND x=y, add y=3
> 
> I don't know if I'm way off, but wouldn't removing "x=y" improve
> performance further?

Let me rewrite it.  TODO list updated:

	* In WHERE tab1.x=3 AND tab1.x=tab2.y, add tab2.y=3

-- 
  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] 27+ messages in thread

* Re: [SQL] Good Optimization
@ 1999-07-08 15:48  Bruce Momjian <maillist@candle.pha.pa.us>
  parent: Jan Wieck <wieck@debis.com>
  2 siblings, 1 reply; 27+ messages in thread

From: Bruce Momjian @ 1999-07-08 15:48 UTC (permalink / raw)
  To: Jan Wieck <jwieck@debis.com>; +Cc: Patrik Kudo <kudo@partitur.se>; pgsql-sql

>     IMHO we're improving optimization more and more on  the  cost
>     of  query  parse/rewrite/optimize/plan time. Thus performance
>     of statements that EXECUTE fast slows  down  more  and  more.
>     Isn't   it   time   to   think   about  some  (maybe  shared)
>     "parsetree->plan" cache that provides ready to use  plans  if
>     only Const values have changed?

TODO list has:

	* Cache most recent query plan(s?)

-- 
  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] 27+ messages in thread

* RE: [SQL] Good Optimization
@ 1999-07-09 09:59  Ansley, Michael <Michael.Ansley@intec.co.za>
  0 siblings, 0 replies; 27+ messages in thread

From: Ansley, Michael @ 1999-07-09 09:59 UTC (permalink / raw)
  To: pgsql-sql

> >
> > Bruce Momjian wrote:
> > >=20
> > > Added to TODO:
> > >=20
> > >         * In WHERE x=3D3 AND x=3Dy, add y=3D3
> >
> > I don't know if I'm way off, but wouldn't removing "x=3Dy" improve
> > performance further?
> 
<snip some example stuff>
> 
>     IMHO we're improving optimization more and more on  the  cost
>     of  query  parse/rewrite/optimize/plan time. Thus performance
>     of statements that EXECUTE fast slows  down  more  and  more.
>     Isn't   it   time   to   think   about  some  (maybe  shared)
>     "parsetree->plan" cache that provides ready to use  plans  if
>     only Const values have changed?

Isn't this what stored procs are supposed to be?  If a stored proc is
defined
with parameters, then the query plan is saved, and the execution of the
statement
is saved the effort of having to recompile the plan.  In fact, I think that
SQL 
Server, and possibly Oracle as well, store the plan for any query sent, and
then 
drop it when the connection is terminated.  Perhaps this is something to
think 
about.

MikeA




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

* Good Optimization
@ 1999-07-09 10:15  Ansley, Michael <Michael.Ansley@intec.co.za>
  0 siblings, 2 replies; 27+ messages in thread

From: Ansley, Michael @ 1999-07-09 10:15 UTC (permalink / raw)
  To: 'maillist@candle.pha.pa.us' <maillist@candle.pha.pa.us>; +Cc: pgsql-sql

Hi, Bruce,

I've only just picked up the thread on optimizations (I get the digest for
the pgsql-sql list).  I really feel that a lot of effort could be saved with
some good benefits if a stored proc mechanism is put into place.  Once that
has been done, it can be used to store temporary plans (procedures) for
ad-hoc queries which are released on the termination of the connection.
However, I think that a lot of users will develop stored procs to replace a
lot of their existing common SQL, in order to (partially) optimize their
systems.  Perhaps in the PREPARE statement we could add a facility to allow
the user to specify the TTL of the cached proc for an ad-hoc query.

When I talk about stored procs, I don't mean functions.  We already have
those.  I mean procedures that are able to return a rowset.  Just to make
sure nobody gets the wrong idea.

And it's another feature on the marketing list.  I know that marketing is
definitely not what drives the developers, but there are some of us who are
really pushing for PostgreSQL to be used in production systems, and when
comparisons are done, stored procs is a biggy, even though I'm sure that
most of the people who ask the question are not even really sure what a
stored proc is.  They just like to use it in order to perform feature
comparison.
However, in a system that uses stored procs, of course, conversion to PG is
out, because we just don't have them.  Remember that from a design position,
there is an argument that all data should be accessed through stored procs
(except very ad-hoc stuff), in order to abstract the implementation a
further level.  Whether or not this works, or whether it's desirable in a
design is another question which I'm not attempting to answer.  However,
there are a lot of systems out there that have them.


Thoughts...

Also, has anything happened about the idea to get PG to cluster (somebody
mentioned Beowulf)?



--------------------------------------------------------------------
Science is the game we play with God to find out what his rules are.
--------------------------------------------------------------------

[(LI)U]NIX IS user friendly; it's just picky about who its friends are.




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

* RE: [SQL] Good Optimization
@ 1999-07-09 10:56  John Ridout <johnridout@ctasystems.co.uk>
  parent: Ansley, Michael <Michael.Ansley@intec.co.za>
  1 sibling, 1 reply; 27+ messages in thread

From: John Ridout @ 1999-07-09 10:56 UTC (permalink / raw)
  To: pgsql-sql; +Cc: 'Ansley, Michael' <Michael.Ansley@intec.co.za>

Hi,

I'm developing on PostgreSQL.  The lack of stored procedures
which return a rowset is a major disadvantage for PostgreSQL.
Completely aside from the performance issue are application
security and the architecture of the information system.
I'll give an example so I'm what I'm talking about. :-)

Based on the database user's identity I chose which rowset
to return.  We can't allow someone to peek at someone else's
data.  (They may be a few more steps before I decide which
rowset to return.)

The alternatives are not pretty.

1. Allow the client to chose which rowset is returned.
   This is means no security so its not really an option.

2. Create a database function which given the primary key
   of a table returns true or false for whether the current
   user may return this row.  Use this function in a view.
   Massively inefficient.

3. Adopt a 3-tier architecture for all parts of the system.
   Right down to the most trivial data entry and reporting.
   So now we have an unnecessarily complex system.  I.e. it
   will take longer, cost more and have more bugs.  Of course
   they won't be my bugs. ;-)

I have a small question where do I get started hacking PostgreSQL?
I'll have plenty of time to get started round about September.
In the meantime what homework do you suggest?
Clustering!?  Can we have a transaction log first?

Regards

John Ridout.

P.S.  Send me your tips for programming PostgreSQL.
      So far I have received hints and tips for
      Query optimization.
      Passing more than 8 arguments to a function.




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

* Re: Good Optimization
@ 1999-07-09 16:45  Bruce Momjian <maillist@candle.pha.pa.us>
  parent: Ansley, Michael <Michael.Ansley@intec.co.za>
  1 sibling, 0 replies; 27+ messages in thread

From: Bruce Momjian @ 1999-07-09 16:45 UTC (permalink / raw)
  To: Ansley, Michael <Michael.Ansley@intec.co.za>; +Cc: pgsql-sql

[Charset iso-8859-1 unsupported, filtering to ASCII...]
> Hi, Bruce,
> 
> I've only just picked up the thread on optimizations (I get the digest for
> the pgsql-sql list).  I really feel that a lot of effort could be saved with
> some good benefits if a stored proc mechanism is put into place.  Once that
> has been done, it can be used to store temporary plans (procedures) for
> ad-hoc queries which are released on the termination of the connection.
> However, I think that a lot of users will develop stored procs to replace a
> lot of their existing common SQL, in order to (partially) optimize their
> systems.  Perhaps in the PREPARE statement we could add a facility to allow
> the user to specify the TTL of the cached proc for an ad-hoc query.
> 
> When I talk about stored procs, I don't mean functions.  We already have
> those.  I mean procedures that are able to return a rowset.  Just to make
> sure nobody gets the wrong idea.

Not sure why our functions can't return tuples.

> Also, has anything happened about the idea to get PG to cluster (somebody
> mentioned Beowulf)?

No one has mentioned this.

-- 
  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] 27+ messages in thread

* Re: [SQL] Good Optimization
@ 1999-07-09 16:47  Bruce Momjian <maillist@candle.pha.pa.us>
  parent: John Ridout <johnridout@ctasystems.co.uk>
  0 siblings, 0 replies; 27+ messages in thread

From: Bruce Momjian @ 1999-07-09 16:47 UTC (permalink / raw)
  To: John Ridout <johnridout@ctasystems.co.uk>; +Cc: pgsql-sql; 'Ansley, Michael' <Michael.Ansley@intec.co.za>

> I have a small question where do I get started hacking PostgreSQL?
> I'll have plenty of time to get started round about September.

See the FAQ.  Read developers FAQ, flowchard, and developers manual.

> In the meantime what homework do you suggest?
> Clustering!?  Can we have a transaction log first?
> 


-- 
  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] 27+ messages in thread

* RE: [SQL] Re: Good Optimization
@ 1999-07-09 20:47  Jackson, DeJuan <djackson@cpsgroup.com>
  0 siblings, 0 replies; 27+ messages in thread

From: Jackson, DeJuan @ 1999-07-09 20:47 UTC (permalink / raw)
  To: Bruce Momjian <maillist@candle.pha.pa.us>; Ansley, Michael <Michael.Ansley@intec.co.za>; +Cc: pgsql-sql

> [Charset iso-8859-1 unsupported, filtering to ASCII...]
> > Hi, Bruce,
> > 
> > I've only just picked up the thread on optimizations (I get the digest
> for
> > the pgsql-sql list).  I really feel that a lot of effort could be saved
> with
> > some good benefits if a stored proc mechanism is put into place.  Once
> that
> > has been done, it can be used to store temporary plans (procedures) for
> > ad-hoc queries which are released on the termination of the connection.
> > However, I think that a lot of users will develop stored procs to
> replace a
> > lot of their existing common SQL, in order to (partially) optimize their
> > systems.  Perhaps in the PREPARE statement we could add a facility to
> allow
> > the user to specify the TTL of the cached proc for an ad-hoc query.
> > 
> > When I talk about stored procs, I don't mean functions.  We already have
> > those.  I mean procedures that are able to return a rowset.  Just to
> make
> > sure nobody gets the wrong idea.
> 
> Not sure why our functions can't return tuples.
> 
> > Also, has anything happened about the idea to get PG to cluster
> (somebody
> > mentioned Beowulf)?
> 
> No one has mentioned this.
> 
	I believe he's referring to having PostgreSQL have distributed
loading across multiple machines.  I've been thinking of testing a Beowulf
setup here for fault tolerance and fail-over.  It's might be the most
elegant and easy solution to implement, starting with a Red Hat box.  
	It's time to move www.airforce.com to PostgreSQL and PHP :)
		-DEJ



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

* Re: [SQL] Good Optimization
@ 1999-07-19 14:02  secret <secret@kearneydev.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 27+ messages in thread

From: secret @ 1999-07-19 14:02 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql; John Ridout <johnridout@ctasystems.co.uk>

Tom Lane wrote:

> secret <secret@kearneydev.com> writes:
> >     There is a simple way to optimize SQL queries involving joins to
> > PostgreSQL that I think should be handled by Postgre?  If one is joining
> > a tables a,b on attribute "x" and if one has something like x=3 then it
> > helps A LOT to say: a.x=3 and b.x=3 in addition to saying a.x=b.x ...
> > The example below shoulds the radical speed gain of doing this, and I
> > think it isn't something real obvious to most people...
>
> How much *actual* speedup is there?  I don't trust the optimizer's
> numbers as anything more than relative measures ;-)
>
> I'm a bit surprised that you are getting a nested-loop plan and not
> a merge or hash join.  With a merge join, at least, there ought not be
> a large difference from providing the additional qual clause (I think).
> What Postgres version are you using?
>
>                         regards, tom lane

    The actual performance difference is HUGE.  Hours vs minutes or Minutes vs
Seconds...

David Secret
MIS Director
Kearney Development Co., Inc.





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

* Re: [SQL] Good Optimization
@ 1999-07-19 14:15  secret <secret@kearneydev.com>
  parent: Bruce Momjian <maillist@candle.pha.pa.us>
  0 siblings, 1 reply; 27+ messages in thread

From: secret @ 1999-07-19 14:15 UTC (permalink / raw)
  To: Bruce Momjian <maillist@candle.pha.pa.us>; +Cc: Jan Wieck <jwieck@debis.com>; Patrik Kudo <kudo@partitur.se>; pgsql-sql

Bruce Momjian wrote:

> >     IMHO we're improving optimization more and more on  the  cost
> >     of  query  parse/rewrite/optimize/plan time. Thus performance
> >     of statements that EXECUTE fast slows  down  more  and  more.
> >     Isn't   it   time   to   think   about  some  (maybe  shared)
> >     "parsetree->plan" cache that provides ready to use  plans  if
> >     only Const values have changed?
>
> TODO list has:
>
>         * Cache most recent query plan(s?)
>
> --
>   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

    Why not let the user select how much time he wants the DB to spend on
optimization?  maybe an explicit "FAST SELECT ..." will skip the costly steps
for subsecond queries, or "OPTIMIZE SELECT" for the long(hour) queries that
really need a lot of help :)... Or even a SET OPTION= for turning it on/off?

--David





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

* Re: [SQL] Good Optimization
@ 1999-07-19 14:37  Bruce Momjian <maillist@candle.pha.pa.us>
  parent: secret <secret@kearneydev.com>
  0 siblings, 0 replies; 27+ messages in thread

From: Bruce Momjian @ 1999-07-19 14:37 UTC (permalink / raw)
  To: secret <secret@kearneydev.com>; +Cc: Jan Wieck <jwieck@debis.com>; Patrik Kudo <kudo@partitur.se>; pgsql-sql

>     Why not let the user select how much time he wants the DB to spend on
> optimization?  maybe an explicit "FAST SELECT ..." will skip the costly steps
> for subsecond queries, or "OPTIMIZE SELECT" for the long(hour) queries that
> really need a lot of help :)... Or even a SET OPTION= for turning it on/off?

In most cases, the optimizer knows much better how long to optimize, and
the time it spends is usually very small.  Most time is spent in
executor.  Pre-6.5 optimizer had problems, so this was not true then.
-- 
  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] 27+ messages in thread

* Re: [SQL] Good Optimization
@ 1999-07-19 15:14  Tom Lane <tgl@sss.pgh.pa.us>
  parent: secret <secret@kearneydev.com>
  0 siblings, 0 replies; 27+ messages in thread

From: Tom Lane @ 1999-07-19 15:14 UTC (permalink / raw)
  To: secret <secret@kearneydev.com>; +Cc: pgsql-sql; John Ridout <johnridout@ctasystems.co.uk>

secret <secret@kearneydev.com> writes:
> Tom Lane wrote:
>> How much *actual* speedup is there?  I don't trust the optimizer's
>> numbers as anything more than relative measures ;-)
>> 
>> I'm a bit surprised that you are getting a nested-loop plan and not
>> a merge or hash join.  With a merge join, at least, there ought not be
>> a large difference from providing the additional qual clause (I think).
>> What Postgres version are you using?

> The actual performance difference is HUGE.  Hours vs minutes or Minutes vs
> Seconds...

Well, yeah, it could be huge in a nested-loop scenario; in a mergejoin
I think it would make little difference.

Actually, if the inner path is indexed then this shouldn't make any
difference for a nestloop either; each probe into the inner path *ought*
to be using the value of the current outer tuple's join variable as an
indexqual constraint, which would have the same limiting effect as the
explicit restriction you propose adding.  There is code in the optimizer
that claims to be making that happen.  Sounds like it is broken :-(

What are the data types of po_id and material_po in your example?
And, again, which Postgres version exactly?

			regards, tom lane



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


end of thread, other threads:[~1999-07-19 15:14 UTC | newest]

Thread overview: 27+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-07-07 14:56 Good Optimization secret <secret@kearneydev.com>
1999-07-07 15:22 ` Roderick A. Anderson <raanders@altoplanos.net>
1999-07-07 15:44   ` Tom Lane <tgl@sss.pgh.pa.us>
1999-07-07 16:10     ` Roderick A. Anderson <raanders@altoplanos.net>
1999-07-07 16:45   ` Bruce Momjian <maillist@candle.pha.pa.us>
1999-07-07 15:42 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-07-19 14:02   ` secret <secret@kearneydev.com>
1999-07-19 15:14     ` Tom Lane <tgl@sss.pgh.pa.us>
1999-07-07 17:22 ` Jan Wieck <wieck@debis.com>
1999-07-07 18:00   ` Bruce Momjian <maillist@candle.pha.pa.us>
1999-07-08 08:09     ` Patrik Kudo <kudo@partitur.se>
1999-07-08 09:04       ` Jan Wieck <wieck@debis.com>
1999-07-08 09:23         ` Patrik Kudo <kudo@partitur.se>
1999-07-08 10:24           ` Jan Wieck <wieck@debis.com>
1999-07-08 15:01         ` Tom Lane <tgl@sss.pgh.pa.us>
1999-07-08 15:48         ` Bruce Momjian <maillist@candle.pha.pa.us>
1999-07-19 14:15           ` secret <secret@kearneydev.com>
1999-07-19 14:37             ` Bruce Momjian <maillist@candle.pha.pa.us>
1999-07-08 15:40       ` Bruce Momjian <maillist@candle.pha.pa.us>
1999-07-07 18:10   ` Tom Lane <tgl@sss.pgh.pa.us>
1999-07-07 19:09     ` Bruce Momjian <maillist@candle.pha.pa.us>
1999-07-09 09:59 RE: [SQL] Good Optimization Ansley, Michael <Michael.Ansley@intec.co.za>
1999-07-09 10:15 Good Optimization Ansley, Michael <Michael.Ansley@intec.co.za>
1999-07-09 10:56 ` John Ridout <johnridout@ctasystems.co.uk>
1999-07-09 16:47   ` Bruce Momjian <maillist@candle.pha.pa.us>
1999-07-09 16:45 ` Bruce Momjian <maillist@candle.pha.pa.us>
1999-07-09 20:47 RE: [SQL] Re: Good Optimization Jackson, DeJuan <djackson@cpsgroup.com>

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