Obvious cases don't work

Question

Obvious cases don't work

jfinzel opened this issue 7 years ago · 8 comments

I have gotten dexter to work for me, but in throwing some obvious cases at it, it's not giving me new indexes. I have a snapshot of production I am running in which I have dropped every index except primary keys. I am trying to see if dexter can build me only the indexes I need.

I am running:
PostgreSQL 9.6.6 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.4) 5.4.0 20160609, 64-bit
And hypopg version 1.1.0.
And dexter Ubuntu Xenial package version 0.2.1-1506008296.7423139

Here is my example:
I have a table with 453236 records. It has a field updated_at which spans several years. I only have an index on the primary key, which is a serial column. Here is my query:

SELECT * FROM table WHERE updated_at >= current_date - 1;

Here is my dexter invocation:

tail -F <logfile> | dexter -d foo -p 0000 --min-time 0

But dexter simply says:

Processing 1 new query fingerprints
No new indexes found

Here is the query plan with a full scan (warm cache):

                                                       QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..14730.76 rows=39 width=151) (actual time=72.497..74.809 rows=1317 loops=1)
   Workers Planned: 3
   Workers Launched: 3
   ->  Parallel Seq Scan on foo  (cost=0.00..13726.86 rows=13 width=151) (actual time=68.802..70.700 rows=329 loops=4)
         Filter: (updated_at >= (('now'::cstring)::date - 1))
         Rows Removed by Filter: 112980
 Planning time: 0.608 ms
 Execution time: 79.123 ms
(8 rows)

And if I manually create the index, obviously much faster:

                                                             QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on foo  (cost=27.08..3883.25 rows=1374 width=151) (actual time=0.179..0.511 rows=1317 loops=1)
   Recheck Cond: (updated_at >= (('now'::cstring)::date - 1))
   Heap Blocks: exact=151
   ->  Bitmap Index Scan on foo_updated_at_idx  (cost=0.00..26.73 rows=1374 width=0) (actual time=0.160..0.160 rows=1317 loops=1)
         Index Cond: (updated_at >= (('now'::cstring)::date - 1))
 Planning time: 0.365 ms
 Execution time: 0.592 ms
(7 rows)

Answer 1 · 2017-12-21T17:01:45.000Z

Hey @jfinzel, can you run with --log-level debug2 and share the output?

Answer 2 · 2017-12-21T17:30:23.000Z

Thanks for all your responses - I'm glad to see willingness to improve!

This is related to the schema_name issue. However, I tried to workaround it using search_path with no success. Here is the example (I am masking my names):

tail -F foo-2017-12-21_080000.log | dexter -d my_db -p 60000 --min-time 0 --log-level debug2 --log-sql --interval 5 | tee /tmp/dexter.log

Here is my 2 SQL statements (I made the schema for this table in search_path):

SHOW search_path;
               search_path
-----------------------------------------
 "$user", public, foo_schema
(1 row)

SELECT * FROM foo_schema.foo_table WHERE customer_id = 3913437;

SELECT * FROM foo_table WHERE customer_id = 3913437;

Here is Dexter output:

SQL: SELECT hypopg_reset()
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         134,1         Top
SQL: SELECT * FROM pg_available_extensions WHERE name = 'hypopg' AND installed_version IS NOT NULL
SQL: SET lock_timeout = '5s'
Started
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 01935edfa9c5e040fffddecfd955563351d55fc649
Total time: 0.0 min, avg time: 1 ms, calls: 1
No candidate tables for indexes

SELECT * FROM pg_available_extensions WHERE name = 'hypopg' AND installed_version IS NOT NULL

Processing 2 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 017694f81fc17a972a9ed4ac824d4914bcb121dbbc
Total time: 0.0 min, avg time: 0 ms, calls: 1
No candidate tables for indexes

SET lock_timeout = '5s'

--------------------------------------------------------------------------------
Query 019650634ebfcd43f242c7f8c0834df9f0ab2ef39c
Total time: 0.0 min, avg time: 0 ms, calls: 1
No candidate tables for indexes

SELECT hypopg_reset()

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 01df0e7076d117e49a0576c11e3983373e8371c92f
Total time: 0.0 min, avg time: 6 ms, calls: 2
No candidate tables for indexes

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'

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 01c08a83631a0c4306ba316bbf4b05706d5c3498cd
Total time: 0.0 min, avg time: 1 ms, calls: 1
No candidate tables for indexes

SELECT pg_catalog.quote_ident(datname) FROM pg_catalog.pg_database  WHERE substring(pg_catalog.quote_ident(datname),1,3)='acc'  LIMIT 1000

Processing 0 new query fingerprints
Processing 0 new query fingerprints
Processing 0 new query fingerprints
Processing 0 new query fingerprints
Processing 0 new query fingerprints
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 012ca94a1b63a807a498285fd718f4ffc1e7f2c9a0
Total time: 0.0 min, avg time: 1513 ms, calls: 1
No candidate tables for indexes

SELECT * FROM foo_schema.foo_table WHERE customer_id = 3913437;

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'
SQL: SELECT schemaname AS schema, relname AS table, last_analyze, last_autoanalyze FROM pg_stat_user_tables WHERE relname IN ('foo_table')
Running analyze: ANALYZE "foo_table"
SQL: ANALYZE "foo_table"
SQL: EXPLAIN (FORMAT JSON) SELECT * FROM foo_table WHERE customer_id = 3913437
SQL: SELECT table_name, column_name, data_type FROM information_schema.columns WHERE table_schema = 'public' AND table_name IN ('foo_table') ORDER BY 1, 2
SQL: EXPLAIN (FORMAT JSON) SELECT * FROM foo_table WHERE customer_id = 3913437
SQL: EXPLAIN (FORMAT JSON) SELECT * FROM foo_table WHERE customer_id = 3913437
SQL: SELECT schemaname AS schema, t.relname AS table, ix.relname AS name, regexp_replace(pg_get_indexdef(i.indexrelid), '^[^\(]*\((.*)\)$', '\1') AS columns, regexp_replace(pg_get_indexdef(i.indexrelid), '.* USING ([^ ]*) \(.*', '\1') AS using FROM pg_index i INNER JOIN pg_class t ON t.oid = i.indrelid INNER JOIN pg_class ix ON ix.oid = i.indexrelid LEFT JOIN pg_stat_user_indexes ui ON ui.indexrelid = i.indexrelid WHERE t.relname IN ('foo_table') AND schemaname IS NOT NULL AND indisvalid = 't' AND indexprs IS NULL AND indpred IS NULL ORDER BY 1, 2
No new indexes found
--------------------------------------------------------------------------------
Query 01aeb7d25d5d4d0d40ecaba7d1fd80054f53f0ff45
Total time: 0.0 min, avg time: 1546 ms, calls: 1
Start: 943461.81
Pass1: 943461.81 : None
Pass2: 943461.81 : None
Final: 943461.81 : None

SELECT * FROM foo_table WHERE customer_id = 3913437;

Processing 5 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'
SQL: SELECT schemaname AS schema, relname AS table, last_analyze, last_autoanalyze FROM pg_stat_user_tables WHERE relname IN ('foo_table')
SQL: EXPLAIN (FORMAT JSON) EXPLAIN (FORMAT JSON) SELECT * FROM foo_table WHERE customer_id = 3913437
SQL: EXPLAIN (FORMAT JSON) ANALYZE "foo_table"
SQL: SELECT schemaname AS schema, t.relname AS table, ix.relname AS name, regexp_replace(pg_get_indexdef(i.indexrelid), '^[^\(]*\((.*)\)$', '\1') AS columns, regexp_replace(pg_get_indexdef(i.indexrelid), '.* USING ([^ ]*) \(.*', '\1') AS using FROM pg_index i INNER JOIN pg_class t ON t.oid = i.indrelid INNER JOIN pg_class ix ON ix.oid = i.indexrelid LEFT JOIN pg_stat_user_indexes ui ON ui.indexrelid = i.indexrelid WHERE t.relname IN ('foo_table') AND schemaname IS NOT NULL AND indisvalid = 't' AND indexprs IS NULL AND indpred IS NULL ORDER BY 1, 2
No new indexes found
--------------------------------------------------------------------------------
Query 013ce1dc4db78af651cb1584fe349cb3b2a76642f0
Total time: 0.0 min, avg time: 0 ms, calls: 3
Could not run explain

EXPLAIN (FORMAT JSON) SELECT * FROM foo_table WHERE customer_id = 3913437

--------------------------------------------------------------------------------
Query 016ec46c3a2d89603a3372884726d3e7ad9972de78
Total time: 0.0 min, avg time: 0 ms, calls: 1
Tables not present in current database

SELECT schemaname AS schema, t.relname AS table, ix.relname AS name, regexp_replace(pg_get_indexdef(i.indexrelid), '^[^\(]*\((.*)\)$', '\1') AS columns, regexp_replace(pg_get_indexdef(i.indexrelid), '.* USING ([^ ]*) \(.*', '\1') AS using FROM pg_index i INNER JOIN pg_class t ON t.oid = i.indrelid INNER JOIN pg_class ix ON ix.oid = i.indexrelid LEFT JOIN pg_stat_user_indexes ui ON ui.indexrelid = i.indexrelid WHERE t.relname IN ('foo_table') AND schemaname IS NOT NULL AND indisvalid = 't' AND indexprs IS NULL AND indpred IS NULL ORDER BY 1, 2

--------------------------------------------------------------------------------
Query 018f71bfdc5277a2f46491fed0004d46abd13ec6a8
Total time: 0.0 min, avg time: 11 ms, calls: 1
Tables not present in current database

SELECT schemaname AS schema, relname AS table, last_analyze, last_autoanalyze FROM pg_stat_user_tables WHERE relname IN ('foo_table')

--------------------------------------------------------------------------------
Query 0198c564148f82d61db2b4328b18949cbcf6e7dffc
Total time: 0.0 min, avg time: 2 ms, calls: 1
Tables not present in current database

SELECT table_name, column_name, data_type FROM information_schema.columns WHERE table_schema = 'public' AND table_name IN ('foo_table') ORDER BY 1, 2

--------------------------------------------------------------------------------
Query 019d656ba1db4a5104a5ef56261cc2e2341defad87
Total time: 0.2 min, avg time: 10745 ms, calls: 1
Could not run explain

ANALYZE "foo_table"

Processing 0 new query fingerprints
Processing 0 new query fingerprints
Processing 0 new query fingerprints

Answer 3 · 2017-12-22T23:25:17.000Z

Hey @jfinzel, can you install master and see if it works as expected?

gem install specific_install
gem specific_install ankane/pgdexter

Answer 4 · 2017-12-26T16:28:36.000Z

@ankane as I said, I don't have much of a ruby dev env. I tried, but failed, running into this issue: rdp/specific_install#21

sudo gem specific_install https://github.com/ankane/dexter.git
/usr/bin/git
git installing from https://github.com/ankane/dexter.git
Cloning into '/tmp/d20171226-19790-sawpds'...
remote: Counting objects: 1142, done.
remote: Compressing objects: 100% (103/103), done.
remote: Total 1142 (delta 84), reused 115 (delta 51), pack-reused 987
Receiving objects: 100% (1142/1142), 153.16 KiB, done.
Resolving deltas: 100% (638/638), done.
ERROR:  While executing gem ... (NoMethodError)
    undefined method `build' for Gem::Package:Module

I'm not too thrilled about figuring this env problem out. Is it difficult for you to package the ubuntu xenial packages? I normally download these actual debian package files and install them using dpkg. If you have any suggestions as to how to resolve my ruby issues I'm all ears.

Answer 5 · 2017-12-26T16:46:56.000Z

@jfinzel Package instructions are here: https://github.com/ankane/dexter/blob/master/guides/Linux.md

Answer 6 · 2017-12-26T17:10:45.000Z

Beautiful. Didn't realize that the packages were available already. Thanks. I will get back to you.

Answer 7 · 2017-12-26T17:43:53.000Z

It appears to be working now!

--------------------------------------------------------------------------------
Query 024c3b0749767ad544e002823b0476bb03d20a5d03
Total time: 0.0 min, avg time: 6 ms, calls: 1
No candidate tables for indexes

SELECT pg_catalog.quote_ident(c.relname) FROM pg_catalog.pg_class c WHERE c.relkind IN ('r', 'S', 'v', 'm', 'f') AND substring(pg_catalog.quote_ident(c.relname),1,6)='pg_sta' AND pg_catalog.pg_table_is_visible(c.oid)        UNION
        SELECT pg_catalog.quote_ident(n.nspname) || '.' FROM pg_catalog.pg_namespace n WHERE substring(pg_catalog.quote_ident(n.nspname) || '.',1,6)='pg_sta' AND (SELECT pg_catalog.count(*) FROM pg_catalog.pg_namespace WHERE substring(pg_catalog.quote_ident(nspname) || '.',1,6) = substring('pg_sta',1,pg_catalog.length(pg_catalog.quote_ident(nspname))+1)) > 1
        UNION
        SELECT pg_catalog.quote_ident(n.nspname) || '.' || pg_catalog.quote_ident(c.relname) FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n WHERE c.relnamespace = n.oid AND c.relkind IN ('r', 'S', 'v', 'm', 'f') AND substring(pg_catalog.quote_ident(n.nspname) || '.' || pg_catalog.quote_ident(c.relname),1,6)='pg_sta' AND substring(pg_catalog.quote_ident(n.nspname) || '.',1,6) = substring('pg_sta',1,pg_catalog.length(pg_catalog.quote_ident(n.nspname))+1) AND (SELECT pg_catalog.count(*) FROM pg_catalog.pg_namespace WHERE substring(pg_catalog.quote_ident(nspname) || '.',1,6) = substring('pg_sta',1,pg_catalog.length(pg_catalog.quote_ident(nspname))+1)) = 1
        LIMIT 1000

Processing 0 new query fingerprints
Processing 0 new query fingerprints
Processing 2 new query fingerprints
SQL: SELECT hypopg_reset()
SQL: SELECT table_schema || '.' || table_name AS 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'
SQL: SHOW search_path
SQL: SELECT schemaname || '.' || relname AS table, last_analyze, last_autoanalyze FROM pg_stat_user_tables WHERE schemaname || '.' || relname IN ('foo.bar')
Last analyze: foo.bar : 2017-12-21T11:23:10-06:00
SQL: EXPLAIN (FORMAT JSON) SELECT * FROM foo.bar WHERE customer_id = 3913437
SQL: SELECT table_schema || '.' || table_name AS table_name, column_name, data_type FROM information_schema.columns WHERE table_schema || '.' || table_name IN ('foo.bar') ORDER BY 1, 2
SQL: SELECT * FROM hypopg_create_index('CREATE INDEX ON "foo"."bar" ("customer_id")')
SQL: EXPLAIN (FORMAT JSON) SELECT * FROM foo.bar WHERE customer_id = 3913437
SQL: EXPLAIN (FORMAT JSON) SELECT * FROM foo.bar WHERE customer_id = 3913437
SQL: SELECT schemaname || '.' || t.relname AS table, ix.relname AS name, regexp_replace(pg_get_indexdef(i.indexrelid), '^[^\(]*\((.*)\)$', '\1') AS columns, regexp_replace(pg_get_indexdef(i.indexrelid), '.* USING ([^ ]*) \(.*', '\1') AS using FROM pg_index i INNER JOIN pg_class t ON t.oid = i.indrelid INNER JOIN pg_class ix ON ix.oid = i.indexrelid LEFT JOIN pg_stat_user_indexes ui ON ui.indexrelid = i.indexrelid WHERE schemaname || '.' || t.relname IN ('foo.bar') AND indisvalid = 't' AND indexprs IS NULL AND indpred IS NULL ORDER BY 1, 2
Index found: foo.bar (customer_id)
--------------------------------------------------------------------------------
Query 0211d4ad4b4b4536ea8873999fa4637b2a5b515384
Total time: 0.0 min, avg time: 1544 ms, calls: 1
Start: 943461.81
Pass1: 47.68 : foo.bar (customer_id)
Pass2: 47.68 : foo.bar (customer_id)
Final: 47.68 : foo.bar (customer_id)

SELECT * FROM foo.bar WHERE customer_id = 3913437;

--------------------------------------------------------------------------------
Query 02f8605773dd3aec31308b7e23ae3eaf0e160f30b6
Total time: 0.0 min, avg time: 1 ms, calls: 1
Tables not present in current database

SELECT * FROM pg_stat_activity WHERE NOT pid = pg_backend_pid();

SQL: SELECT pg_try_advisory_lock(123456)
SQL: SELECT schemaname || '.' || t.relname AS table, ix.relname AS name, regexp_replace(pg_get_indexdef(i.indexrelid), '^[^\(]*\((.*)\)$', '\1') AS columns, regexp_replace(pg_get_indexdef(i.indexrelid), '.* USING ([^ ]*) \(.*', '\1') AS using FROM pg_index i INNER JOIN pg_class t ON t.oid = i.indrelid INNER JOIN pg_class ix ON ix.oid = i.indexrelid LEFT JOIN pg_stat_user_indexes ui ON ui.indexrelid = i.indexrelid WHERE schemaname || '.' || t.relname IN ('foo.bar') AND indisvalid = 't' AND indexprs IS NULL AND indpred IS NULL ORDER BY 1, 2
Creating index: CREATE INDEX CONCURRENTLY ON "foo"."bar" ("customer_id")
SQL: CREATE INDEX CONCURRENTLY ON "foo"."bar" ("customer_id")
Index created: 59109 ms
SQL: SET client_min_messages = 'error'
SQL: SELECT pg_advisory_unlock(123456)
SQL: SET client_min_messages = warning
Processing 9 new query fingerprints
SQL: SELECT hypopg_reset()
SQL: SELECT table_schema || '.' || table_name AS 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'
SQL: SHOW search_path
SQL: SELECT schemaname || '.' || relname AS table, last_analyze, last_autoanalyze FROM pg_stat_user_tables WHERE schemaname || '.' || relname IN ('foo.bar')
Last analyze: foo.bar : 2017-12-21T11:23:10-06:00
SQL: EXPLAIN (FORMAT JSON) SET client_min_messages = warning
SQL: EXPLAIN (FORMAT JSON) SELECT pg_try_advisory_lock(123456)
SQL: EXPLAIN (FORMAT JSON) SELECT * FROM hypopg_create_index('CREATE INDEX ON "foo"."bar" ("customer_id")')
SQL: EXPLAIN (FORMAT JSON) EXPLAIN (FORMAT JSON) SELECT * FROM foo.bar WHERE customer_id = 3913437
SQL: EXPLAIN (FORMAT JSON) SELECT pg_advisory_unlock(123456)
SQL: EXPLAIN (FORMAT JSON) CREATE INDEX CONCURRENTLY ON "foo"."bar" ("customer_id")
SQL: SELECT schemaname || '.' || t.relname AS table, ix.relname AS name, regexp_replace(pg_get_indexdef(i.indexrelid), '^[^\(]*\((.*)\)$', '\1') AS columns, regexp_replace(pg_get_indexdef(i.indexrelid), '.* USING ([^ ]*) \(.*', '\1') AS using FROM pg_index i INNER JOIN pg_class t ON t.oid = i.indrelid INNER JOIN pg_class ix ON ix.oid = i.indexrelid LEFT JOIN pg_stat_user_indexes ui ON ui.indexrelid = i.indexrelid WHERE schemaname || '.' || t.relname IN ('foo.bar') AND indisvalid = 't' AND indexprs IS NULL AND indpred IS NULL ORDER BY 1, 2
No new indexes found
--------------------------------------------------------------------------------
Query 020f4251015b74eb7e58755c04749b6bdc5e6142cb
Total time: 0.0 min, avg time: 0 ms, calls: 3
No tables

SET client_min_messages = warning

--------------------------------------------------------------------------------
Query 022134fffa0ec1ab5ca6ecfaa2e8ac08d32a1e4a75
Total time: 0.0 min, avg time: 3 ms, calls: 4
Tables not present in current database

SELECT schemaname || '.' || t.relname AS table, ix.relname AS name, regexp_replace(pg_get_indexdef(i.indexrelid), '^[^\(]*\((.*)\)$', '\1') AS columns, regexp_replace(pg_get_indexdef(i.indexrelid), '.* USING ([^ ]*) \(.*', '\1') AS using FROM pg_index i INNER JOIN pg_class t ON t.oid = i.indrelid INNER JOIN pg_class ix ON ix.oid = i.indexrelid LEFT JOIN pg_stat_user_indexes ui ON ui.indexrelid = i.indexrelid WHERE schemaname || '.' || t.relname IN ('foo.bar') AND indisvalid = 't' AND indexprs IS NULL AND indpred IS NULL ORDER BY 1, 2

--------------------------------------------------------------------------------
Query 022152944e070157a00864594efd3afa1a5f2df1ae
Total time: 0.0 min, avg time: 1 ms, calls: 2
Low initial cost: 0.01

SELECT pg_try_advisory_lock(123456)

--------------------------------------------------------------------------------
Query 02358dd0a711aa176bc3b2931c37a2cae4422db7fa
Total time: 0.0 min, avg time: 7 ms, calls: 2
Tables not present in current database

SELECT schemaname || '.' || relname AS table, last_analyze, last_autoanalyze FROM pg_stat_user_tables WHERE schemaname || '.' || relname IN ('foo.bar')

--------------------------------------------------------------------------------
Query 02b801bf8304d078f264405606b772f832bc3558d7
Total time: 0.0 min, avg time: 2 ms, calls: 2
Tables not present in current database

SELECT table_schema || '.' || table_name AS table_name, column_name, data_type FROM information_schema.columns WHERE table_schema || '.' || table_name IN ('foo.bar') ORDER BY 1, 2

--------------------------------------------------------------------------------
Query 02bebf4f26540f8da6b2f0b0cb03387f3e1fb3e798
Total time: 0.0 min, avg time: 0 ms, calls: 2
Low initial cost: 10.25

SELECT * FROM hypopg_create_index('CREATE INDEX ON "foo"."bar" ("customer_id")')

--------------------------------------------------------------------------------
Query 02c4515bd877f9816617be5a8e02796ee21c538c13
Total time: 0.0 min, avg time: 0 ms, calls: 6
Could not run explain

EXPLAIN (FORMAT JSON) SELECT * FROM foo.bar WHERE customer_id = 3913437

--------------------------------------------------------------------------------
Query 02ec6d7cfd17893bde837a09c75c0e669494d525b9
Total time: 0.0 min, avg time: 1 ms, calls: 2
Low initial cost: 0.01

SELECT pg_advisory_unlock(123456)

--------------------------------------------------------------------------------
Query 02f4a9be8567d2f25ee51695f7560a2ab7f4ad3fdb
Total time: 0.0 min, avg time: 0 ms, calls: 1
Could not run explain

CREATE INDEX CONCURRENTLY ON "foo"."bar" ("customer_id")

Processing 3 new query fingerprints
SQL: SELECT hypopg_reset()
SQL: SELECT table_schema || '.' || table_name AS 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'
SQL: SHOW search_path
No new indexes found
--------------------------------------------------------------------------------
Query 023d3fe4c2977c63c45b14e2c3fe2ed43a8f96c2c7
Total time: 0.0 min, avg time: 0 ms, calls: 2
No candidate tables for indexes

EXPLAIN (FORMAT JSON) SELECT pg_advisory_unlock(123456)

--------------------------------------------------------------------------------
Query 02d09b3a0e0186e30996ec50ee44d834b1f497ee62
Total time: 0.0 min, avg time: 0 ms, calls: 2
No candidate tables for indexes

EXPLAIN (FORMAT JSON) SELECT pg_try_advisory_lock(123456)

--------------------------------------------------------------------------------
Query 02ed6f30b114223a58064463658319d8bebd3ebe5d
Total time: 0.0 min, avg time: 0 ms, calls: 2
No candidate tables for indexes

EXPLAIN (FORMAT JSON) SELECT * FROM hypopg_create_index('CREATE INDEX ON "foo"."bar" ("customer_id")')

Processing 0 new query fingerprints

Answer 8 · 2017-12-26T18:07:24.000Z

Great, going to close this out. Thanks again for all the great feedback (across all the issues you opened), and let me know if anything else comes up as you're using it. 💯