Post filter on date range appears to improve performance


(Michalis A Anastasopoulos) #1

Hello,
I am trying to improve the performance of date range queries and came up with the following alternatives. Field "updatedAt" is a date field in the following

Which alternative would you recommend?

Curiously query (3) that uses post_filter appears much quicker.

Post_filter is guaranteed to execute after the query. Hence the number of documents the expensive date range has to go through is much smaller. Can that be the explanation? And what about the recommendation in https://www.elastic.co/guide/en/elasticsearch/guide/current/_post_filter.html that says "Don't use post filter for searches" ?

(1)

{
"query": {
    "bool": {
        "filter": [
            {
                "term": {
                    "customerId": 1005
                }
            },
            {
                "term": {
                    "isChecked": false
                }
            }
        ],
        "must": [
            {
                "range": {
                    "updatedAt": {
                        "from": null,
                        "to": "now",
                        "include_lower": true,
                        "include_upper": false
                    }
                }
            }
        ]
    }
},
"sort": [
    {
        "updatedAt": {}
    }
]}

(2)

{
"query": {
    "bool": {
        "filter": [
            {
                "term": {
                    "customerId": 1005
                }
            },
            {
                "term": {
                    "isChecked": false
                }
            },
            {
                "range": {
                    "updatedAt": {
                        "from": null,
                        "to": "now",
                        "include_lower": true,
                        "include_upper": false
                    }
                }
            }
        ]
    }
},
"sort": [
    {
        "updatedAt": {}
    }
]}

(3)

{
"query": {
    "bool": {
        "filter": [
            {
                "term": {
                    "customerId": 1005
                }
            },
            {
                "term": {
                    "isChecked": false
                }
            }
        ]
    }
},
"post_filter": {
    "bool": {
        "must": [
            {
                "range": {
                    "updatedAt": {
                        "from": null,
                        "to": "now",
                        "include_lower": true,
                        "include_upper": false
                    }
                }
            }
        ]
    }
},
"sort": [
    {
        "updatedAt": {}
    }
]}

(system) #2

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.