Re: [HACKERS] Performance problem in textanycat/anytextcat

2010-05-29 Thread Robert Haas
On Mon, May 17, 2010 at 9:23 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Mon, May 17, 2010 at 4:01 PM, Tom Lane t...@sss.pgh.pa.us wrote: Perhaps this is a backpatchable bug fix.  Comments? I can't say whether this is safe enough to back-patch, but the

Re: [HACKERS] Performance problem in textanycat/anytextcat

2010-05-29 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes: This is still on the 9.0 open items list, but ISTM you fixed it with two commits on May 27th. Is that correct? Oh, sorry, forgot to update the open items. Done now. regards, tom lane -- Sent via pgsql-hackers mailing list

Re: [HACKERS] Performance problem in textanycat/anytextcat

2010-05-17 Thread Tom Lane
I wrote: Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: Marking textanycat as not immutable would forbid using it in expression indexes, too. True. On the other hand, the current state of affairs allows one to create an index on expressions that aren't really immutable, with

Re: [HACKERS] Performance problem in textanycat/anytextcat

2010-05-17 Thread Robert Haas
On Mon, May 17, 2010 at 4:01 PM, Tom Lane t...@sss.pgh.pa.us wrote: I wrote: Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: Marking textanycat as not immutable would forbid using it in expression indexes, too. True.  On the other hand, the current state of affairs allows one

Re: [HACKERS] Performance problem in textanycat/anytextcat

2010-05-17 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes: On Mon, May 17, 2010 at 4:01 PM, Tom Lane t...@sss.pgh.pa.us wrote: Perhaps this is a backpatchable bug fix.  Comments? I can't say whether this is safe enough to back-patch, but the way this is set up, don't we also need to fix some catalog entries

Re: [HACKERS] Performance problem in textanycat/anytextcat

2010-05-17 Thread Robert Haas
On Mon, May 17, 2010 at 9:23 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Mon, May 17, 2010 at 4:01 PM, Tom Lane t...@sss.pgh.pa.us wrote: Perhaps this is a backpatchable bug fix.  Comments? I can't say whether this is safe enough to back-patch, but the

Re: [HACKERS] Performance problem in textanycat/anytextcat

2010-05-16 Thread Heikki Linnakangas
Tom Lane wrote: So I think that labeling textanycat/anytextcat as immutable was a thinko, and we instead ought to label them volatile so that the planner can inline them no matter what the behavior of the underlying text cast is. That feels backwards, having to label functions as more

Re: [HACKERS] Performance problem in textanycat/anytextcat

2010-05-16 Thread Tom Lane
Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: Tom Lane wrote: So I think that labeling textanycat/anytextcat as immutable was a thinko, and we instead ought to label them volatile so that the planner can inline them no matter what the behavior of the underlying text cast is.

Re: [HACKERS] Performance problem in textanycat/anytextcat

2010-05-16 Thread Tom Lane
I wrote: Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: Marking textanycat as not immutable would forbid using it in expression indexes, too. True. On the other hand, the current state of affairs allows one to create an index on expressions that aren't really immutable, with

Re: [HACKERS] Performance problem in textanycat/anytextcat

2010-05-16 Thread Robert Haas
On Sat, May 15, 2010 at 9:51 PM, Tom Lane t...@sss.pgh.pa.us wrote: I noticed by accident that there are some cases where the planner fails to inline the SQL functions that underlie the || operator for text vs non-text cases.  The reason is that these functions are marked immutable, but their

Re: [HACKERS] Performance problem in textanycat/anytextcat

2010-05-16 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes: Couldn't you apply this argument to any built-in immutable function whatsoever? No, only the ones that are built on top of other functions that aren't immutable. I did go looking for other potential problems of the same ilk. The only one I can find

Re: [HACKERS] Performance problem in textanycat/anytextcat

2010-05-16 Thread Robert Haas
On Sun, May 16, 2010 at 1:11 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: Couldn't you apply this argument to any built-in immutable function whatsoever? No, only the ones that are built on top of other functions that aren't immutable. Built on top of?

Re: [HACKERS] Performance problem in textanycat/anytextcat

2010-05-16 Thread Jaime Casanova
On Sun, May 16, 2010 at 1:20 PM, Robert Haas robertmh...@gmail.com wrote: On Sun, May 16, 2010 at 1:11 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: Couldn't you apply this argument to any built-in immutable function whatsoever? No, only the ones that are

Re: [HACKERS] Performance problem in textanycat/anytextcat

2010-05-16 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes: On Sun, May 16, 2010 at 1:11 PM, Tom Lane t...@sss.pgh.pa.us wrote: No, only the ones that are built on top of other functions that aren't immutable. Built on top of? I don't get it. It seems like anything of the form

Re: [HACKERS] Performance problem in textanycat/anytextcat

2010-05-16 Thread Robert Haas
On Sun, May 16, 2010 at 2:59 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Sun, May 16, 2010 at 1:11 PM, Tom Lane t...@sss.pgh.pa.us wrote: No, only the ones that are built on top of other functions that aren't immutable. Built on top of?  I don't get

Re: [HACKERS] Performance problem in textanycat/anytextcat

2010-05-16 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes: I guess my point is that the actual volatility of an expression is presumably independent of whether it gets inlined. (If inlining is changing the semantics, that's a problem.) So if inlining is changing it's apparent volatility, then there's

[HACKERS] Performance problem in textanycat/anytextcat

2010-05-15 Thread Tom Lane
I noticed by accident that there are some cases where the planner fails to inline the SQL functions that underlie the || operator for text vs non-text cases. The reason is that these functions are marked immutable, but their expansion involves a coercion to text that might not be immutable. The

Re: [HACKERS] Performance problem in textanycat/anytextcat

2010-05-15 Thread Jaime Casanova
On Sat, May 15, 2010 at 8:51 PM, Tom Lane t...@sss.pgh.pa.us wrote: Is it reasonable to fix this now, and if so should I bump catversion or leave it alone?  My own preference is to fix it in pg_proc.h but not touch catversion; but you could argue that different ways. are you planning to

Re: [HACKERS] Performance problem in textanycat/anytextcat

2010-05-15 Thread Tom Lane
Jaime Casanova ja...@2ndquadrant.com writes: On Sat, May 15, 2010 at 8:51 PM, Tom Lane t...@sss.pgh.pa.us wrote: Is it reasonable to fix this now, and if so should I bump catversion or leave it alone?  My own preference is to fix it in pg_proc.h but not touch catversion; but you could argue

Re: [HACKERS] Performance problem in textanycat/anytextcat

2010-05-15 Thread Jaime Casanova
On Sat, May 15, 2010 at 10:16 PM, Tom Lane t...@sss.pgh.pa.us wrote: Jaime Casanova ja...@2ndquadrant.com writes: On Sat, May 15, 2010 at 8:51 PM, Tom Lane t...@sss.pgh.pa.us wrote: Is it reasonable to fix this now, and if so should I bump catversion or leave it alone?  My own preference is to

Re: [HACKERS] Performance problem in textanycat/anytextcat

2010-05-15 Thread Tom Lane
Jaime Casanova ja...@2ndquadrant.com writes: On Sat, May 15, 2010 at 10:16 PM, Tom Lane t...@sss.pgh.pa.us wrote: Jaime Casanova ja...@2ndquadrant.com writes: On Sat, May 15, 2010 at 8:51 PM, Tom Lane t...@sss.pgh.pa.us wrote: Is it reasonable to fix this now, and if so should I bump

Re: [HACKERS] Performance problem in textanycat/anytextcat

2010-05-15 Thread Jaime Casanova
On Sat, May 15, 2010 at 10:21 PM, Tom Lane t...@sss.pgh.pa.us wrote: Jaime Casanova ja...@2ndquadrant.com writes: On Sat, May 15, 2010 at 10:16 PM, Tom Lane t...@sss.pgh.pa.us wrote: Jaime Casanova ja...@2ndquadrant.com writes: On Sat, May 15, 2010 at 8:51 PM, Tom Lane t...@sss.pgh.pa.us