Re: [HACKERS] Parameterized-path cost comparisons need some work

2012-04-18 Thread Greg Stark
On Tue, Apr 17, 2012 at 5:14 PM, Tom Lane t...@sss.pgh.pa.us wrote: I've been hacking away on a patch to do this, and attached is something that I think is pretty close to committable. I have nothing to add but I just wanted to say thank you for taking the time to write up this explanation.

Re: [HACKERS] Parameterized-path cost comparisons need some work

2012-04-18 Thread Hakan Kocaman
2012/4/18 Greg Stark st...@mit.edu On Tue, Apr 17, 2012 at 5:14 PM, Tom Lane t...@sss.pgh.pa.us wrote: I've been hacking away on a patch to do this, and attached is something that I think is pretty close to committable. [..]Even when some of us don't comment on some of the longer, more

Re: [HACKERS] Parameterized-path cost comparisons need some work

2012-04-18 Thread Tom Lane
So while testing this patch I've found out that there is a pretty nasty bug in HEAD as well as in my current formulation of the patch. Here is an example using the regression database: select count(*) from (tenk1 a join tenk1 b on a.unique1=b.unique2) left join tenk1 c on a.unique2 =

Re: [HACKERS] Parameterized-path cost comparisons need some work

2012-04-17 Thread Robert Haas
On Tue, Apr 17, 2012 at 12:14 PM, Tom Lane t...@sss.pgh.pa.us wrote: I've been hacking away on a patch to do this, and attached is something that I think is pretty close to committable.  It needs another going-over and some new regression test cases, but it seems to work, and it fixes a number

Re: [HACKERS] Parameterized-path cost comparisons need some work

2012-04-17 Thread Alvaro Herrera
Excerpts from Robert Haas's message of mar abr 17 15:46:23 -0300 2012: On Tue, Apr 17, 2012 at 12:14 PM, Tom Lane t...@sss.pgh.pa.us wrote: The core of the patch is in the new functions get_baserel_parampathinfo and get_joinrel_parampathinfo, which look up or construct ParamPathInfos, and

Re: [HACKERS] Parameterized-path cost comparisons need some work

2012-04-17 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes: On Tue, Apr 17, 2012 at 12:14 PM, Tom Lane t...@sss.pgh.pa.us wrote: BTW, after writing the code for it I decided to remove creation of parameterized MergeAppendPaths from allpaths.c, though there is still some support for them elsewhere. On

Re: [HACKERS] Parameterized-path cost comparisons need some work

2012-04-17 Thread Robert Haas
On Tue, Apr 17, 2012 at 3:05 PM, Tom Lane t...@sss.pgh.pa.us wrote: Well, we already made a policy decision that we weren't going to try very hard to support merge joins inside parameterized subtrees, because the potential growth in planning time looked nasty.  My thought was that we might

Re: [HACKERS] Parameterized-path cost comparisons need some work

2012-04-17 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes: On Tue, Apr 17, 2012 at 3:05 PM, Tom Lane t...@sss.pgh.pa.us wrote: Personally, I find required_outer more clear. YMMV. Perhaps. What's bothering me is the potential for confusion with outer joins; the parameter-supplying rels are *not* necessarily

Re: [HACKERS] Parameterized-path cost comparisons need some work

2012-04-13 Thread Robert Haas
On Thu, Apr 12, 2012 at 3:27 PM, Tom Lane t...@sss.pgh.pa.us wrote: 1. Lobotomize add_path_precheck so it always returns true for a parameterized path.  This sounds horrid, but in the test cases I'm using it seems that this only results in doing the full path construction for a very small

Re: [HACKERS] Parameterized-path cost comparisons need some work

2012-04-12 Thread Tom Lane
I wrote: So I'm back to thinking we need to look explicitly at the rowcount comparison as well as all the existing conditions in add_path. One annoying thing about that is that it will reduce the usefulness of add_path_precheck, because that's called before we compute the rowcount estimates

Re: [HACKERS] Parameterized-path cost comparisons need some work

2012-03-05 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes: On Sun, Mar 4, 2012 at 12:20 AM, Tom Lane t...@sss.pgh.pa.us wrote: One annoying thing about that is that it will reduce the usefulness of add_path_precheck, because that's called before we compute the rowcount estimates (and indeed not having to make

Re: [HACKERS] Parameterized-path cost comparisons need some work

2012-03-05 Thread Robert Haas
On Mon, Mar 5, 2012 at 1:02 PM, Tom Lane t...@sss.pgh.pa.us wrote:  But it's nervous-making to be making decisions like that on the basis of rather small sets of queries. I heartily agree. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via

Re: [HACKERS] Parameterized-path cost comparisons need some work

2012-03-04 Thread Robert Haas
On Sun, Mar 4, 2012 at 12:20 AM, Tom Lane t...@sss.pgh.pa.us wrote: After looking at the results, I think that the fallacy in what we've been discussing is this: a parameterized path may well have some extra selectivity over a less-parameterized one, but perhaps *not enough to be meaningful*.  

Re: [HACKERS] Parameterized-path cost comparisons need some work

2012-03-03 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes: On Wed, Feb 29, 2012 at 6:01 PM, Tom Lane t...@sss.pgh.pa.us wrote: (... thinks some more ...) No, that doesn't get us there, because that doesn't establish that a more-parameterized path produces fewer rows than some path that requires less

Re: [HACKERS] Parameterized-path cost comparisons need some work

2012-03-01 Thread Robert Haas
On Wed, Feb 29, 2012 at 6:01 PM, Tom Lane t...@sss.pgh.pa.us wrote: Well, my evidence is that a parameterized path should pretty much always include a paramaterized path somewhere in there - otherwise, what is parameterization doing for us? Well, yes, we know that much. I didn't write what I

Re: [HACKERS] Parameterized-path cost comparisons need some work

2012-02-29 Thread Simon Riggs
On Tue, Feb 28, 2012 at 10:35 PM, Tom Lane t...@sss.pgh.pa.us wrote: The flaw in this logic, of course, is that the seqscan might be cheaper than the parameterized indexscan, but *it produces a whole lot more rows*, meaning that any subsequent join will be a lot more expensive. Previously

Re: [HACKERS] Parameterized-path cost comparisons need some work

2012-02-29 Thread Robert Haas
On Tue, Feb 28, 2012 at 5:35 PM, Tom Lane t...@sss.pgh.pa.us wrote: The most obvious thing to do about this is to consider that one path can dominate another on cost only if it is both cheaper *and* produces no more rows. Hmm. Are you sure that's the right rule? I am having trouble

Re: [HACKERS] Parameterized-path cost comparisons need some work

2012-02-29 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes: On Tue, Feb 28, 2012 at 5:35 PM, Tom Lane t...@sss.pgh.pa.us wrote: The most obvious thing to do about this is to consider that one path can dominate another on cost only if it is both cheaper *and* produces no more rows. Hmm. Are you sure that's

Re: [HACKERS] Parameterized-path cost comparisons need some work

2012-02-29 Thread Robert Haas
On Wed, Feb 29, 2012 at 1:40 PM, Tom Lane t...@sss.pgh.pa.us wrote: I am having trouble constructing an example, but I feel like there might be cases where it's possible to have path A, not parameterized, path B, parameterized by R, and path C, parameterized by S, and maybe also path D,

Re: [HACKERS] Parameterized-path cost comparisons need some work

2012-02-29 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes: On Wed, Feb 29, 2012 at 1:40 PM, Tom Lane t...@sss.pgh.pa.us wrote: Indeed, and the code already knows that.  However, in this example, path A is capable of dominating the other three (being strictly less parameterized than them), and B and C are each

Re: [HACKERS] Parameterized-path cost comparisons need some work

2012-02-29 Thread Amit Kapila
@postgreSQL.org Subject: [HACKERS] Parameterized-path cost comparisons need some work I was experimenting today with a test case sent to me by somebody at Red Hat. The details aren't too important, except that it involves an inner join on the inside of a left join (where it cannot commute with the left

Re: [HACKERS] Parameterized-path cost comparisons need some work

2012-02-29 Thread Robert Haas
On Wed, Feb 29, 2012 at 2:08 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Wed, Feb 29, 2012 at 1:40 PM, Tom Lane t...@sss.pgh.pa.us wrote: Indeed, and the code already knows that.  However, in this example, path A is capable of dominating the other three

Re: [HACKERS] Parameterized-path cost comparisons need some work

2012-02-29 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes: On Wed, Feb 29, 2012 at 2:08 PM, Tom Lane t...@sss.pgh.pa.us wrote: I think you're just assuming that without any solid evidence.  My point is precisely that if the more-parameterized path *fails* to generate fewer rows, we want add_path to notice that

[HACKERS] Parameterized-path cost comparisons need some work

2012-02-28 Thread Tom Lane
I was experimenting today with a test case sent to me by somebody at Red Hat. The details aren't too important, except that it involves an inner join on the inside of a left join (where it cannot commute with the left join). I can reproduce the behavior with a standard regression test table, if