Thread: Explain's estimation differs from real count enormously

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/

run vacuum analyze <table>; before running that query, is it still off after that ?

> run vacuum analyze <table>; before running that query, is it still off after
> that ?
>

I forgot to mention that ANALYZE, VACUUM ANALYZE even w/ FULL and
REINDEX doesn't help.

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

>
> 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;
>

One more thing - removing condition "AND box(om_point, om_point) <@
box(point(-55.97398205077, -100), point(82.166446008477, 180))" leads
to good (right) estimation.

BTW, pg 8.3.3 behave the same.

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

"Sergey Konoplev" <gray.ru@gmail.com> writes:
> Could somebody explain me why it happens and how to make it work right?

Implement a selectivity estimator for <@ that isn't just a stub :-(

            regards, tom lane