sql: stats being different leads to different plans when using a placeholder

[cucaroach]

This originated from https://github.com/cockroachlabs/support/issues/2179. Basically we a get a RowCount of 1 w/o placeholders and a RowCount of 1/3 the columns distinct count w/ a placeholder. Here's a really straightforward distilled repro courtesy @DrewKimball :

exec-ddl
CREATE TABLE xy (x INT, y INT, INDEX (x) STORING (y));
----
exec-ddl
ALTER TABLE xy INJECT STATISTICS '[
  {
    "columns": ["x"],
    "created_at": "2018-01-01 1:00:00.00000+00:00",
    "row_count": 100000,
    "distinct_count": 100000
  },
  {
    "columns": ["y"],
    "created_at": "2018-01-01 1:00:00.00000+00:00",
    "row_count": 100000,
    "distinct_count": 2
  }
]';
----
opt format=show-all
WITH RECURSIVE cte(a,b) AS (
  (SELECT * FROM xy WHERE x = 1)
  UNION ALL
  (SELECT * FROM cte WHERE False)
)
SELECT * FROM cte;
----

If you replace 1 with $1 the problem becomes apparent. Doesn't matter if you use this simple approach or assign-placeholders-opt query-args=(1) format=show-all.

I've narrowed it down to (sb *statisticsBuilder) applyConstraintSet but haven't completed the investigation. In the customer issue the result is a hash join vs a lookup join which is believed to be causing poor performance.

Jira issue: CRDB-25906

[DrewKimball]

I don't think the statistics builder is necessarily doing anything wrong itself, but we're not updating the properties that are used by the statistics builder after placeholder assignment happens. The property calculation takes place here when the original query plan is built, and we don't update it during the re-optimization that happens after placeholder assignment.

[cucaroach]

But it doesn't make sense to me that if the distinct_count and row_count are both N we have a selectivity of 1 on a equality filter but if we know the value the selectivity drops to .0001. We shouldn't need a particular value in order to calculate that the selectivity is much smaller than 1 right? Or am I missing something?

[DrewKimball]

But it doesn't make sense to me that if the distinct_count and row_count are both N we have a selectivity of 1 on a equality filter but if we know the value the selectivity drops to .0001. We shouldn't need a particular value in order to calculate that the selectivity is much smaller than 1 right? Or am I missing something?

You make a good point that we should be able to see that that filter should produce at most one row, but for now I think we don't estimate for a filter with a placeholder at all, and instead use the "unknown" 1/3 selectivity.

[cucaroach]

You make a good point that we should be able to see that that filter should produce at most one row, but for now I think we don't estimate for a filter with a placeholder at all, and instead use the "unknown" 1/3 selectivity.

Yeah I'm just not sure it matters, like do we need good estimates for filters with a placeholder if assign placeholders process will revisit everything? I guess the real question is whether there are any other bits of work we do at build time that should be redone at assign-placeholders time that isn't?

[mgartner]

The output of the tests in the description:

exec-ddl
CREATE TABLE xy (x INT, y INT, INDEX (x) STORING (y));
----

opt format=show-all
WITH RECURSIVE cte(a,b) AS (
  (SELECT * FROM xy WHERE x = 1)
  UNION ALL
  (SELECT * FROM cte WHERE False)
)
SELECT * FROM cte;
----
project
 ├── columns: a:10(int) b:11(int)
 ├── stats: [rows=10]
 ├── cost: 28.8900001
 ├── prune: (10,11)
 ├── recursive-c-t-e
 │    ├── columns: a:6(int) b:7(int)
 │    ├── working table binding: &1
 │    ├── initial columns: t.public.xy.x:1(int) t.public.xy.y:2(int)
 │    ├── recursive columns: a:8(int) b:9(int)
 │    ├── stats: [rows=10]
 │    ├── cost: 28.5700001
 │    ├── fake-rel
 │    │    ├── columns: a:6(int) b:7(int)
 │    │    ├── cardinality: [1 - ]
 │    │    ├── stats: [rows=10]
 │    │    └── cost: 0.02
 │    ├── scan t.public.xy@xy_x_idx
 │    │    ├── columns: t.public.xy.x:1(int!null) t.public.xy.y:2(int)
 │    │    ├── constraint: /1/3: [/1 - /1]
 │    │    ├── stats: [rows=10, distinct(1)=1, null(1)=0]
 │    │    ├── cost: 28.5200001
 │    │    ├── fd: ()-->(1)
 │    │    └── prune: (2)
 │    └── values
 │         ├── columns: a:8(int!null) b:9(int!null)
 │         ├── cardinality: [0 - 0]
 │         ├── stats: [rows=0]
 │         ├── cost: 0.01
 │         ├── key: ()
 │         ├── fd: ()-->(8,9)
 │         └── prune: (8,9)
 └── projections
      ├── variable: a:6 [as=a:10, type=int, outer=(6)]
      └── variable: b:7 [as=b:11, type=int, outer=(7)]

assign-placeholders-opt query-args=(1) format=show-all
WITH RECURSIVE cte(a,b) AS (
  (SELECT * FROM xy WHERE x = $1)
  UNION ALL
  (SELECT * FROM cte WHERE False)
)
SELECT * FROM cte;
----
project
 ├── columns: a:10(int) b:11(int)
 ├── stats: [rows=10]
 ├── cost: 28.8900001
 ├── prune: (10,11)
 ├── recursive-c-t-e
 │    ├── columns: a:6(int) b:7(int)
 │    ├── working table binding: &1
 │    ├── initial columns: t.public.xy.x:1(int) t.public.xy.y:2(int)
 │    ├── recursive columns: a:8(int) b:9(int)
 │    ├── stats: [rows=10]
 │    ├── cost: 28.5700001
 │    ├── fake-rel
 │    │    ├── columns: a:6(int) b:7(int)
 │    │    ├── cardinality: [1 - ]
 │    │    ├── stats: [rows=330]
 │    │    └── cost: 0.02
 │    ├── scan t.public.xy@xy_x_idx
 │    │    ├── columns: t.public.xy.x:1(int!null) t.public.xy.y:2(int)
 │    │    ├── constraint: /1/3: [/1 - /1]
 │    │    ├── stats: [rows=10, distinct(1)=1, null(1)=0]
 │    │    ├── cost: 28.5200001
 │    │    ├── fd: ()-->(1)
 │    │    └── prune: (2)
 │    └── values
 │         ├── columns: a:8(int!null) b:9(int!null)
 │         ├── cardinality: [0 - 0]
 │         ├── stats: [rows=0]
 │         ├── cost: 0.01
 │         ├── key: ()
 │         ├── fd: ()-->(8,9)
 │         └── prune: (8,9)
 └── projections
      ├── variable: a:6 [as=a:10, type=int, outer=(6)]
      └── variable: b:7 [as=b:11, type=int, outer=(7)]

@cucaroach in the real-world case where you saw this problem, was the bad row count affecting the query plan that the optimizer chose?

[cucaroach]

@cucaroach in the real-world case where you saw this problem, was the bad row count affecting the query plan that the optimizer chose?

Yes it was causing us to use a hash join instead of a lookup join.