Presto: Push down LIMIT clause to connector source level

[alexliu68]

so connector can use the Limit clause to limit the result

[geetikagupta16]

Hi, Is there any way by which we can push down the limit clause to our connector?

[kokosing]

AFAIK currently it is not yet supported.

[geetikagupta16]

Thanks, @kokosing for the quick reply.
Actually, We are trying to implement our custom connector for a project which stores data by performing some encoding on the data. Is there a way by which we can perform post decoding of data only on the resultant data. As right now we are doing the decoding process on all the data regardless of the count in the limit query. So Is there a way by which I can perform the limit operation before the decoding.

[kokosing]

What about using LazyBlock, does it answer your need? That way data is
not decoded until it is used. However, whole block is decoded even if you
decode single value.

On Tue, Jul 25, 2017 at 2:36 PM, Geetika Gupta [e-mail]
wrote:

Thanks, @kokosing https://github.com/kokosing for the quick reply.
Actually, We are trying to implement our custom connector for a project
which stores data by performing some encoding on the data. Is there a way
by which we can perform post decoding of data only on the resultant data.
As right now we are doing the decoding process on all the data regardless
of the count in the limit query. So Is there a way by which I can perform
the limit operation before the decoding.

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
https://github.com/prestodb/presto/issues/1112#issuecomment-317723877,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AHN_-0tmLJgykzR0TbsGPrPcqIbSQi8qks5sReFlgaJpZM4Bob-T
.

[findepi]

SQL connectors (think presto-base-jdbc) should be able to benefit from LIMIT pushdown.

Also, consider a large, partitioned Hive table. A user may want to select some 100 rows to see how the data looks like:

select * from table limit 100

During query scheduling, we will create and enqueue all splits. If the table has over hive.max-partitions-per-scan partitions, such a query would fail.
If we pushed the LIMIT down to com.facebook.presto.spi.connector.ConnectorMetadata#getTableLayouts, the connector would be able to return fewer partitions (as long as we can know the partitions are non-empty).

[kokosing]

It seems to be ONLY valid to queries you mentioned:

select * from table limit 100

According to ANSI SQL, such query will return 100 first random rows from table. That would mean that user want to preview some of the rows from given table. To achieve similar effect user could use TABLESAMPLE, see https://prestodb.io/docs/current/sql/select.html#tablesample:

select * from table TABLESAMPLE SYSTEM(1);

Considering the limit application space of this, I wonder how useful such feature would be. Or simply, do you think it is worth to do it?

[electrum]

We actually won’t schedule all the splits for such a query. The engine will ask for a batch of splits, then the Hive split source will fetch metadata for a few partitions, then list files in the partition location, and stop when it’s internal queue is full. The query should finish quickly, long before we do this for the whole table. This feature is called “pipelined scheduling”.

Limit pushdown wouldn’t work if there is a filter, unless the entire filter could be pushed down and the connector could guarantee it evaluates the filter fully. Otherwise, it could return rows that are filtered out and the query would return too few rows.

[kokosing]

What about a table which has more partitions that hive.max-partitions-per-scan? As I understand such query does not work today, even if reading only few first partition would be enough to satisfy the query. Does pipelined scheduling has to be somehow explicitly enabled?

[electrum]

@kokosing that is a safety feature to avoid the coordinator running out of memory or doings full GC. Hopefully, you never have 100k partitions in a table. Or if you do, it’s only a few, so the query still works for 99.99% of the tables. And for the others, the user can add a partition filter.

[kokosing]

This feature might be useful also in case where whole predicate is pushed down to connector, queries like:

SELECT * FROM t WHERE a = 1 AND b = 3 LIMIT 100

[dain]

@martint and I have chatted about this before. IIRC he thought it was a good idea. If so, I don't think it would be too hard to implement, maybe add another field to spi.Constraint which is passed to getTableLayout

[findepi]

... the query still works for 99.99% of the tables. And for the others, the user can add a partition filter.

@electrum this works as long as you know what kind of filter you can add. However, if you are e.g. a new data scientist at a company, come to work with existing tables and you don't know exact values (e.g. some device type codes), then you will have hard time finding any reasonable partition filter.
And queries like SELECT partkey FROM .. LIMIT 10 or SHOW PARTITIONS .. LIMIT 10 will all fail now.

So this is not only about performance (here it might be useful or not), but also about specific data discovery use case which is a real need of some users and is not possible today.

maybe add another field to spi.Constraint which is passed to getTableLayout

This would be possible to use by JDBC-based connectors in quite straightforward manner, which is good.
@dain @electrum how this could be made to work with Hive?
Usually, with LIMIT n, first n rows will be all in a first partition, but we can't know that. How can we make SELECT partkey FROM .. LIMIT 10 or SHOW PARTITIONS .. LIMIT 10 work with Hive table that is overly partitioned?

[stale[bot]]

This issue has been automatically marked as stale because it has not had any activity in the last 2 years. If you feel that this issue is important, just comment and the stale tag will be removed; otherwise it will be closed in 7 days. This is an attempt to ensure that our open issues remain valuable and relevant so that we can keep track of what needs to be done and prioritize the right things.