PostgreSQL returns result only when additional AND clause is added
I have a request like this:
SELECT boroughs.name
FROM boroughs, uniroads
WHERE uniroads.normalizedName='6 AVENUE'
AND st_intersects(boroughs.geometry, uniroads.way)
AND boroughs.name='Brooklyn'
0 results
But when I run it, it returns no results. However, I can find a specific row in the table that I would like to return, and when I add a clause asking for that specific row, it works fine:
SELECT boroughs.name
FROM boroughs, uniroads
WHERE uniroads.normalizedName='6 AVENUE'
AND st_intersects(boroughs.geometry, uniroads.way)
AND boroughs.name='Brooklyn'
AND uniroads.osm_id='23334071'
1 result
I am using Postgres 9.2.2.0 with PostGIS via Postgres.app.
source to share
A guess.
uniroads.osm_id
looks like a key, so it will most likely be indexed.
AND uniroads.osm_id='23334071'
causes the reason (different, maybe?) the index to be used, so it could mean (some?) the originally used indexes are corrupted.
Maybe the following might help?
REINDEX TABLE boroughs;
REINDEX TABLE uniroads;
In any case, EXPLAIN ANALYZE
required for both queries, as well as for the complete definitions of the tables used.
source to share