title: Creating foreign key references to optimize Tableau SQL queries
body: <p>I'm a newbie on database design hoping to learn by experimenting and implementing, and I'm sure some version of this question has been asked within database design in general, but this is specific to Tableau.</p>
  
  <p>I have a few dashboards that are drawing from a PostgreSQL database table that contains several million rows of data. Performance rerendering views is quite slow (ie., if I select a different parameter, Tableau's <code>Executing SQL query</code> popup will appear and it often takes several minutes to complete).</p>
  
  <p>I debugged this by using the performance recording option within Tableau, exporting the SQL queries that Tableau is using to a text file, and then using <strong><code>EXPLAIN ANALYZE</code></strong> to figure out what exactly the bottlenecks were. Unfortunately, I can't post the SQL queries themselves, but I've created a contrived case below to be as helpful as possible. Here is what my table looks like currently. The <strong>actual values being rendered by Tableau</strong> are in <strong>green</strong>, and the columns that I have foreign key references on are in <strong>yellow</strong>:
  <a href="https://i.stack.imgur.com/O6ota.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/O6ota.png" alt="enter image description here"></a></p>
  
  <p>I see within the Query Plan that there's a lot of costly bitmap heap scans that are implementing the filters am using on Tableau on the frontend on <strong><code>neighborhood_id</code>, <code>view</code>, <code>animal</code>, <code>date_updated</code>, <code>animal_name</code></strong>. </p>
  
  <p>I've attempted to place a multiple index on these fields, but upon rerunning the queries, it does not look like the PG query planner is electing to use these indices.</p>
  
  <p>So my proposed solution is to create foreign key references for each of these fields (<strong><code>neighborhood_id</code>, <code>view</code>, <code>animal</code>, <code>date_updated</code>, <code>animal_name</code></strong>)- again, <strong>yellow represents a FK reference</strong>:
  <a href="https://i.stack.imgur.com/1Db5s.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/1Db5s.png" alt="enter image description here"></a></p>
  
  <p>My hope is that these FK references will force the query planner is use an index scan instead of a sequential scan or bitmap heap scan. However, my questions are </p>
  
  <ol>
  <li><p>Before, all the data was more or less stored in this one table, with
  two joins to <code>shelter</code> and <code>age_of_animal</code> tables. Now, this table
  will be joined to 8 smaller subtables- will these joins drastically
  reduce performance? The subtables are quite small (ie. the <code>animal</code>
  table will have only 40 entries). </p></li>
  <li><p>I know the question is difficult to answer without seeing the actual
  query and query plan, but what are some high-level reasons would the
  query planner elect to not use an index? I've read through some articles like <a href="https://robots.thoughtbot.com/why-postgres-wont-always-use-an-index" rel="nofollow noreferrer">"Why Postgres Won't Always Use An Index"</a> but mostly they refer to cases where it's a small table and a simple query where the cost of the index lookup is greater than simply traversing the rows. I don't think applies to my case though- I have millions of rows and a complex filter on 5+ columns.</p></li>
  <li>Is the PG Query Planner any more likely to use multiple column
  indices on a collection of foreign key columns versus regular
  columns? <a href="https://stackoverflow.com/questions/48793071/does-a-postgres-foreign-key-imply-an-index">I know that PG does not automatically add indices on
  foreign keys</a>, so I imagine I'll still need to add indices after
  creating the foreign key references.</li>
  </ol>
  
  <p>Of course, the answers to my questions could be "Why don't you just try it and see?", but in this case refactoring such a large table is quite costly and I'd like some intuition on whether it's worth the cost prior to undertaking it. </p>