sql: support the initial correlated subquery issued by Symfony/Doctrine
Closed this issue · 5 comments
This specific item is extracted from #3288 (comment) and #32098 (comment)
SELECT
a.attnum,
quote_ident(a.attname) AS field,
t.typname AS type,
format_type(a.atttypid, a.atttypmod) AS complete_type,
(SELECT t1.typname FROM pg_catalog.pg_type t1 WHERE t1.oid = t.typbasetype) AS domain_type,
(SELECT format_type(t2.typbasetype, t2.typtypmod) FROM
pg_catalog.pg_type t2 WHERE t2.typtype = 'd' AND t2.oid = a.atttypid) AS domain_complete_type,
a.attnotnull AS isnotnull,
(SELECT 't'
FROM pg_index
WHERE c.oid = pg_index.indrelid
AND pg_index.indkey[0] = a.attnum
AND pg_index.indisprimary = 't'
) AS pri,
(SELECT pg_get_expr(adbin, adrelid)
FROM pg_attrdef
WHERE c.oid = pg_attrdef.adrelid
AND pg_attrdef.adnum=a.attnum
) AS default,
(SELECT pg_description.description
FROM pg_description WHERE pg_description.objoid = c.oid AND a.attnum = pg_description.objsubid
) AS comment
FROM pg_attribute a, pg_class c, pg_type t, pg_namespace n
WHERE n.nspname NOT IN ('pg_catalog', 'information_schema', 'pg_toast') AND c.relname = 'backward_dependencies' AND n.nspname = 'crdb_internal'
AND a.attnum > 0
AND a.attrelid = c.oid
AND a.atttypid = t.oid
AND n.oid = c.relnamespace
ORDER BY a.attnum@andy-kimball has responded on this:
- Use of a subscript expression
[0]; this is not currently supported by the cost-based optimizer and so triggers fallback to the heuristic planner.- Absence of unique index assertions on the
pg_catalogtables, so the optimizer cannot statically prove that correlated subqueries return <= 1 result.
Item 1 has been addressed in the latest CockroachDB code, but the second point not yet.
@andy-kimball would it help to populate suitable PK index descriptors in the pg_catalog tables? I think this can be done at a low cost as long as the CBO can be told to not issue point lookups on them (i.e. keep full table scan + filter in the plan for execution).
Yes, that'd definitely be possible if we had the index descriptors. We could then insert a bit of code to not consider indexes for vtables.
+@RaduBerinde for visibility
Minor nitpicking: The PHP framework is spelled 'Symfony', not 'Symphony'. Only mentioning it so people specifically searching for that term may be better able to find this issue.
thanks!
This query appears to work in the latest beta.
SELECT VERSION(); version
----------------------------------------------------------------------------------------------------
CockroachDB CCL v19.1.0-beta.20190318 (x86_64-apple-darwin14, built 2019/03/12 22:10:57, go1.11.5)
(1 row)
SELECT
a.attnum,
quote_ident(a.attname) AS field,
t.typname AS type,
format_type(a.atttypid, a.atttypmod) AS complete_type,
(SELECT t1.typname FROM pg_catalog.pg_type t1 WHERE t1.oid = t.typbasetype) AS domain_type,
(SELECT format_type(t2.typbasetype, t2.typtypmod) FROM
pg_catalog.pg_type t2 WHERE t2.typtype = 'd' AND t2.oid = a.atttypid) AS domain_complete_type,
a.attnotnull AS isnotnull,
(SELECT 't'
FROM pg_index
WHERE c.oid = pg_index.indrelid
AND pg_index.indkey[0] = a.attnum
AND pg_index.indisprimary = 't'
) AS pri,
(SELECT pg_get_expr(adbin, adrelid)
FROM pg_attrdef
WHERE c.oid = pg_attrdef.adrelid
AND pg_attrdef.adnum=a.attnum
) AS default,
(SELECT pg_description.description
FROM pg_description WHERE pg_description.objoid = c.oid AND a.attnum = pg_description.objsubid
) AS comment
FROM pg_attribute a, pg_class c, pg_type t, pg_namespace n
WHERE n.nspname NOT IN ('pg_catalog', 'information_schema', 'pg_toast') AND c.relname = 'backward_dependencies' AND n.nspname = 'crdb_internal'
AND a.attnum > 0
AND a.attrelid = c.oid
AND a.atttypid = t.oid
AND n.oid = c.relnamespace
ORDER BY a.attnum; attnum | field | type | complete_type | domain_type | domain_complete_type | isnotnull | pri | default | comment
--------+--------------------+------+---------------+-------------+----------------------+-----------+-----+---------+---------
1 | descriptor_id | int8 | bigint | | | f | | |
2 | descriptor_name | text | text | | | t | | |
3 | index_id | int8 | bigint | | | f | | |
4 | column_id | int8 | bigint | | | f | | |
5 | dependson_id | int8 | bigint | | | t | | |
6 | dependson_type | text | text | | | t | | |
7 | dependson_index_id | int8 | bigint | | | f | | |
8 | dependson_name | text | text | | | f | | |
9 | dependson_details | text | text | | | f | | |
(9 rows)
Closing this item as it works with 19.1