BigQuery dry_run estimations significantly off when clustering is applied [176795805]

Assigned

Feature Request

Status Update

No update yet.

Description

se...@google.com

created issue #1

Jan 5, 2021 12:59PM

As per[1]:

In a clustered table, BigQuery automatically sorts the data based on the values in the clustering columns and organizes them in optimally sized storage blocks. You can achieve more finely grained sorting by creating a table that is clustered and partitioned. A clustered table maintains the sort properties in the context of each operation that modifies it. As a result, BigQuery might not be able to accurately estimate the bytes processed by the query or the query costs.

However, in some cases the estimated bytes shown is the size of the full table scan and does not take into account the partition size.

Therefore, assuming this is provided as best effort basis, there still might be some room for improvement on Bigquery dry_run calculations.

[1] https://cloud.google.com/bigquery/docs/clustered-tables#clustering_partitioned_tables

Comments

se...@google.com <se...@google.com> #2Jan 5, 2021 01:01PM

[Deleted User] <[Deleted User]> #3Jan 5, 2021 01:29PM

je...@google.com <je...@google.com> Jan 19, 2021 06:46PM

Reassigned to mi...@google.com.

mi...@google.com <mi...@google.com> #4Apr 28, 2021 09:50PM

Reassigned to ja...@google.com.

Not sure who owns Dry Run estimation - Jagan pls reassign if it's not you. Thanks!

za...@sadan.me <za...@sadan.me> #5Oct 25, 2022 08:18PM

iv...@gmail.com <iv...@gmail.com> #6Oct 26, 2022 08:28AM

ta...@plaid.co.jp <ta...@plaid.co.jp> #7Oct 31, 2022 04:00PM

ba...@httparchive.org <ba...@httparchive.org> #8May 1, 2023 05:26PM

I've noticed one weird thing. With an admin/table owner account I get a more accurate estimate. While any pother account get's the inaccurate one.

For example the following gives me an estimate of 42.29 GB when running as a table owner account:

SELECT DISTINCT
  date,
  client,
  url
FROM
  `httparchive.all.requests`,
  UNNEST (response_headers) as resp_headers
WHERE
  date = "2023-04-01" AND
  LOWER(resp_headers.name) = 'vary' AND
  LOWER(resp_headers.value) LIKE '%x-wf-forwarded-proto%' AND
  is_main_document

(the table is partitioned on date and clustered on is_main_document)

Running as a general account with no particular permissions on this project estimates 3.08 TB. Despite the fact that this account can also see the clustering details.

In both cases 42.29 GB is what is actually used when the run is completed.

Why is the estimate different, and is there any extra permissions we can give to provide access to the more accurate estimate?

ak...@tutelatechnologies.com <ak...@tutelatechnologies.com> #9Jul 13, 2023 07:18PM

of note, today we noticed that turning BI Engine on for a project gives a better estimate.
Can this be confirmed?

pu...@google.com <pu...@google.com> Oct 13, 2023 12:35PM

Reassigned to gc...@google.com.

bv...@gmail.com <bv...@gmail.com> #10May 22, 2024 06:50PM

This one also gives an estimate of 8Tb while actually it costs 420Mb:

WITH t1 AS (
  SELECT ANY_VALUE("") AS col1
  FROM `httparchive.all.pages`
  WHERE date = "2024-04-01" -- partitioning
), t2 AS (
  SELECT ANY_VALUE(custom_metrics) --heavy column
  FROM `httparchive.all.pages`
  WHERE date = "2024-04-01" -- partitioning
    AND rank = 1000 -- clustering
)
SELECT *
FROM t1
JOIN t2 ON TRUE

Estimation for both of the CTEs (separately) is done correctly.

Doing UNION ALL also gives the same wrong estimation:

SELECT ANY_VALUE("")
  FROM `httparchive.all.pages`
  WHERE date = "2024-04-01" -- partitioning
UNION ALL
  SELECT ANY_VALUE(custom_metrics) -- heavy column
  FROM `httparchive.all.pages`
  WHERE date = "2024-04-01" -- partitioning
    AND rank = 1000 -- clustering