Re: Troubleshooting query performance issues - resolved (sort of)

Поиск
Список
Период
Сортировка
От Jim Garrison
Тема Re: Troubleshooting query performance issues - resolved (sort of)
Дата
Msg-id 0C723FEB5B4E5642B25B451BA57E27303EE06EA7@S1P5DAG3C.EXCHPROD.USA.NET
обсуждение исходный текст
Список pgsql-performance
We have traced this to the *addition* of a two-column index.
> -----Original Message-----
> From: pgsql-performance-owner@postgresql.org [mailto:pgsql-
> performance-owner@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
thetwo 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
giventhe data volume) 

Does this sound like a bug in the query planner?




В списке pgsql-performance по дате отправления:

Предыдущее
От: Jim Garrison
Дата:
Сообщение: Re: Troubleshooting query performance issues - Resolved (sort of)
Следующее
От: Josh Berkus
Дата:
Сообщение: Re: Planner performance extremely affected by an hanging transaction (20-30 times)?