TODO / Ideas

[ankane]

0.4.0

• Update to pg_query 2
• Drop support for Ruby < 2.5 (2.5 is EOL but Ubuntu 18.04 uses it)

Ideas

• determine write-heavy tables automatically
• notifications when indexes found (email & Slack)
• only create indexes at certain times of day (low traffic times) - one approach is to run Dexter daily instead of stream
• option to write created/suggested indexes to table (in dexter schema)
• optimize across all queries (get N best indexes for a query)
• non-BTREE indexes (add support to hypopg)
• use multiple samples for each fingerprint
• add support for directions to multicolumn indexes
• calculate total time saved, and order indexes by this

Big ideas

• prefer lots of small partial indexes over full indexes

Full index management

• drop duplicate/covered indexes
• drop unused indexes - drop_unused branch (have to be careful with replicas)
• drop indexes that have little benefit (have to be careful with replicas)

[matias-eduardo]

This is great! Thank you.
+1 for determining write-heavy tables 🙌

[booleanbetrayal]

+1 for multi-column indexes, as this would cover a significant amount of our existing index definitions. Would love to use Dexter to validate certain assumptions, but probably can't until that goes in.

[jfinzel]

What about partial indexes - especially for low cardinality fields? You could use pg_statistics to help with that. I haven't checked if these are supported as suggestions but I am guessing not yet?

[anthares]

Hi @ankane,

Great work so far on pghero and dexter, thanks :)
I was wondering if there is plans to implement dexter to Slack connectivity in a near future.
Because we'd rather use dexter for automatic missing index detection and notification. Slack notification would be far more convenient than tailling the logs.

Thanks,
Anthares

[jfinzel]

@ankane does it support creating indexes when queries only use views? I have a particular system where almost all user queries are pointed to views, thus of course indexes can only apply to the tables underneath. This would be very useful.

[ankane]

@jfinzel Just added support for views and materialized views.

[jfinzel]

@ankane now that I am finding this working, I have another thought. It would be nice to run this and save the query_id, initial cost, pass costs, sql and suggested index. The reason is for additional analysis - I want to see how many queries end up suggesting the same index before creating one.

It already is very useful as-is, but would be a "nice to have" to have something like an output format that I could save itself into a db table and run some sampling on.

[alexandervlpl]

+1 for "non-BTREE indexes", specifically GiST!

@ankane This is amazing work, I wish I could already use Dexter to GiST index my PostGIS databases. The potential to improve the performance of OpenStreetMap and other map services would be enormous!

Specifically, it would be great to have Dexter suggest indexes like this one from OpenStreetMap:

CREATE INDEX planet_osm_roads_admin ON planet_osm_roads USING GIST (way) WHERE boundary = 'administrative';

[brightball]

Specify a tablespace for indexes created by Dexter. Beneficial for setups with multiple mounted disks where you may have access to something like very fast NVMe drives.

[cpj95]

Is there support for AWS RDS running PostgreSQL yet?
@ankane

[ankane]

@brightball fwiw, just released a --tablespace option.

@cpj95 See the instructions in the readme for how to use Dexter with Amazon RDS.