github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/exec/execbuilder/testdata/virtual (about) 1 # LogicTest: !3node-tenant 2 query TTT 3 EXPLAIN SELECT * FROM pg_catalog.pg_class WHERE oid = 50 4 ---- 5 · distributed false 6 · vectorized false 7 virtual table · · 8 · source pg_class@pg_class_oid_idx 9 · constraint /2: [/50 - /50] 10 11 query TTT 12 EXPLAIN SELECT * FROM pg_catalog.pg_class WHERE relname = 'blah' 13 ---- 14 · distributed false 15 · vectorized false 16 filter · · 17 │ filter relname = 'blah' 18 └── virtual table · · 19 · source pg_class@primary 20 21 22 # We can push the filter into information_schema.tables, which has an index 23 # on the table_name field. 24 query TTT 25 EXPLAIN SELECT * FROM information_schema.tables WHERE table_name = 'blah' 26 ---- 27 · distributed false 28 · vectorized false 29 virtual table · · 30 · source tables@tables_table_name_idx 31 · constraint /4: [/'blah' - /'blah'] 32 33 # Make sure that if we need an ordering on one of the virtual indexes we 34 # provide it using a sortNode even though the optimizer expects the virtual 35 # index to provide it "naturally". 36 query TTT 37 EXPLAIN SELECT * FROM information_schema.tables WHERE table_name > 'blah' ORDER BY table_name 38 ---- 39 · distributed false 40 · vectorized false 41 sort · · 42 │ order +table_name 43 └── virtual table · · 44 · source tables@tables_table_name_idx 45 · constraint /4: [/e'blah\x00' - ] 46 47 # Make sure that we properly push down just part of a filter on two columns 48 # where only one of them is satisfied by the virtual index. 49 query TTT 50 EXPLAIN SELECT * FROM information_schema.tables WHERE table_name = 'blah' AND table_type = 'foo' 51 ---- 52 · distributed false 53 · vectorized false 54 filter · · 55 │ filter table_type = 'foo' 56 └── virtual table · · 57 · source tables@tables_table_name_idx 58 · constraint /4: [/'blah' - /'blah'] 59 60 # Lookup joins into virtual indexes. 61 62 query TTT 63 EXPLAIN SELECT * FROM pg_constraint INNER LOOKUP JOIN pg_class on conrelid=pg_class.oid 64 ---- 65 · distributed false 66 · vectorized false 67 virtual-table-lookup-join · · 68 │ table pg_class@pg_class_oid_idx 69 │ type inner 70 │ equality (conrelid) = (oid) 71 └── virtual table · · 72 · source pg_constraint@primary 73 74 query TTT 75 EXPLAIN SELECT * FROM pg_constraint LEFT LOOKUP JOIN pg_class on conrelid=pg_class.oid 76 ---- 77 · distributed false 78 · vectorized false 79 virtual-table-lookup-join · · 80 │ table pg_class@pg_class_oid_idx 81 │ type left outer 82 │ equality (conrelid) = (oid) 83 └── virtual table · · 84 · source pg_constraint@primary 85 86 # Can't lookup into a vtable with no index. 87 query error could not produce 88 EXPLAIN SELECT * FROM pg_constraint INNER LOOKUP JOIN pg_index on true 89 90 # Test that a gnarly ORM query from ActiveRecord uses lookup joins for speed. 91 92 query TTT 93 EXPLAIN SELECT 94 t2.oid::REGCLASS AS to_table, 95 a1.attname AS column, 96 a2.attname AS primary_key, 97 c.conname AS name, 98 c.confupdtype AS on_update, 99 c.confdeltype AS on_delete, 100 c.convalidated AS valid 101 FROM 102 pg_constraint AS c 103 JOIN pg_class AS t1 ON c.conrelid = t1.oid 104 JOIN pg_class AS t2 ON c.confrelid = t2.oid 105 JOIN pg_attribute AS a1 ON 106 (a1.attnum = c.conkey[1]) AND (a1.attrelid = t1.oid) 107 JOIN pg_attribute AS a2 ON 108 (a2.attnum = c.confkey[1]) AND (a2.attrelid = t2.oid) 109 JOIN pg_namespace AS t3 ON c.connamespace = t3.oid 110 WHERE 111 ((c.contype = 'f') AND (t1.oid = 'b'::regclass)) 112 AND (t3.nspname = ANY (current_schemas(false))) 113 ORDER BY 114 c.conname 115 ---- 116 · distributed false 117 · vectorized false 118 render · · 119 └── sort · · 120 │ order +conname 121 └── render · · 122 └── hash-join · · 123 │ type inner 124 │ equality (oid) = (connamespace) 125 ├── filter · · 126 │ │ filter nspname = ANY ARRAY['public'] 127 │ └── render · · 128 │ └── virtual table · · 129 │ source pg_namespace@primary 130 └── virtual-table-lookup-join · · 131 │ table pg_attribute@pg_attribute_attrelid_idx 132 │ type inner 133 │ equality (oid) = (attrelid) 134 │ pred column131 = attnum 135 └── render · · 136 └── virtual-table-lookup-join · · 137 │ table pg_attribute@pg_attribute_attrelid_idx 138 │ type inner 139 │ equality (oid) = (attrelid) 140 │ pred (column108 = attnum) AND (attrelid = 'b'::REGCLASS) 141 └── render · · 142 └── virtual-table-lookup-join · · 143 │ table pg_class@pg_class_oid_idx 144 │ type inner 145 │ equality (conrelid) = (oid) 146 │ pred oid = 'b'::REGCLASS 147 └── virtual-table-lookup-join · · 148 │ table pg_class@pg_class_oid_idx 149 │ type inner 150 │ equality (confrelid) = (oid) 151 └── filter · · 152 │ filter (conrelid = 'b'::REGCLASS) AND (contype = 'f') 153 └── render · · 154 └── virtual table · · 155 · source pg_constraint@primary