Re: [PERFORM] Re: Query > 1000× slowdown after adding datetime comparison

Поиск
Список
Период
Сортировка
От David G. Johnston
Тема Re: [PERFORM] Re: Query > 1000× slowdown after adding datetime comparison
Дата
Msg-id CAKFQuwbUZLWuhoaAF7mWdmRr-6LG9NdMfsyPasXo1ZY82YVRzA@mail.gmail.com
обсуждение исходный текст
Ответ на Re: Query > 1000× slowdown after adding datetime comparison  (twoflower <standa.kurik@gmail.com>)
Ответы Re: Re: Query > 1000× slowdown after adding datetime comparison  (twoflower <standa.kurik@gmail.com>)
Список pgsql-performance
Дерево обсуждения
On Mon, Aug 31, 2015 at 3:19 PM, twoflower <standa.kurik@gmail.com> wrote:
And another thing which comes out as a little surprising to me - if I replace
the *date_last_updated* condition with another one, say *doc.documenttype =
4*, the query finishes immediately. *documenttype* is an unindexed integer
column.


​The only index that matters here is the pkey on document.  The problem is the failure to exit the nested loop once 1,000 translations have been gathered.  Translation is related to document via key - hence the nested loop.  A hashing-based plan would make use of the secondary indexes but likely would not be particularly useful in this query (contrary to my earlier speculation).

Here's the query plan:

<http://postgresql.nabble.com/file/n5864080/qp3.png>

What's so special about that *date_last_updated* condition that makes it so
slow to use? Is it because it involves the *date()* function call that it
makes it difficult for the planner to guess the data distribution in the
DOCUMENT table?

What happens if you pre-compute the date condition and hard code it?

​
​David J.
​

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

Предыдущее
От: "David G. Johnston"
Дата:
Сообщение: Re: [PERFORM] Re: Query > 1000× slowdown after adding datetime comparison
Следующее
От: twoflower
Дата:
Сообщение: Re: Re: Query > 1000× slowdown after adding datetime comparison