We have traced this to the *addition* of a two-column index. > -----Original Message----- > From: pgsql-performance-ow...@postgresql.org [mailto:pgsql- > performance-ow...@postgresql.org] On Behalf Of Jim Garrison > Sent: Wednesday, September 25, 2013 8:58 AM > To: pgsql-performance@postgresql.org > Subject: [PERFORM] Troubleshooting query performance issues > > I spent about a week optimizing a query in our performance-testing > environment, which has hardware similar to production. > > I was able to refactor the query and reduce the runtime from hours to about > 40 seconds, through the use of CTEs and a couple of new indexes. > > The database was rebuilt and refreshed with the very similar data from > production, but now the query takes hours again. > > In the query plan, it is clear that the row count estimates are WAY too low, > even though the statistics are up to date. Here's a sample query plan: > [snip]
The two tables in question both have single-column indexes on two foreign keys, say columns A and B. The query joins the two large tables on A and B. With only the two indexes, the query plan does a bitmap AND on the index scan results and performance is stable. I added an index on (A,B), and this caused the planner to use the new index, but I was never able to get the query to complete. In one instance I let it run 18 hours. The onlly difference was the addition of the index Summary: - With index on (A,B) -- query time is "infinite" - Without index on (A,B), relying on individual indexes and bitmap AND -- query time is about 4 minutes (as expected given the data volume) Does this sound like a bug in the query planner? -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
