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.
