Open xinyuliu12 opened 4 years ago
Hi! Thank you for the detailed information.
This is a pretty strange query - we either return the entire table or nothing. I think the fact that the second plan runs faster is to some extent an accident - the join process exits early when it sees that one side has no rows. I guess we could add some extra checking in the scan if the filter turns out to be constant and false. This is part of a greater problem that we currently can't reoptimize parts of the query with the result from a subquery (#23264).
Is there a reason you can't check the condition on the client side instead?
You could also write it this way which is I think more clear, and it should work as expected:
SELECT
o_orderpriority
FROM
orders
LIMIT
CASE
WHEN (SELECT n_comment FROM nation ORDER BY n_comment LIMIT 1) IS NOT DISTINCT FROM 'random string'
THEN 1000000000
ELSE 0
END
Thanks for your detailed reply! It would be great if you could comment on the challenges associated with optimizing these queries wherein there is a dependency on the sub-query.
We optimize the entire expression tree (which includes any subqueries) once, and then we run the subqueries, followed by the "main" query. We don't go back and re-optimize the main query with the actual results of the subquery (which in this case would either result in removing the scan operator altogether, or removing the filter). This is mainly because of architectural reasons that were carried over from previous iterations of our system. It's something we will fix at some point.
Thank you so much for your explanation!
See #53653 for another case.
See https://github.com/cockroachdb/cockroach/issues/82503#issuecomment-1163755810 for another case.
Hello everyone!
First off, I am sorry for gestures vaguely all of this. Second, I think we have found a performance bug related to a suboptimal plan selection when evaluating predicate.
Here is a pair of TPC-H queries that exhibit this behavior. They both ask the database system to project a column in the orders table only if the predicate on the nation table is true.
Actual Behavior The first query takes ~8.3 seconds on v20.1.3, while the second query only takes ~44 milliseconds. For the first query, the database system scans the large orders table (7500000 rows), even when the predicate on the nation table can be evaluated within a few milliseconds. (only 25 rows). Its "EXPLAIN ANALYZE" result is here. For the second query, the database system evaluates the predicate on the nation table first, which can avoid the unnecessary scan of the orders table if the predicate turns out to be false. Its "EXPLAIN ANALYZE" result is here.
Expected Behavior I would have expected the database system run these two queries with similar execution time, given that they both have the same semantics. To this end, the optimizer should generate a better execution plan for the first query, such as the plan generated for the second query. Here are the steps for reproducing our observations:
Test environment
Reproduce Bug
Download TPC-H (scale factor of 5) and extract it Download the dataset from the link: https://drive.google.com/file/d/1ZHldLW2iPfpsRh6lL-slcUoKazfai4P7/view?usp=sharing
Create DB and TPC-H schema
$ cockroach sql --insecure --host=localhost:26257
In CockroachDB's built-in SQL client to create database:
Create TPC-H Schema
$ cockroach sql --insecure --host=localhost:26257 -d tpch5 < dss.ddl
Import benchmark
$ ./setup.sh
Execute the queries
$ cockroach sql --insecure --host=localhost:26257 -d tpch5 < query.sql
Jira issue: CRDB-4007
Epic CRDB-1491