Skip to content

Override query_conditions_for_initial_load#182

Merged
rafiss merged 1 commit intocockroachdb:masterfrom
keithdoggett:fix/initial-load-query
Mar 5, 2021
Merged

Override query_conditions_for_initial_load#182
rafiss merged 1 commit intocockroachdb:masterfrom
keithdoggett:fix/initial-load-query

Conversation

@keithdoggett
Copy link
Copy Markdown
Contributor

@keithdoggett keithdoggett commented Mar 5, 2021

Overrides the TypeMapInitializer#query_conditions_for_initial_load method to use a faster query that doesn't rely on 'array_in(cstring,oid,integer)'::regprocedure.

That part of the condition can be replaced with

t.typarray = 0 AND t.typcategory='A'

This can be verified by comparing the following queries:

SELECT t.oid, t.typinput, t.typarray, t.typcategory FROM
pg_type as t WHERE t.typinput='array_in(cstring,oid,integer)'::regprocedure
ORDER BY t.oid;
SELECT t.oid, t.typinput, t.typarray, t.typcategory FROM
pg_type as t WHERE t.typarray=0 AND t.typcategory='A' ORDER BY t.oid;

the second query is about 10x faster for me.

A potential issue is that these do not return the same results in Postgres (a few records are missing from the second) so the queries could eventually diverge depending on updates to Cockroach.

This is related to cockroachdb/cockroach#61082.

@rafiss
Copy link
Copy Markdown
Contributor

rafiss commented Mar 5, 2021

Excellent! This looks good, will just wait for the tests to pass.

I checked on Postgres, and it looks like the original query only returns one extra row compared to the new query:

rafiss@127:postgres> select d2.* from d2 left join d1 on d2.oid = d1.oid where d1.oid is null;
+-------+-----------+------------+------------+---------------+
| oid   | typname   | typinput   | typarray   | typcategory   |
|-------+-----------+------------+------------+---------------|
| 2287  | _record   | array_in   | 0          | P             |
+-------+-----------+------------+------------+---------------+

This is the "tuple array" type, and it looks like it has a typcategory of P (pseudotype) instead of A (array) in PostgreSQL. I don't think this detail matters that much.

@rafiss rafiss self-requested a review March 5, 2021 18:36
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Labels

None yet

Projects

None yet

Development

Successfully merging this pull request may close these issues.

2 participants