I have a table with 600000 records. My query is filtering by two columns one being indexed and it’s called ancestry and the other being type and it has 3 values (1,2,3) and it is not indexed.
When running the first part alone it takes 1 second to get the results. The query being:
SELECT * FROM geolocations WHERE EXISTS ( SELECT 1 FROM geolocations g2 WHERE g2.ancestry = CONCAT(geolocations.ancestry, '/', geolocations.id) )
The other query runs in 0.7 seconds:
SELECT * FROM geolocations WHERE location_type = 2
When combining both queried with an OR it takes around 70 seconds to finish if it ever finishes. I ended up using a union and it returns result in less than 2 seconds which is logical to me.
My question is, what exactly in the way postgres executes the original OR query that makes it this slow?