Re: TABLESAMPLE patch

On 09/04/15 21:30, Peter Eisentraut wrote:
>
> In the SQL standard, the TABLESAMPLE clause is attached to a table
> expression (<table primary>), which includes table functions,
> subqueries, CTEs, etc. In the proposed patch, it is attached to a table
> name, allowing only an ONLY clause. So this is a significant deviation.
>

I wouldn't call something that implements subset of standard a
deviation. Especially if other major dbs have chosen same approach
(afaik the only db that supports sampling over something besides
physical relations is Oracle but their sampling works slightly
differently than what standard has).

> Obviously, doing block sampling on a physical table is a significant use
> case, but we should be clear about which restrictions and tradeoffs were
> are making now and in the future, especially if we are going to present
> extension interfaces. The fact that physical tables are interchangeable
> with other relation types, at least in data-reading contexts, is a
> feature worth preserving.

Yes, but I don't think there is anything that prevents us from adding
this in the future. The sampling scan could made to be able to read both
directly from heap and from executor subnode which is doable even if it
won't be extremely pretty (but it should be easy to encapsulate into 2
internal interfaces as the heap reading is encapsulated to 1 internal
interface already). Another approach would be having two different
executor nodes - SampingScan and SamplingFilter and letting planner pick
one depending on what is the source for TABLESAMPLE clause.

The extension api is currently mainly:
nextblock - gets next blockid to read from heap
nextuple - gets next tupleid to read current block
examinetuple - lets the extension to decide if tuple should be indeed
returned (this one is optional)

For the executor node reading we'd probably just call the examinetuple
as there are no block ids or tuple ids there. This makes the API look
slightly schizophrenic but on the other hand it gives the plugins
control over how physical relation is read if that's indeed the source.
And I guess we could let the plugin specify if it supports the heap
access (nextblock/nexttuple) and if it doesn't then planner would always
choose SamplingFilter over SequentialScan for physical relation instead
of SamplingScan.

All of this is possible to add without breaking compatibility with what
is proposed for commit currently.

The reasons why we need the nextblock and nexttuple interfaces and the
ability to read the heap directly are a) block sampling can't be done by
reading from another executor node, b) performance.

>
> It may be worth thinking about some examples of other sampling methods,
> in order to get a better feeling for whether the interfaces are appropriate.
>

There is one additional method which is just purely for testing the
interface and that uses column value to determine if the tuple should be
returned or not (which is useless in practice obviously as you can do
that using WHERE, it just shows how to use the interface).

I would like to eventually have something that's time limited rather
than size limited for example. I didn't think much about other sampling
algorithms but Simon proposed some and they should work with the current
API.

> Earlier in the thread, someone asked about supporting specifying a
> number of rows instead of percents. While not essential, that seems
> pretty useful, but I wonder how that could be implemented later on if we
> take the approach that the argument to the sampling method can be an
> arbitrary quantity that is interpreted only by the method.
>

Well, you can have two approaches to this, either allow some specific
set of keywords that can be used to specify limit, or you let sampling
methods interpret parameters, I believe the latter is more flexible.
There is nothing stopping somebody writing sampling method which takes
limit as number of rows, or anything else.

Also for example for BERNOULLI to work correctly you'd need to convert
the number of rows to fraction of table anyway (and that's exactly what
the one database which has this feature does internally) and then it's
no different than passing (SELECT 100/reltuples*number_of_rows FROM
tablename) as a parameter.

--
Petr Jelinek http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services