Explain's estimation differs from real count enormously - Mailing list pgsql-general

Hi,

My environment is:

PostgreSQL 8.3.4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.2.3
(Ubuntu 4.2.3-2ubuntu7)
+ recent patch from Teodor Sigaev

EXPLAIN ANALYZE of the query shows strange estimated rows count:

EXPLAIN ANALYZE SELECT * FROM objonmap
WHERE 1=1
    AND box(om_point, om_point) <@ box(point(-55.97398205077, -100),
point(82.166446008477, 180))
    AND (om_flag OR om_ref_id IN (69000001071, 69000001513,
69000001092, 69000001091))
    AND om_state = 1;

Index Scan using i_objonmap__geo on objonmap  (cost=0.00..7.80 rows=54
width=556) (actual time=0.085..241.089 rows=55107 loops=1)
  Index Cond: (box(om_point, om_point) <@
'(82.166446008477,180),(-55.97398205077,-100)'::box)
  Filter: (om_flag OR (om_ref_id = ANY
('{69000001071,69000001513,69000001092,69000001091}'::bigint[])))
Total runtime: 259.286 ms

I had thought firstly that It is because of the index until I tried
turning indexscan and bitmapscan off:

SET enable_indexscan TO OFF;
SET enable_bitmapscan TO OFF;

Seq Scan on objonmap  (cost=0.00..30725.14 rows=54 width=556) (actual
time=0.042..133.192 rows=55107 loops=1)
  Filter: ((om_state = 1) AND (om_flag OR (om_ref_id = ANY
('{69000001071,69000001513,69000001092,69000001091}'::bigint[]))) AND
(box(om_point, om_point) <@
'(82.166446008477,180),(-55.97398205077,-100)'::box))
Total runtime: 151.589 ms

Could somebody explain me why it happens and how to make it work right?

--
Regards,
Sergey Konoplev
--
PostgreSQL articles in english & russian
http://gray-hemp.blogspot.com/search/label/postgresql/