Strange differences in query response times

[jda]

@mreiche

SELECT * FROM data 
WHERE type='FishingTrip' AND 
    (locationtype='1' AND locationlevel2='91')
    UNION
SELECT * FROM data 
WHERE type='FishingTrip' AND  
    (locationtype='2' AND locationlevel4='91')

Very interesting…

That query returns the documents in 230-250ms (!!)

And adding the DISTINCT doesn’t seem to change the response time.

[jda]

I just did another experiment by adding another set of locationtype/-level conditions:

SELECT DISTINCT *
FROM (
    SELECT *
    FROM data
    WHERE type='FishingTrip'
        AND (locationtype='1' AND locationlevel2='91')
    UNION
    SELECT *
    FROM data
    WHERE type='FishingTrip'
        AND (locationtype='2' AND locationlevel4='91')
    UNION
    SELECT *
    FROM data
    WHERE type='FishingTrip'
        AND (locationtype='4' AND locationlevel4='91')) d

And that increases the response just a little bit (~30ms) instead of doubling it.

So yes, that may be the workaround that I can implement to get the better response times. I haven’t had time to look at upgrading the servers yet, but I should do that as well.

Thanks for the valuable input @mreiche and @dh

/John

[dh]

It is weird that the index scan with multiple ranges plus fetch is the only combination that is problematic. But in isolation both operations perform well, as does an index scan with a single range + fetch.

I guess you’d see good performance then when selecting only one of the indexed fields too, e.g.

SELECT type FROM data 
WHERE type='FishingTrip' AND (
    ((locationtype='1' AND locationlevel2='91') or 
    (locationtype='2' AND locationlevel4='91'))
    )

as this is covered and shouldn’t require a fetch either.

Glad to hear the workaround will work for your immediate needs.

[jda]

Yes it is Ok, 280ms

[jda]

Just a quick follow up. I upgraded our test servers to 7.2.4 this morning prior to wanting to implement the workaround. Then I did some measurements first - and it seems that response times for the original query and the union query now are similar - so I’ll leave it as is for now

[dh]

Pleased to hear the anomaly is absent at least.