I have what appears to be a corrupt RTREE index.
The first query shows that of the fifteen records I'm looking at, every one of
them has the "@" based condition showing as true. The second shows one record
that really ought to be there not being listed.
I just tried the second query with enable_indexscan = off and the missing
record reappears. So I guess this is a corrupt index.
This is 7.4.6 but the database was initdb'd with an earlier 7.4.
Should I REINDEX or should I keep this around for forensic study?
slo=>
SELECT distinct store_id, geom2 @ box
'(-72.7956933266664,46.041219387024),(-74.3364602689304,44.9613057801126)'
FROM store_location
WHERE earth_dist(geom, -73.5660767977984, 45.5012625835683) <= 60
;
slo=> slo-> slo-> slo-> slo-> store_id | ?column?
----------+----------
504 | t
597 | t
909 | t
2841 | t
2940 | t
2997 | t
3423 | t
3438 | t
3641 | t
3656 | t
4057 | t
4487 | t
4489 | t
4490 | t
4493 | t
(15 rows)
slo=>
SELECT distinct store_id, geom2 @ box
'(-72.7956933266664,46.041219387024),(-74.3364602689304,44.9613057801126)'
FROM store_location
WHERE earth_dist(geom, -73.5660767977984, 45.5012625835683) <= 60
AND geom2 @ box
'(-72.7956933266664,46.041219387024),(-74.3364602689304,44.9613057801126)'
;
slo=> slo-> slo-> slo-> slo-> store_id | ?column?
----------+----------
504 | t
597 | t
909 | t
2841 | t
2940 | t
2997 | t
3423 | t
3438 | t
3641 | t
3656 | t
4057 | t
4487 | t
4489 | t
4490 | t
(14 rows)
--
greg
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
