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

On Mon, Aug 31, 2015 at 3:19 PM, twoflower <standa(dot)kurik(at)gmail(dot)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.
​