Thread: Strange postgres planner behaviour

Hi All, 

I want to descibe some strange behaviour of the postgres planner.


I have 2 tables:

wsdb=# \d q3c    Table "public.q3c"Column |  Type  | Modifiers 
--------+--------+-----------ipix   | bigint | errbox | box    | ra     | real   | dec    | real   | 
Indexes:   "ipix_idx" btree (ipix) CLUSTER   "rtree_ind" rtree (errbox)


And the other table 

wsdb=# \d q3c_subset  Table "public.q3c_subset"Column |  Type  | Modifiers 
--------+--------+-----------ipix   | bigint | errbox | box    | ra     | real   | dec    | real   | 


When I run  the following query, the plan for it is index scan, and it's ok.  


wsdb=# EXPLAIN SELECT  *  FROM q3c_subset AS uu,q3c WHERE 
(q3c.ipix>uu.dec::bigint AND q3c.ipix<uu.ra::bigint) AND
(q3c.ipix>uu.ra::bigint AND q3c.ipix<uu.dec::bigint) ;


QUERY PLAN                                                                                    

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Nested
Loop (cost=0.01..270564956.56 rows=4221207699 width=96)  ->  Seq Scan on q3c_subset uu  (cost=0.00..2314.72 rows=113972
width=48) ->  Index Scan using ipix_idx on q3c  (cost=0.01..1262.80 rows=37038
 
width=48)        Index Cond: ((q3c.ipix > ("outer"."dec")::bigint) AND (q3c.ipix <
("outer".ra)::bigint) AND (q3c.ipix > ("outer".ra)::bigint) AND (q3c.ipix <
("outer"."dec")::bigint))
(4 rows)


But, when in my query I replace one "AND" to "OR" (see below), I have the
sequential scan. BUT THIS IS NOT the reason why I wrote this letter, the
main surprising thing is that even if I "set enable_seq_scan to off" the
plan for new query is still seq. scan!!! So the planner don't even consider
the index scan plan in that case (see below).



wsdb=# EXPLAIN SELECT  *  FROM q3c_subset AS uu,q3c WHERE 
(q3c.ipix>uu.dec::bigint AND q3c.ipix<uu.ra::bigint) OR
(q3c.ipix>uu.ra::bigint AND q3c.ipix<uu.dec::bigint) ;


QUERY PLAN                                                                                           

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Nested
Loop (cost=2428.69..13676776298.93 rows=71760530869 width=96)  Join Filter: ((("outer".ipix > ("inner"."dec")::bigint)
AND("outer".ipix
 
< ("inner".ra)::bigint)) OR (("outer".ipix > ("inner".ra)::bigint) AND
("outer".ipix < ("inner"."dec")::bigint)))  ->  Seq Scan on q3c  (cost=0.00..60928.16 rows=3000016 width=48)  ->
Materialize (cost=2428.69..3568.41 rows=113972 width=48)        ->  Seq Scan on q3c_subset uu  (cost=0.00..2314.72
rows=113972
width=48)
(5 rows)


wsdb=# set enable_seqscan TO off;
SET

wsdb=# EXPLAIN SELECT  *  FROM q3c_subset AS uu,q3c WHERE 
(q3c.ipix>uu.dec::bigint AND q3c.ipix<uu.ra::bigint) OR
(q3c.ipix>uu.ra::bigint AND q3c.ipix<uu.dec::bigint) ;

QUERY PLAN                                                                                           

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Nested
Loop (cost=200002428.69..13876776298.93 rows=71760530869 width=96)  Join Filter: ((("outer".ipix >
("inner"."dec")::bigint)AND ("outer".ipix
 
< ("inner".ra)::bigint)) OR (("outer".ipix > ("inner".ra)::bigint) AND
("outer".ipix < ("inner"."dec")::bigint)))  ->  Seq Scan on q3c  (cost=100000000.00..100060928.16 rows=3000016
width=48)  ->  Materialize  (cost=100002428.69..100003568.41 rows=113972 width=48)        ->  Seq Scan on q3c_subset uu
(cost=100000000.00..100002314.72
 
rows=113972 width=48)
(5 rows)


I tried this queries on 7.4.6 and 8.0.1 and the result is the same. 

So, Why the planner cannot use the index scan for that case ? 
What is wrong ? 

Thank you in advance.

Sergey

------------------------------------------------------------
Sergey E. Koposov
Sternberg Astronomical Institute, Moscow University (Russia)
Max-Planck Institute for Astronomy (Germany) 
Internet: math@sai.msu.ru, http://lnfm1.sai.msu.su/~math/

"Sergey E. Koposov" <math@sai.msu.ru> writes:
> I want to descibe some strange behaviour of the postgres planner.

It's not strange exactly: the mechanism for OR indexscan and the
mechanism for nestloop join indexscan are separate and don't talk
to each other.  So you don't get to have a join inner indexscan that
involves an OR condition.

I have some vague ideas about replacing orindxpath.c entirely, once
we have some infrastructure for doing OR indexscans via bitmap union.
But it's not just around the corner.

In the meantime you might try expressing your query as a UNION.
        regards, tom lane

On Sat, 12 Mar 2005, Tom Lane wrote:

> "Sergey E. Koposov" <math@sai.msu.ru> writes:
>> I want to descibe some strange behaviour of the postgres planner.
>
> It's not strange exactly: the mechanism for OR indexscan and the
> mechanism for nestloop join indexscan are separate and don't talk
> to each other.  So you don't get to have a join inner indexscan that
> involves an OR condition.
>
> I have some vague ideas about replacing orindxpath.c entirely, once
> we have some infrastructure for doing OR indexscans via bitmap union.
> But it's not just around the corner.

for 8.1, probably ?

>
> In the meantime you might try expressing your query as a UNION.

Hmm, I'm wondering if the table will be reades as much as the number 
of UNIONs or there is some optimization ?

>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>               http://archives.postgresql.org
>
    Regards,        Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83