I'm looking to set up indexing for all of the foreign keys in our production DB to improve efficiency. The foreign keys have already been made, I was worried that I would have to re-create the table with the new indexing on foreign keys but from what I've read that isn't necessary. I have tried adding the foreign keys both through the PGAdmin viewer and by creating individual SQL queries.
When I look at the foreign key settings the "Auto FK Indexing" isn't checked and doesn't let me toggle it.
So the next thing I tried was deleting the entire foreign key and re-creating it. When I do this it does allow me to toggle the button to enable "Auto FK Indexing" but after I save it and refresh it goes back to the default with Auto Indexing turned off.
Lastly, I tried just adding an index to the FK like this- CREATE INDEX idx_tablename_ProspectId_2 ON public.table_name ("ProspectId");
That gave me a success message but again, when I check the ForeignKey it claims that there is nothing there.
Finally, I tried deleting the foreign key and recreating it in SQL like this- ALTER TABLE IF EXISTS public.table_name ADD CONSTRAINT "dx_tablename_ProspectId_2" FOREIGN KEY ("ProspectId") REFERENCES public.prospect_info (id) ON UPDATE NO ACTION ON DELETE CASCADE;
What seems odd to me is that there aren't any error messages as I add Indexing to the foreign keys. If there are some basic principles that are preventing the foreign keys from accepting the indexing commands, I'd love to learn about them.