Creating a run-book for troubleshooting PostgreSQL query performance, specifically focusing on JOIN operations, involves understanding how suboptimal indexes impact JOIN performance and providing tips for writing optimal JOINs. Let's break this down:

Troubleshooting PostgreSQL Query Performance in JOINs

Step 1: Identifying Slow JOINs

• Use EXPLAIN ANALYZE on your queries to identify which JOIN operations are slow.
• Look for high row estimations, sequential scans on large tables, or nested loop joins that iterate over a large number of rows.

Step 2: Analyze Execution Plan

• Focus on the join type (Nested Loop, Hash Join, Merge Join) and see if it's appropriate for the data and indexes available.
• Check if the planner's row estimations are significantly off from the actual rows returned, indicating outdated statistics.

Step 3: Check Index Usage

• Ensure that the columns being joined on are indexed, especially for large tables.
• Verify that the indexes are being used effectively. Sometimes, PostgreSQL may choose a sequential scan over an index scan if it thinks it's faster.

Step 4: Update Statistics

• Run ANALYZE on the tables involved to ensure the statistics are up-to-date.
• Consider increasing the default_statistics_target parameter for more detailed statistics if the default level isn't sufficient.

Step 5: Experiment with Query Rewriting

• Try rewriting the query to see if a different formulation yields a better execution plan.

Step 6: Server Configuration

• Check if the configuration settings like work_mem are sufficient for the sort and join operations.

Step 7: Monitor and Log Long Queries

• Enable log_min_duration_statement to log long-running queries for further analysis.

Impact of Suboptimal Indexes on PostgreSQL JOINs

• Nested Loop Performance: Suboptimal indexes can lead to inefficient nested loop joins, where the database has to iterate over many rows of one table for each row of another.
• Hash and Merge Joins: Without proper indexes, PostgreSQL might not choose more efficient join methods like hash or merge joins, leading to slower performance.
• Index Scans vs. Sequential Scans: Poorly designed indexes might lead to the database opting for a full table scan instead of a faster index scan.

Tips for Writing Optimal JOINs in PostgreSQL

1. Use Appropriate Indexes:
   • Create indexes on columns used in JOIN conditions, especially for large tables.
   • Consider multi-column indexes if you frequently join on multiple columns together.
2. Balanced JOIN Conditions:
   • Ensure that the data types in JOIN conditions match to avoid implicit type conversion.
3. Optimize JOIN Order:
   • In complex queries, the order of JOINs can impact performance. Start with the smallest table or the one with the most restrictive conditions.
4. Use Explicit JOIN Types When Necessary:
   • Sometimes, giving PostgreSQL a hint about using a specific JOIN type (e.g., INNER JOIN, LEFT JOIN) can lead to a better execution plan.
5. Keep Statistics Updated:
   • Regularly update statistics with ANALYZE for accurate query planning.
6. Avoid Redundant Columns in JOINs:
   • Only select the columns you need in the JOIN to reduce the amount of data processed.
7. Consider Partitioning Large Tables:
   • If JOINs involve very large tables, table partitioning might improve performance.
8. Monitor and Tune:
   • Regularly monitor query performance and tune as necessary based on actual query execution plans.

Remember, each PostgreSQL environment is unique, so these guidelines should be adapted and tested according to your specific context. Regular monitoring, along with a thorough understanding of your data and query patterns, is key to maintaining optimal JOIN performance.