No support for schema names

[jfinzel]

Diving into why dexter was not working, there appear to be vast assumptions in the code about no differing schema names. This simply won't work and is why I am getting in my log files: "No candidate tables for indexes". Queries and tables have to be fingerprinted taking into account search_path and allowing for differing schema names. Not only do most of our queries use qualified schema names, but it is possible to have multiple schemas with the same table name in each schema in a single database.

[ankane]

Hey @jfinzel, I'll revisit schema support in the next week or so. Do you have an example that isn't working?

[jfinzel]

Yes - the logs simply show "no candidate tables found". If you see https://github.com/ankane/dexter/issues/14 this should give you what you want.

Here's a contrived example:

CREATE SCHEMA foo;
CREATE SCHEMA bar;

CREATE TABLE foo.foo(id int);
CREATE TABLE bar.foo(id int);
INSERT INTO foo.foo
SELECT * FROM generate_series(1,1000000);
INSERT INTO bar.foo
SELECT * FROM generate_series(1,1000000);

ANALYZE foo.foo;
ANALYZE bar.foo;
SELECT * FROM foo.foo WHERE id = 10000;
SELECT * FROM bar.foo WHERE id = 10000;

Here is dexter output:

Query 018d2cda093bb1c21952cddc71932414ddae8f2d38
Total time: 0.0 min, avg time: 0 ms, calls: 1
No candidate tables for indexes

EXPLAIN SELECT * FROM bar.foo WHERE id = 10000;

Processing 1 new query fingerprints
SQL: SELECT hypopg_reset()
SQL: SELECT table_name FROM information_schema.tables WHERE table_catalog = current_database() AND table_schema NOT IN ('pg_catalog', 'information_schema') AND table_type = 'BASE TABLE'
No new indexes found
--------------------------------------------------------------------------------
Query 014396fb589099ea5e09d44c10c4224adbfaaebca3
Total time: 0.0 min, avg time: 43 ms, calls: 1
No candidate tables for indexes

EXPLAIN ANALYZE SELECT * FROM bar.foo WHERE id = 10000;

Processing 0 new query fingerprints
Processing 0 new query fingerprints

[ankane]

Awesome, thanks!

[ankane]

Added schema support on master.