search

cockroachdb / docs

CockroachDB user documentation
https://cockroachlabs.com/docs
Creative Commons Attribution 4.0 International
188 stars 456 forks source link

Define Table Partitions Doc Update - filtering on indexed column #9205

Open andreimatei opened 3 years ago

andreimatei commented 3 years ago

Andrei Matei commented:

Re: Define Table Partitions

The filtering on an indexed column is very unclear. It starts with

If you filter the query of a partitioned table on a column in the index directly following the partition prefix, the cost-based optimizer creates a query plan that scans each partition in parallel, rather than performing a costly sequential scan of the entire table.

This claims that partitions are scanned in parallel, but the EXPLAIN listings below do not confirm that. In the first listing, the scan doesn't have the attribute parallel in there. The second one does, but that comes after adding an extra constraint. It's hard to repeat exactly the steps in the documentation and expect the same results because things have gotten more complex now with statistics collection, but I believe that, indeed, you're not going to get a parallel scan of the partitions without an extra constraint on the partitioning column because, technically, the default partition could return an unbounded number of rows - which inhibits the parallelization in question.

Then

If you know the set of all possible partitioned values, adding a check constraint to the table's create statement can also improve performance.

This paragraph would really benefit from more words. The word "also" seems weird, because it's not clear what it refers to and whether the suggestion is a replacement for any alternative (it is not, so an "in addition" would be better). I think here we need to explain something about the default partition and its effects on the parallelization.

cc @rytaft

Jira Issue: DOC-872

github-actions[bot] commented 1 year ago

We have marked this issue as stale because it has been inactive for 18 months. If this issue is still relevant, removing the stale label or adding a comment will keep it active. Otherwise, we'll close it in 10 days to keep the issue queue tidy. Thank you for your contribution to CockroachDB docs!

rytaft commented 1 year ago

I believe this is still relevant.

exalate-issue-sync[bot] commented 1 year ago

Richard Loveland (rmloveland) commented: hey Ryan Kuo ! Assigning this over to you since you are the SQL Queries docs person now per https://cockroachlabs.atlassian.net/wiki/x/RQG-dg