The open source drop_indexes function in our open source Postgres Bag of Tricks (Github) lets you pass in a schema and table, like this:

And it automatically drops all of the indexes on that table, like this:

Why, you might ask? Well, because for my Postgres performance tuning classes, I needed a way to rapidly reset tables after each training module so that we were working with just the bare heap.

Dr. Daniel Ducharme, Ph.D. (Github – LinkedIn) was kind enough to write this, so a warm round of applause for him!

Parameters and What They Do

v_schema_name and v_table_name: required, self-explanatory.

v_drop_primary_keys boolean default false: if you pass in true, we’ll delete the table’s primary keys as well. Defaults to false because in index tuning classes, the primary key isn’t usually up for debate or reassignment.

v_force_drop_with_constraints boolean default false: I don’t use this in the training classes, but Dr. Ducharme was nice enough to code it, so in it stays!

v_drop_concurrently boolean default false: I don’t use this in the training classes, but Dr. Ducharme was nice enough to code it, so in it stays!

v_list_indexes_being_dropped boolean default true: if false, the only output result just says “Success”, and has one row rather than one row per index.

v_print_drops_but_dont_execute boolean default false: if true, that means you don’t trust our code or you want to run the drops yourself. Neither of those hurts my feelings.

For Support or Changes

Use Github Issues in the Box of Tricks repo. When filing an issue, make sure to include your Postgres version and any screenshots to help explain the problem you’re running into.

For feature requests, please be prepared to contribute the code yourself. Open source doesn’t mean free consulting or development from others – it means being willing to roll up your sleeves if there’s something you really want included. Don’t be scared – the source code on this one is really simple! Hop in there and get started.

Hope drop_indexes makes your job & training easier. Cheers!