Thread: Boosting cost estimates for some built-in functions

We recently saw a complaint about psql \d commands being quite slow
with many tables, which turned out to be because the planner was
putting a table_has_privilege() call where it would get executed a
lot, before other cheaper tests on pg_class.  This is not the
planner's fault --- it has no information that would suggest that
table_has_privilege() is any more expensive than any other built-in
function.

In another case I was looking at just now, it seems that to_tsquery()
and to_tsvector() are noticeably slower than most other built-in
functions, which is not surprising given the amount of mechanism that
gets invoked inside them.  It would be useful to tell the planner
about that to discourage it from picking seqscan plans that involve
repeated execution of these functions.

I'd like to suggest boosting the built-in cost estimates for the
xxx_has_privilege functions to perhaps 10.  to_tsquery and to_tsvector
maybe should be boosted even higher, but I don't have a good specific
number in mind.

Thoughts?
        regards, tom lane

On Sun, 2009-10-18 at 14:50 -0400, Tom Lane wrote:

> I'd like to suggest boosting the built-in cost estimates for the
> xxx_has_privilege functions to perhaps 10.  to_tsquery and to_tsvector
> maybe should be boosted even higher, but I don't have a good specific
> number in mind.

ISTM we should set up a test that measures the cost, of those and other
functions.

-- Simon Riggs           www.2ndQuadrant.com

On Sun, Oct 18, 2009 at 3:38 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
> On Sun, 2009-10-18 at 14:50 -0400, Tom Lane wrote:
>
>> I'd like to suggest boosting the built-in cost estimates for the
>> xxx_has_privilege functions to perhaps 10.  to_tsquery and to_tsvector
>> maybe should be boosted even higher, but I don't have a good specific
>> number in mind.
>
> ISTM we should set up a test that measures the cost, of those and other
> functions.

Benchmarking???  Heresy!

...Robert

Simon Riggs <simon@2ndQuadrant.com> writes:
> On Sun, 2009-10-18 at 14:50 -0400, Tom Lane wrote:
>> I'd like to suggest boosting the built-in cost estimates for the
>> xxx_has_privilege functions to perhaps 10.  to_tsquery and to_tsvector
>> maybe should be boosted even higher, but I don't have a good specific
>> number in mind.

> ISTM we should set up a test that measures the cost, of those and other
> functions.

Well, some quick testing suggests that 10x is in the right ballpark for
has_table_privilege versus a simple function such as OID equality,
and that even fairly simple cases of to_tsvector/to_tsquery can be
100x the cost of OID equality.  I'm not sure if there is a "typical
case" for the latter given the wide range of possible tsearch
configurations, so trying to benchmark it with any degree of precision
might be pointless.
        regards, tom lane