search

trinodb / trino

Official repository of Trino, the distributed SQL query engine for big data, formerly known as PrestoSQL (https://trino.io)
https://trino.io
Apache License 2.0
10.49k stars 3.02k forks source link

Query with limit on s3 is not working optimally #21595

Open AlonHarmon opened 7 months ago

AlonHarmon commented 7 months ago

When query on hive partitioned table that is on s3 with limit, trino loads all of the queried partition and only then evaluates the limit part of the query. On huge partitioned tables it makes it impossible to do the simple following query:

select * from hive.schema.tablename limit 10;

Env for reproduction (altho it shouldn't matter) - Trino version - 436 Catalog - hive Storage - s3 compatible ceph Objects format - parquet

Do you think it's possible to make the coordinator check every x seconds how many rows each task retrieved and then choose if to abort the rest and return the combined results?

About queries with filtering AND limit, maybe the same is possible but to do it only to the last query stage (where the limit should happen)

hashhar commented 7 months ago

Are you using FTE by any chance?

julienlau commented 5 months ago

you suggest retry-policy=NONE can also impact performance ?

I also observed large query plan differences between Spark-sql and Trino on S3 for simple queries like select * table limit 100;

julienlau commented 5 months ago

I think you had this behind your mind : https://github.com/trinodb/trino/pull/18862