X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 466F29DC880 for ; Sat, 17 Dec 2005 03:28:15 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 67809-04 for ; Sat, 17 Dec 2005 03:28:15 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id F20E29DC84D for ; Sat, 17 Dec 2005 03:28:11 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id jBH7SBBH004253; Sat, 17 Dec 2005 02:28:11 -0500 (EST) To: "Craig A. James" cc: pgsql-performance@postgresql.org Subject: Re: Overriding the optimizer In-reply-to: <43A22368.8060307@modgraph-usa.com> References: <200512141603.52583.blargity@gmail.com> <7484.1134600423@sss.pgh.pa.us> <1134629567.14248.27.camel@firebolt> <200512150148.16125.blargity@gmail.com> <1134644526.14248.60.camel@firebolt> <43A1C40D.8040006@paradise.net.nz> <43A1F6DB.3080805@modgraph-usa.com> <18537.1134692629@sss.pgh.pa.us> <43A22368.8060307@modgraph-usa.com> Comments: In-reply-to "Craig A. James" message dated "Thu, 15 Dec 2005 18:16:08 -0800" Date: Sat, 17 Dec 2005 02:28:11 -0500 Message-ID: <4252.1134804491@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.012 required=5 tests=[AWL=0.012] X-Spam-Score: 0.012 X-Spam-Level: X-Archive-Number: 200512/421 X-Sequence-Number: 16242 "Craig A. James" writes: > How about this: Instead of arguing in the abstract, tell me in > concrete terms how you would address the very specific example I gave, > where myfunc() is a user-written function. To make it a little more > challenging, try this: myfunc() can behave very differently depending > on the parameters, and sometimes (but not always), the application > knows how it will behave and could suggest a good execution plan. A word to the wise: regression=# explain select * from tenk1 where ten > 5 and ten < 9 regression-# and myfunc(unique1,unique2); QUERY PLAN ------------------------------------------------------------------ Seq Scan on tenk1 (cost=0.00..533.00 rows=982 width=244) Filter: ((ten > 5) AND (ten < 9) AND myfunc(unique1, unique2)) (2 rows) regression=# explain select * from tenk1 where myfunc(unique1,unique2) regression-# and ten > 5 and ten < 9; QUERY PLAN ------------------------------------------------------------------ Seq Scan on tenk1 (cost=0.00..533.00 rows=982 width=244) Filter: (myfunc(unique1, unique2) AND (ten > 5) AND (ten < 9)) (2 rows) I might have taken your original complaint more seriously if it weren't so blatantly bogus. Your query as written absolutely would not have evaluated myfunc() first, because there was no reason for the planner to reorder the WHERE list. regards, tom lane