Oleg Bartunov <oleg@sai.msu.su> writes:
> something is broken in HEAD. I recall it worked in STABLE, here is what I have
No, actually it is broken in 8.0 too. The difference is that 8.0
defaults to not using an indexscan for this query. In 8.0 I see:
regression=# set enable_seqscan TO 1;
SET
regression=# explain select count(*) from gist_emp4000 where home_base <<
'(35565,5404),(35546,5360)';
QUERY PLAN
---------------------------------------------------------------------
Aggregate (cost=65.53..65.53 rows=1 width=0)
-> Seq Scan on gist_emp4000 (cost=0.00..64.75 rows=310 width=0)
Filter: (home_base << '(35565,5404),(35546,5360)'::box)
(3 rows)
regression=# select count(*) from gist_emp4000 where home_base <<
'(35565,5404),(35546,5360)';
count
-------
2214
(1 row)
regression=# set enable_seqscan TO 0;
SET
regression=# explain select count(*) from gist_emp4000 where home_base <<
'(35565,5404),(35546,5360)';
QUERY PLAN
----------------------------------------------------------------------------------------
Aggregate (cost=112.96..112.96 rows=1 width=0)
-> Index Scan using grect2ind on gist_emp4000 (cost=0.00..112.18 rows=310
width=0)
Index Cond: (home_base << '(35565,5404),(35546,5360)'::box)
(3 rows)
regression=# select count(*) from gist_emp4000 where home_base <<
'(35565,5404),(35546,5360)';
count
-------
2144
(1 row)
In CVS tip the default plan is:
regression=# explain select count(*) from gist_emp4000 where home_base <<
'(35565,5404),(35546,5360)';
QUERY PLAN
--------------------------------------------------------------------------------
Aggregate (cost=35.74..35.74 rows=1 width=0)
-> Bitmap Heap Scan on gist_emp4000 (cost=5.08..34.96 rows=310 width=0)
Recheck Cond: (home_base << '(35565,5404),(35546,5360)'::box)
-> Bitmap Index Scan on grect2ind (cost=0.00..5.08 rows=310 width=0)
Index Cond: (home_base << '(35565,5404),(35546,5360)'::box)
(5 rows)
regression=# select count(*) from gist_emp4000 where home_base <<
'(35565,5404),(35546,5360)';
count
-------
2144
(1 row)
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
