From: Kevin Brown <kevin@sysexperts.com>
To: pgsql-performance@postgresql.org
Subject: Re: Overriding the optimizer
Date: Thu, 15 Dec 2005 21:08:33 -0800
Message-ID: <20051216050833.GA691@filer> (raw)
In-Reply-To: <43A2251A.60907@modgraph-usa.com>
References: <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>
<43A21D74.8030002@familyhealth.com.au>
<c2d9e70e0512151800l5561fc04q7ee79593dcb4ea5c@mail.gmail.com>
<43A2203B.2050102@familyhealth.com.au>
<43A2251A.60907@modgraph-usa.com>
Craig A. James wrote:
>
>
> Christopher Kings-Lynne wrote:
> >>>> select * from my_table where row_num >= 50000 and row_num <
> >>>>100000
> >>>> and myfunc(foo, bar);
> >>>
> >>>
> >>>You just create an index on myfunc(foo, bar)
> >>
> >>
> >>only if myfunc(foo, bar) is immutable...
> >
> >
> >And if it's not then the best any database can do is to index scan
> >row_num - so still you have no problem.
>
> Boy, you picked a *really* bad example ;-)
>
> The problem is that Postgres decided to filter on myfunc() *first*, and
> then filter on row_num, resulting in a query time that jumped from seconds
> to hours. And there's no way for me to tell Postgres not to do that!
Apologies in advance if all of this has been said, or if any of it is
wrong.
What kind of plan do you get if you eliminate the myfunc(foo, bar)
from the query entirely? An index scan or a full table scan? If the
latter then (assuming that the statistics are accurate) the reason you
want inclusion of myfunc() to change the plan must be the expense of
the function, not the expense of the scan (index versus sequential).
While the expense of the function isn't, as far as I know, known or
used by the planner, that obviously needn't be the case.
On the other hand, if the inclusion of the function call changes the
plan that is selected from an index scan to a sequential scan, then
that, I think, is clearly a bug, since even a zero-cost function
cannot make the sequential scan more efficient than an index scan
which is already more efficient than the base sequential scan.
> So, "you still have no problem" is exactly wrong, because Postgres picked
> the wrong plan. Postgres decided that applying myfunc() to 10,000,000
> rows was a better plan than an index scan of 50,000 row_nums. So I'm
> screwed.
If PostgreSQL is indeed applying myfunc() to 10,000,000 rows, then
that is a bug if the function is declared VOLATILE (which is the
default if no volatility is specified), because it implies that it's
applying the function to rows that don't match the selection
condition. From your prior description, it sounds like your function
is declared STABLE.
For your specific situation, my opinion is that the proper
modification to PostgreSQL would be to give it (if it isn't already
there) the ability to include the cost of functions in the plan. The
cost needn't be something that it automatically measures -- it could
be specified at function creation time.
--
Kevin Brown kevin@sysexperts.com
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: kevin@sysexperts.com
Subject: Re: Overriding the optimizer
In-Reply-To: <20051216050833.GA691@filer>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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