| From: | Greg Stark <gsstark(at)mit(dot)edu> | 
|---|---|
| To: | pgsql-general(at)postgresql(dot)org | 
| Subject: | Corrupt RTREE index | 
| Date: | 2004-12-13 21:23:45 | 
| Message-ID: | 87zn0hvqn2.fsf@stark.xeocode.com | 
| Views: | Whole Thread | Raw Message | Download mbox | Resend email | 
| Thread: | |
| Lists: | pgsql-general | 
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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Scott Marlowe | 2004-12-13 21:39:25 | Re: SELECTing on age | 
| Previous Message | Phil Endecott | 2004-12-13 21:18:09 | Re: Temporary tables and disk activity |